title: where like over varchar(500)
body: <p>I have a query which slows down immensely when i add an addition where part</p>
  
  <p>which essentially is just a like lookup on a varchar(500) field</p>
  
  <p>where...</p>
  
  <p>and (xxxxx.yyyy like '% blahblah %')</p>
  
  <p>I've been racking my head but pretty much the query slows down terribly when I add this in.</p>
  
  <p>I'm wondering if anyone has suggestions in terms of changing field type, index setup, or index hints or something that might assist.</p>
  
  <p>any help appreciated.</p>
  
  <p>sql 2000 enterprise.</p>
  
  <p>HERE IS SOME ADDITIONAL INFO:</p>
  
  <p>oops.  as some background unfortunately I do need (in the case of the like statement) to have the % at the front.
  There is business logic behind that which I can't avoid.</p>
  
  <p>I have since created a full text catalogue on the field which is causing me problems
  and converted the search to use the contains syntax.</p>
  
  <p>Unfortunately although this has increased performance on occasion it appears to be slow (slower) for new word searchs.
  So if i have apple.. apple appears to be faster the subsequent times but not for new searches of orange (for example).</p>
  
  <p>So i don't think i can go with that (unless you can suggest some tinkering to make that more consistent).</p>
  
  <p>Additional info:</p>
  
  <p>the table contains only around 60k records
  the field i'm trying to filter is a varchar(500)
  sql 2000 on windows server 2003</p>
  
  <p>The query i'm using is definitely convoluted</p>
  
  <p>Sorry i've had to replace proprietary stuff.. but should give you and indication of the query:</p>
  
  <pre><code>SELECT TOP 99 AAAAAAAA.Item_ID, AAAAAAAA.CatID, AAAAAAAA.PID, AAAAAAAA.Description, 
  AAAAAAAA.Retail, AAAAAAAA.Pack, AAAAAAAA.CatID, AAAAAAAA.Code, BBBBBBBB.blahblah_PictureFile AS PictureFile, 
  AAAAAAAA.CL1, AAAAAAAA.CL1, AAAAAAAA.CL2, AAAAAAAA.CL3 
  FROM CCCCCCC INNER JOIN DDDDDDDD ON CCCCCCC.CID = DDDDDDDD.CID 
  INNER JOIN AAAAAAAA ON DDDDDDDD.CID = AAAAAAAA.CatID LEFT OUTER JOIN BBBBBBBB 
  ON AAAAAAAA.PID = BBBBBBBB.Product_ID INNER JOIN EEEEEEE ON AAAAAAAA.BID = EEEEEEE.ID 
  WHERE 
  (CCCCCCC.TID = 654321) AND (DDDDDDDD.In_Use = 1) AND (AAAAAAAA.Unused = 0) 
  AND (DDDDDDDD.Expiry &gt; '10-11-2010 09:23:38')  AND 
  (
      (AAAAAAAA.Code = 'red pen') OR 
          ( 
              (my_search_description LIKE '% red %') AND (my_search_description LIKE '% nose %')  
              AND (DDDDDDDD.CID IN (63,153,165,305,32,33))
           )
  )
  AND (DDDDDDDD.CID IN (20,32,33,63,64,65,153,165,232,277,294,297,300,304,305,313,348,443,445,446,447,454,472,479,481,486,489,498)) 
  ORDER BY AAAAAAAA.f_search_priority DESC, DDDDDDDD.Priority DESC, AAAAAAAA.Description ASC
  </code></pre>
  
  <p>You can see throwing in the my_search_description filter also includes a dddd.cid filter (business logic).</p>
  
  <p>This is the part which is slowing things down (from a 1.5-2 second load of my pages down to a 6-8 second load (ow ow ow))</p>
  
  <p>It might be my lack of understanding of how to have the full text search catelogue working.</p>
  
  <p>Am very impressed by the answers so if anyone has any tips I'd be most greatful.</p>