none
How to optimize a simple Query for a 2M rows table

    Question

  • I have the following table (ON SQL 2005 DEVELOPER ED.)

     

    ITEMS

    ------------------------------------------------------------------------------------------------

    ID_ITEM (INT) <-- Identity (autoincremental)

    ITEMNAMEUNIQUE (NVARCHAR(256))

    .. other FK

    ------------------------------------------------------------------------------------------------

    PRIMARY KEY: ID_ITEM

    INDEX UNIQUE KEY: ID_ITEM AND ITEMNAMEUNIQUE

    ------------------------------------------------------------------------------------------------

     

    This table is AS is, it has no more extras; no catalogs (as i don't konw about), no partitions (as i don't know), and no others things more than the basics.

     

    IT HAVE MORE THAN 2 MILLION OF ROWS

     

    WHATS MY PROBLEM

    i have a complex query, where i need to use a temp table to store the results for a query for that items table.
    When i run:

     

    CREATE TABLE #tquery2b (ID_ITEM INT)

    INSERT INTO #tquery2b

    SELECT ID_ITEM FROM ITEMS WHERE 1 = 1 AND ItemNameUnique LIKE '%anything%'

    DROP TABLE #tquery2b

    IT SPENDS 4 SECONDS

     

    if i use

     

    DECLARE @TT_ITEMS TABLE(ID_ITEM INT)

    INSERT INTO @TT_ITEMS

    SELECT ID_ITEM FROM ITEMS WHERE 1 = 1 AND ItemNameUnique LIKE '%anything%'

    IT SPENDS 8 SECONDS !!

     

    WHAT CAN I DO TO REDUCE THE TIME TO GET THE RESULTS, I THINK THAT CAN BE ANY WAY TO GET IN A 1 SECOND OR LESS, BUT HOW ??

    if it's POSSIBLE to not use "Create Table" best of all, beacuse this database is for a websites (with a thousands of users making queries)

     

    Thursday, June 12, 2008 5:31 PM

All replies

  •  

    Create the temp table, but add an index to it.
    Thursday, June 12, 2008 5:45 PM
  • How ?

     

    If i use:

     

    CREATE TABLE #TT_ITEMS (ID_ITEM INT PRIMARY KEY, ITEMNAMEUNIQUE NVARCHAR(256))

    INSERT INTO #TT_ITEMS

    SELECT ID_ITEM, ITEMNAMEUNIQUE FROM ITEMS WHERE 1 = 1 AND ItemNameUnique LIKE '%anything%'

    DROP TABLE #TT_ITEMS

     

    ALSO SPENDS 4 SCONDS !

     

    What do you mean with "add an index to it" can you post an example

     

     

    Thursday, June 12, 2008 5:53 PM
  •  

    Is ItemNameUnique really unique?  That syntax should already create a clustered index on the id.  Maybe you can create unique index on itemnameunique?  Sorry, now I read more carefully.  You are selecting from the production table, not the temp table.  You should check your indexes on there as well, and expect to see slower times when data volume increases.
    Thursday, June 12, 2008 6:03 PM
  • YES, ItemNameUnique is Unique, as if you see on the top of the post the table ITEMS have a INDEX UNIQUE KEY: ID_ITEM, ITEMNAMEUNIQUE. Note that ID_ITEM is unique also (its an Identity (autoincremental unique id))

     

    Can you (or anyone) post any (or various) possible example to get the query more fast

     

     

     

     

     

     

     

    Thursday, June 12, 2008 6:12 PM
  • can you post the results of dbcc showcontig('ITEMS') ?

     

    Just run:

     

    dbcc showcontig('ITEMS')

     

    when connected to your database containing the ITEMS table.

     

    You could also try SELECT * INTO #TT_ITEMS instead of insert into

    Thursday, June 12, 2008 6:21 PM
  •  

    HERE THE RESULT

    DBCC SHOWCONTIG scanning 'Items' table...
    Table: 'Items' (1092914965); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 30041
    - Extents Scanned..............................: 3775
    - Extent Switches..............................: 3822
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 98.25% [3756:3823]
    - Logical Scan Fragmentation ..................: 1.42%
    - Extent Scan Fragmentation ...................: 99.66%
    - Avg. Bytes Free per Page.....................: 118.8
    - Avg. Page Density (full).....................: 98.53%

     

     

    NOTE: "SELECT * INTO #TT_ITEMS instead of insert into" gives me an error

    CREATE table #TT_ITEMS (ID_ITEM INT , ITEMNAMEUNIQUE NVARCHAR(256))

    SELECT ID_ITEM, ITEMNAMEUNIQUE FROM ITEMS WHERE 1 = 1 AND ItemNameUnique LIKE '%anything%' into #TT_ITEMS

    DROP TABLE #TT_ITEMS

    Mens. 156, Nivel 15, Estado 1, Línea 9

    Incorrect syntax near the keyword 'into'

    Thursday, June 12, 2008 6:53 PM
  •  

    Results don't look too shabby.

     

    For SELECT * INTO you don't need create tables statement.  Try it like this:

     

     

    SELECT ID_ITEM, ITEMNAMEUNIQUE 

    INTO #TT_ITEMS 

    FROM ITEMS

    WHERE ItemNameUnique LIKE '%anything%'

    Thursday, June 12, 2008 7:23 PM
  • I try SELECT INTO and works, BUT ALSO SPEND 4 SECONDS !

    I can't believe that ! - ITS a LOT OF TIME !!

     

    Can you know (or anyone) a solution to make a simple query in a 2Million of rows (or more) Table, more faster ?

    Any suggestion ?, or maybe the use of full-text indexing will be more fastest.

     

    Thursday, June 12, 2008 7:38 PM
  • You may have to run the query a few times to get the compiler to cache it, and get a better run time.  I don't know if four seconds is so bad.  I would have to see your hardware configuration, etc. to know what the root cause is.  I doubt adding an index to the the table after a select * into would reduce your query time.

     

    Thursday, June 12, 2008 7:44 PM
  • I need performance,TIME !, this is why i used this query block as an example.

    Really this SQL script come from a dynamic SP, so it can't be cached (i think that -dynamic sp not cache).

     

    Any case, 4 seconds is a lot

     

    I mantain this post open, beacuse i don't want to believe that SQL 2005 can't get this query in 1 or less than a second (MySql can be done, but i don't have knowledgements and i cannot use MySQL).

     

    Anyone knows if full text indexing can reduce the time ?

     

     

     

     

     

    Thursday, June 12, 2008 8:07 PM
  • An index on the temp table is certainly not going to help with the select into. Indexes improve the performance of select queries but slow down inserts/updates.

    Your table is 30041 pages. That's 234 MB, so reading 234 MB in 4 seconds may or may not be a bad thing depending on your physical db design, especially tempdb.

    Can you post the sp_helpdb output for tempdb and the db you're selecting from:

     

    sp_helpdb tempdb

     

     

     

    Thursday, June 12, 2008 8:27 PM
    Answerer
  • One problem you are going to have is that you are doing a double wildcard string match on ItemNameUnique, which is probably resulting in a Index scan over a seek.  You can try defining a Full Text Index on this column, and using that to do the match.  What does the execution plan for the query look like?  You can provide this by running the following command before you run the query:

     

    Code Snippet

    SET SHOWPLAN_TEXT ON

     

     

    Thursday, June 12, 2008 8:35 PM
    Moderator
  • Here i show the results, for sp_helpdb tempdb

     

    db_size owner dbid created status compatibility_level

    -------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------

    tempdb 1439.06 MB sa 2 Jun 12 2008 Status=ONLINE,

    Updateability=READ_WRITE,

    UserAccess=MULTI_USER,

    Recovery=SIMPLE,

    Version=611

    , Collation=Modern_Spanish_CI_AI,

    SQLSortOrder=0, I

    sAutoCreateStatistics,

    IsAutoUpdateStatistics 90

    name fileid filename filegroup size maxsize growth usage

    -------------------------------------------------------------------------------------------------------------------------------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ ------------------ ---------

    tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf PRIMARY 1437888 KB Unlimited 10% data only

    templog 2 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf NULL 35712 KB Unlimited 10% log only

    Thursday, June 12, 2008 8:45 PM
  • HERE THE SHOW PLAN

     

    StmtText

    -----------------------------------------------------------------------------------------------------------------

     

    --SET SHOWPLAN_TEXT ON

    drop table #tt_items

    SELECT ID_ITEM, ITEMNAMEUNIQUE

    INTO #TT_ITEMS

    FROM ITEMS

    WHERE ItemNameUnique LIKE '%anything%'

    (2 row(s) affected)

    StmtText

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

    |--Table Insert(OBJECTSad[#TT_ITEMS]), SETSad[#TT_ITEMS].[ID_ITEM] = [Expr1006],[#TT_ITEMS].[ITEMNAMEUNIQUE] = [MYDATABASE].[dbo].[Items].[ItemNameUnique]))

    |--Top(ROWCOUNT est 0)

    |--Compute Scalar(DEFINESad[Expr1006]=setidentity([MYDATABASE].[dbo].[Items].[ID_Item],(-7),(0),N'#TT_ITEMS')))

    |--Parallelism(Gather Streams)

    |--Clustered Index Scan(OBJECTSad[MYDATABASE].[dbo].[Items].[PK_Items]), WHERESad[MYDATABASE].[dbo].[Items].[ItemNameUnique] like N'%anything%'))

    (5 row(s) affected)

    StmtText

    --------------------------------------

    select top 50 * from #tt_items

    (1 row(s) affected)

    StmtText

    ----------------------------------------------------------

    |--Top(TOP EXPRESSIONSad(50)))

    |--Table Scan(OBJECTSad[tempdb].[dbo].[#tt_items]))

    (2 row(s) affected)

     

    Thursday, June 12, 2008 8:55 PM
  • Your tempdb is located in the worst possible place for performance. Looks like the system partition and probably the same location as your paging file?

    Is the db you're selecting the data from residing in the same place?

    You really need to review how you've set the databases up:

    http://msdn.microsoft.com/en-us/library/ms175527.aspx?n=0

    http://msdn.microsoft.com/en-us/library/ms179316.aspx

     

    Thursday, June 12, 2008 8:55 PM
    Answerer
  •  

    A temp table that he is probably going to select from in a later query.
    Thursday, June 12, 2008 9:03 PM
  • In which case the index should be added after the #table has been fully populated.

     

    Thursday, June 12, 2008 9:08 PM
    Answerer
  •  goodyes wrote:

    |--Compute Scalar(DEFINE[Expr1006]=setidentity([MYDATABASE].[dbo].[Items].[ID_Item],(-7),(0),N'#TT_ITEMS')))

    |--Parallelism(Gather Streams)

    |--Clustered Index Scan(OBJECT[MYDATABASE].[dbo].[Items].[PK_Items]), WHERE[MYDATABASE].[dbo].[Items].[ItemNameUnique] like N'%anything%'))

     

    From your showplan, you are forcing a Clustered Index Scan which is one of the most expensive scans you can do.

     

    Try creating the following index based on your query:

     

    Code Snippet

    CREATE INDEX IX_Item_Testing ON Items (ItemNameUnique)

     

     

    I don't know how much it will help, but create it, then rerun your query, and provide the showplan_text back here again.
    Thursday, June 12, 2008 9:46 PM
    Moderator
  • I think Tempdb current setup is part of an issue which is causing performance hit as both mdf and ldf are located in the same drive. IO will be impacted because of this.

     

    Friday, June 13, 2008 5:37 AM
  • Hei, thanks for all

     

    I discover the following:

     

    SELECT ID_ITEM, ITEMNAMEUNIQUE

    FROM ITEMS

    WHERE ItemNameUnique LIKE '%anything%'

    SPENDS 4 SECONDS

     

    BUT

     

    SELECT ID_ITEM, ITEMNAMEUNIQUE

    FROM ITEMS

    WHERE ItemNameUnique = '%anything%'  <-- this is good, but is ok only for 1 word

    SPENDS 0 SECONDS

     

    The problem is that the queries (from users) may contain various words ..

    Some one said that is best to use Full-Text, but the problem for FullText is the manteineance, as this table every day grows up about 2 or 5 thousands of rows more.

     

    Any suggestions

     

    Friday, June 13, 2008 11:30 AM
  •  SQLUSA wrote:
     

    Build a SearchTerm table as per my previous post.

     

    You will get subsecond response for 19 of 20 queries (repeats) and 4 - 6 sec for new queries.

     

    Let us know if this works for you.

     

    With an end user ability to search any word or combination of words, this will eventually cost more to use than just scanning the base table would be.  You are also adding overhead for reading the second table and now maintaining it.  If the maintenance of a Full Text Catalog is a concern, then the cost to maintain this method will probably also be a concern.

     

    The post about the difference in time between ='%someword%' versus like '%someword%' misses the fact that you are losing data when you use the equals operator.  Consider the output of the following test:

     

    Code Snippet

    declare @table table

    (rowid int identity primary key,

    rowdata varchar(1000))

     

    insert into @table

    select 'abc123'

    union all select '123abc'

    union all select '123'

    union all select 'abc'

    select * from @table where rowdata = '%abc%'

    select * from @table where rowdata like '%abc%'

     

     

     

    The resultsets are not the same, so while you can get a seek with the equals because the clause becomes sargable, you lose your results accuracy.
    Friday, June 13, 2008 12:09 PM
    Moderator
  •  

    As the users can query "anything with other words" (<--example)

    I will try to use full-text, but anyone knows if rebuilding catalog every day can spend lot of time, and also if rebuilding catalog full-text every day will decrease performance ??

     

    Friday, June 13, 2008 4:20 PM
  • I don't see gaining any speed using full-text.  Is there any way you can filter out any of the records in the query?  Are there archived items that you shouldn't be pulling from the 2 million rows, or can eliminate?  And, like said by a poster above, make sure your underlying disks aren't shared between files, or other people.

     

    Friday, June 13, 2008 4:35 PM
  •  goodyes wrote:

    As the users can query "anything with other words" (<--example)

    I will try to use full-text, but anyone knows if rebuilding catalog every day can spend lot of time, and also if rebuilding catalog full-text every day will decrease performance ??

    You can certainly give it a try. Naturally there is some maintenance overhead involved, there is no "free lunch" when it comes to db tuning. Keep in mind though that this is another brute force solution similar to LIKE, not a "smart" solution.

    I still maintain that my suggestion of building a SearchTerm table will help you to your goal of subsecond response.  You can easily check the validity of this claim by tracing the sproc for a day and running distribution stats of the searc terms. If you see over 70% repeats during a day, that means even higher percentage of repeats over a longer period.  That means that "smart" search will provide you with high performance.

    The general wisdom in performance tuning circles is that high performance starts with platform configuration, database design and continues with index optimization. You can only go so far with index (or full-text index) optimization. Sometimes you have to go back to the drawing board and augment/enhance the original database design.



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Monday, October 15, 2012 7:13 PM
    Friday, June 13, 2008 4:47 PM
  •  Jonathan Kehayias wrote:

     You are also adding overhead for reading the second table and now maintaining it.  If the maintenance of a Full Text Catalog is a concern, then the cost to maintain this method will probably also be a concern.

    .

    Thanks Jonathan for your observation. Agreed, my suggested SearchTerm table solution has some overhead also.

    However, one would get subsecond response in 95%+ of the cases by building a SearchTerm table. I have substantial experience with consumer web search terms. Given the little we know about this application, I deduce that it is mostly 1-2 word search terms. The reason for my conclusion is that longer search terms would not work well with the LIKE comparison operator. Short search terms mean lots of repeats. If so, why scan through 2 million rows every time there is repeat request?



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Monday, October 15, 2012 7:12 PM
    Friday, June 13, 2008 5:00 PM
  •  

    Hi SQLUSA

     

    The problem is that the users search different words, there are litle words that may be repeated.

    And, as i explain, the users typically use a combination of words to search for.

     

    So what can i use for this queries (that combine 2 or more words : "some example to search")

     

    Note that:

    The script in on a Dynamic sproc

    The table items have more than 2Million of rows

    ID_ITEM PK Identity, ItemNameUnique nVarCahr(256) Unique

    Absolutely, Are any way to get a query for multiple words in no more than 1 second ?

     

    ANY MORE QUESTION SQLUSA

     

    If i use your suggested table, whats happend when the Items table gets thousand of new records every day ??

    Example:

    Yesterday the SearchTerm table was filled with:

    SearchTerm --- ID_ITEM

    "some search by user", 1

    "some search by user", 5

    ("some search by user" found in ID_Items 1 and 5)

    TODAY

    The ItemsTable fill with 2 thousands of more records, wicth 2 contains "some search by user" filled as ID_ITEM 10 and 50

     

    If, as your suggestion, the code first try to look at the SearchTerm table for another query that uses the words "some search by user", it will return only ID's_ITEMS for 1 and 5 found BUT WHATS HAPPEND for the new Items filled TODAY (ID_ITEM 10 and 50) ??

    So, i think that your suggestion will no have good effect

     

    Correct me if i'm wrong, or give me a solution

     

     

     

    Friday, June 13, 2008 5:18 PM
  •  

    related to update the SearchTerms table when new items go for that Items Table

    "When new ITEMs are inserted into the ITEMS table, you can update the SearchTerm table real-time or nightly. You have to check for each SearchTerm if it satisfies the LIKE operator, if it does, insert the item_id with that SearchTerm into the SearchTerm table."

     

    I think that need to be strong for the performance, as every day the Items table have more than 2 thousands of items (some days more than 5000), so .. if i script a trigger on the items table tp update the SearchTerms table, it can be spend lot of time , what do you think ?

     

    More, .. and the day when the SearchTerms table have  more than 1.000.000 of rows, and when 10 Million ?

    As "any search by any user" will go for 1 thousand of matches (with like operator), the SearchTerms will have a millions of rows.

    I think that the performance will decrease

    Friday, June 13, 2008 6:07 PM
  • I am going to be up front and say that I am not a Full Text guru by far, but here is what I did to test this:

     

    Code Snippet

    create table contacts (id int identity primary key, data varchar(255))

    insert into dbo.contacts

    select convert(varchar(255), comments)

    from dbo.webcontacts

    where comments is not null

    and len(convert(varchar(255), comments)) > 100

     

    (2360664 row(s) affected)

     

     

    So that gives me my table and data:

     

    Code Snippet

    sp_fulltext_database 'enable'

     

    create fulltext catalog test_fts

    in path 'd:\sqlbackups\test\'

    as default

     

    create fulltext index on contacts

    (data)

    key index PK_Contacts

    on test_fts

    with change_tracking auto

     

     

    This will build the FTS Index and it took about 15 minutes to populate on my dev server.

     

    Code Snippet

    SET STATISTICS IO ON

    SET STATISTICS TIME ON

     

    declare @searchvar varchar(100)

    Set @Searchvar = 'cook'

     

    select id, data from contacts where data like '%'+@SearchVar+'%'

     

    SET @SearchVar = '"*'+@SearchVar+'*"'

     

    select id, data

    from contacts

    where contains(data, @SearchVar)

     

     

    This resulted in:

     

    Code Snippet

    SQL Server parse and compile time:

    CPU time = 16 ms, elapsed time = 247 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    (49976 row(s) affected)

    Table 'contacts'. Scan count 1, logical reads 68241, physical reads 2, read-ahead reads 68112, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 8703 ms, elapsed time = 20549 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    (40444 row(s) affected)

    Table 'contacts'. Scan count 0, logical reads 121332, physical reads 3134, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 609 ms, elapsed time = 3589 ms.

     

     

     

    The Full Text Search is much faster, but it is missing rows.  It doesn't recognize undercook, overcook, recook with the FTS.  If this would be expected in the resultsset then, the FTS may not work out for you.  Keep in mind that these numbers reflect my development server which is not optimally configured, and is memory bound at this point.  I had some performance spikes occur during testing this, that I am going to look at.  Also not sure that I would be doing a auto population in production either.
    Friday, June 13, 2008 7:17 PM
    Moderator
  •  

    Thanks to Jonathan for its data, and also for SQLUSA

     

    Now i will try SQL TermsTable with a trigger; both for single word and also for 2 or more words (using a like operator)

     

    FIRST I WILL PUT THIS SCRIPT ON MY DYNAMIC SPROC (NOTE: "Spiderman 3" is an example (the words that the users enter))

    DECLARE @ITEMNAMEUNIQUE_TOSEARCHFOR NVARCHAR(256)

    SET @ITEMNAMEUNIQUE_TOSEARCHFOR = 'spiderman 3'

    IF NOT EXISTS (

    SELECT ID_ITEM, ItemSearchTerm FROM ItemsSearchTerms WHERE 1 = 1 AND ItemSearchTerm = @ITEMNAMEUNIQUE_TOSEARCHFOR

    )

    BEGIN

    INSERT INTO ItemsSearchTerms SELECT ID_ITEM, @ITEMNAMEUNIQUE_TOSEARCHFOR FROM Items WHERE 1 = 1 AND ItemNameUnique LIKE '%spiderman 3%'

    END

     

    SELECT ID_ITEM, ItemSearchTerm FROM ItemsSearchTerms WHERE 1 = 1 AND ItemSearchTerm = @ITEMNAMEUNIQUE_TOSEARCHFOR

     

    LATER I WILL ADD A TRIGGER LIKE

    CREATE TRIGGER InsertItemSearchTerm ON ITEMS

    AFTER INSERT

    AS

    BEGIN

    DECLARE @ITEMNAMEUNIQUE_INSERTED NVARCHAR(256)

    SET @ITEMNAMEUNIQUE_INSERTED = ( SELECT ItemNameUnique FROM INSERTED )

    DECLARE @ITEMID_INSERTED INT

    SET @ITEMID_INSERTED = ( SELECT ID_ITEM FROM INSERTED )

     

    DECLARE @TT AS TABLE (ItemSearchTerm NVARCHAR(256), CONT INT)

    INSERT INTO @TT

    SELECT ItemSearchTerm, ROW_NUMBER() OVER (ORDER BY ItemSearchTerm) FROM (

    SELECT DISTINCT ItemSearchTerm FROM ItemsSearchTerms

    WHERE @ITEMNAMEUNIQUE_INSERTED like '%'+ REPLACE(ItemSearchTerm,' ','%') +'%'

    )

    AS RES2

    DECLARE @COUNTER int

    SET @COUNTER = 0

    DECLARE @SEARCHTERM NVARCHAR(256)

    WHILE @COUNTER < (SELECT MAX(CONT) FROM @TT)

    BEGIN

    SET @COUNTER = @COUNTER + 1

    SET @SEARCHTERM = (SELECT ItemSearchTerm FROM @TT AS TT WHERE TT.CONT = @COUNTER)

    INSERT INTO ItemsSearchTerms (ID_ITEM, ItemSearchTerm) VALUES (@ITEMID_INSERTED, @SEARCHTERM )

    END

    END

     

    ANY SUGGESTIONS/OPINIONS ABOUT THIS CODE

     

    NOTE TO JONATHAN: Can you explain if the last phrase (where creatting the fulltext catalog), will indicate that will be an AUTO catalog (mean that when any new data inserted on Items Table the catalog will update it by itself ?) ?

     

    NOTE for SQLUSA: What do you think as for performance, as the items table is every time growing up (2000 or more rows per day, updating every time)

     

    Friday, June 13, 2008 7:54 PM
  •  

    Hi, SQLUSA

     

    And for All ..

     

    I tried the solution of using an alternative table (the commented by sqlUsa), that i called ItemsSearchTerms, ..

    I write the above codes (in above post), and i need to say that is very good..

     

    Also i need to comment that also is a best practice to DeFragment the table/s, i increase more performance ..

     

    In this commented post about performance queries - the ones that need to search in a more than 2 million rows table-, using this alternative table to store common words (searched by users) reduce the spended time (in my case) from 4 to less than 1 second.

    Its great, but i need to note various things.

     

    1)Its neccesary to write a Trigger like the one i writed above, for AFTER INSERT, to update the Terms Table.

    2)Its very important -almost for my case- to write a ScheduleTask using SQL Agent, to delete all rows from this table that have a date up to the previous last month. This is beacuse the table grows faster and can have a Billion of rows at a month, so deleting some time some past word-queries can be useful.

     

    Last questions for SQLUSA (i think maybe last)

    My ItemsSearchTerms now have: ID_ITEM(PK int), ItemSearchTerm (PK nvarchar256) and ItemSearchTermDate (date) <-- for delete when the agent process schedule.

    1)How can i create this Clustered index ?

    2)For what columns (i think the 2 PK) ?

    3)Will decrease performance on a daily base update (as per query or as per trigger) ?

    4)Some times the trigger fails, stoping the process of indexing data (hard to me beacuse i need to delete all data recolected this time). See the trigger code above, and if you can or if you see any wrong or any that could be best, can you help me ? (i don't know why some times this trigger fails)

     

    Thanks to all, i will that all people can learn from this post

     

    Monday, June 16, 2008 8:33 PM