June 12, 2008 at 2:13 pm
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 THERE ANY WAY TO GET THE RESULTS 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)
June 12, 2008 at 2:40 pm
Because of the leading wildcard in your LIKE statement - the optimizer essentially can't use your indexes efficiently, so I think you'll find that it's scanning the entire table (or doing an index scan). I'm frankly surprised it's only taking 4 and 8 seconds, although the columns aren't incredibly wide.
If you want a fuzzy search of the item name, you may need to consider using a full text index on the item name.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 12, 2008 at 2:51 pm
Yeah, i will consider.
But a question.
Using Full-text, may not decrease performance ¿?
You think that with full-text the queries will be more faster ?
June 12, 2008 at 3:07 pm
well - you're setting up a scenario to bring back items whose name have a specific keyword (or several), which is essentially one of the things FTI does. It breaks strings into words and indexes those. So - yes - it should help on the finding of items, although you'd have to change your syntax to use FREETEXT or CONTAINS.
Obviously - you'd have to balance against the maintenance for keeping up the Full-text index (like - if your item names get updated a lot - that could become a fair amount of work).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 12, 2008 at 3:22 pm
Hi,
Review and or post your execution plan as this will determine if a table scan is indeed being performed.
Looks like a good case for Full Text Indexing either way, as even with a nonclustered index on the search column, a Full Text Index will provide higher selectivity.
Cheers,
John
June 12, 2008 at 3:34 pm
Thanks, but the problems for FullText is that actually every day the table has more than 2 thousands of new records (every day).
It difficult to mantain
June 12, 2008 at 3:55 pm
Hi,
You could make use of the Incremental Population method, for defining a Full Text Index. This way, you could schedule a daily job to update to your index to take account of the newly inserted rows.
Have a look at the "Incremental timestamp-Based Population " section.
http://technet.microsoft.com/en-us/library/ms142575.aspx
Cheers,
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply