Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
amil67
Starting Member
1 Post |
 Posted - 2011-02-25 : 11:05:58
|
| Can I please get some help improving the time it takes to run the following select statement. The ITEM table has about 2.2M rows and I'm trying to return the number of times the search terms (WORDS table) are found. declare @Words table (Word varchar(100) not null);insert into @Words select ltrim(rtrim('THIS'))insert into @Words select ltrim(rtrim('THAT'))insert into @Words select ltrim(rtrim('PART'))select T.item_code, Count(*) as MatchNumFROM item T inner join @Words W on ' ' + COALESCE(T.item_desc1extended, '') + ' ' + T.item_code + ' ' + COALESCE(T.keywords, '') + ' ' + T.short_code + ' ' like '% ' + Word + ' %' or ' ' + COALESCE(T.item_desc1extended, '') + ' ' + T.item_code + ' ' + COALESCE(T.keywords, '') + ' ' + T.short_code + ' ' like Word + ' %' or ' ' + COALESCE(T.item_desc1extended, '') + ' ' + T.item_code + ' ' + COALESCE(T.keywords, '') + ' ' + T.short_code + ' ' like '% ' + Word group by T.item_code |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-02-25 : 12:07:22
|
| 1) You don't need the three LIKEs; checking against ('%' + Word + '%') will find all cases where Word is in the beginning, middle or end of your string. Explicitly re-checking to see if there is a match at the beginning or end is superfluous.2) LIKE is almost inherently slow since you'll need to do a table or index scan.3) If the data elements in item_code were NOT NULL, you could eleiminate the COALESCE. (I am on a "NOT NULL" jag so I'm probably reaching for this one)=======================================Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986) |
 |
|
|
|
|
|
Help Improve SQL Select