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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply