SQL Server Performance

Help Improve SQL Select

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by amil67, Feb 25, 2011.

  1. amil67 New Member

    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
  2. Adriaan New Member

    Split up the search term: your table variable should have separate fields to match the fields in your ITEM table. The concatenation in your criteria is a guarantee for bad performance.
    Also, define a PK on your table variable on all columns (or use a temporary table instead, so you can use any regular index to support your query).
    Also, have you looked at Full-Text Indexing?
  3. mmarovic Active Member

    [quote user="Adriaan"]
    Split up the search term: your table variable should have separate fields to match the fields in your ITEM table. The concatenation in your criteria is a guarantee for bad performance.
    [/quote]Exactly, here is the link of the most recent article providing possible solution: http://www.sqlservercentral.com/articles/String Manipulation/72540/

Share This Page