where like over varchar(500)

Question

I have a query which slows down immensely when i add an addition where part

which essentially is just a like lookup on a varchar(500) field

where...

and (xxxxx.yyyy like '% blahblah %')

I've been racking my head but pretty much the query slows down terribly when I add this in.

I'm wondering if anyone has suggestions in terms of changing field type, index setup, or index hints or something that might assist.

any help appreciated.

sql 2000 enterprise.

HERE IS SOME ADDITIONAL INFO:

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.

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.

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).

So i don't think i can go with that (unless you can suggest some tinkering to make that more consistent).

Additional info:

the table contains only around 60k records the field i'm trying to filter is a varchar(500) sql 2000 on windows server 2003

The query i'm using is definitely convoluted

Sorry i've had to replace proprietary stuff.. but should give you and indication of the query:

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

You can see throwing in the my_search_description filter also includes a dddd.cid filter (business logic).

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))

It might be my lack of understanding of how to have the full text search catelogue working.

Am very impressed by the answers so if anyone has any tips I'd be most greatful.

Answer 1

If you haven't already, enable full text indexing.

Unfortunately, using the LIKE clause on a query really does slow things down. Full Text Indexing is really the only way that I know of to speed things up (at the cost of storage space, of course).

Here's a link to an overview of Full-Text Search in SQL Server which will show you how to configure things and change your queries to take advantage of the full-text indexes.

Answer 2

The '% blahblah %' is a problem for improving performance. Putting the wildcard at the beginning tells SQL Server that the string can begin with any legal character, so it must scan the entire index. Your best bet if you must have this filter is to focus on your other filters for improvement.

Answer 3

More details would certainly help, but...

Full-text indexing can certainly be useful (depending on the more details about the table and your query). Full Text indexing requires a good bit of extra work both in setup and querying, but it's the only way to try to do the sort of search you seek efficiently.

The problem with LIKE that starts with a Wildcard is that SQL server has to do a complete table scan to find matching records - not only does it have to scan every row, but it has to read the contents of the char-based field you are querying.

With or without a full-text index, one thing can possibly help: Can you narrow the range of rows being searched, so at least SQL doesn't need to scan the whole table, but just some subset of it?

Answer 4

Using LIKE with a wildcard at the beginning of the search pattern forces the server to scan every row. It's unable to use any indexes. Indexes work from left to right, and since there is no constant on the left, no index is used.

From your WHERE clause, it looks like you're trying to find rows where a specific word exists in an entry. If you're searching for a whole word, then full text indexing may be a solution for you.

Full text indexing creates an index entry for each word that's contained in the specified column. You can then quickly find rows that contain a specific word.

Answer 5

As other posters have correctly pointed out, the use of the wildcard character % within the LIKE expression is resulting in a query plan being produced that uses a SCAN operation. A scan operation touches every row in the table or index, dependant on the type of scan operation being performed.

So the question really then becomes, do you actually need to search for the given text string anywhere within the column in question?

If not, great, problem solved but if it is essential to your business logic then you have two routes of optimization.

1. Really go to town on increasing the overall selectivity of your query by focusing your optimization efforts on the remaining search arguments.
2. Implement a Full Text Indexing Solution.

Answer 6

I don't think this is a valid answer, but I'd like to throw it out there for some more experienced posters comments...are these equivlent?

where (xxxxx.yyyy like '% blahblah %')

vs

where patindex(%blahbalh%, xxxx.yyyy) > 0

As far as I know, that's equivlent from a database logic standpoint as it's forcing the same scan. Guess it couldn't hurt to try?