I have a query that slows down a lot when I add an add, where part
which essentially resembles a search in the varchar (500) field
Where...
and (xxxxx.yyyy like '% blahblah%')
I rack my brains, but pretty much the query slows down a lot when I add this.
I am wondering if anyone has any suggestions regarding changing a field type, setting an index or index hints or something that might help.
any help appreciated.
sql 2000.
HERE SOME ADDITIONAL INFORMATION:
oops. as in some cases, unfortunately, I need (in the case of a similar statement) to have% in front. Behind this is business logic, which I cannot escape.
Since then I have created a full text directory on the field that causes me problems and converted the search to use the contains syntax.
Unfortunately, although this increases productivity, it sometimes seems slower for new word searches. Therefore, if I have an apple, then the apple looks faster than subsequent times, but not for new searches for orange (for example).
So, I don’t think I can handle it (unless you can offer some work to make it more consistent).
Additional Information:
the table contains only about 60 thousand records the field I'm trying to filter is varchar (500) sql 2000 on a Windows 2003 server
The query I'm using is definitely confusing.
Sorry, I had to replace the proprietary material .. but you should specify and specify the request:
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 > '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
, my_search_description dddd.cid(-).
, ( 1,5-2 6-8- (ow ow ow))
, , .
, , - - , .