SQL Server Performance Forum – Threads Archive

Search String Variable help

I have a query I was wondering if I could optimize further regarding the search string (in red): SELECT Company.CM_ID,Company.CM_Name_1,Company.CM_Name_2,Company.CM_Branch,Xref_Addr_Comp.AD_ID,Address.AD_Line_1,
Address.AD_Line_2,Address.AD_City,Address.AD_State,Address.AD_Postal,Address.AD_County,
Address.AD_Country From (Company LEFT OUTER JOIN (Xref_Addr_Comp JOIN Address on
(Xref_Addr_Comp.AD_ID=Address.AD_ID)) on (Company.CM_ID=Xref_Addr_Comp.CM_ID))
WHERE Address.AD_Type=’BILL’
AND CM_Store IN (‘CSC’,’ ‘)
AND CM_Name_1 LIKE ‘%DOUBLE R%’
ORDER BY CM_Name_1 ASC,Company.CM_ID ASC There is an unclustered index on CM_Name_1 but it is not utilized as it should because of the leading % in the search string. tThe web front end uses a pick list that uses the following choices for a search: ‘Matches’,’Starts with’, and ‘Contains’. It is the ‘contains’ parameter that results in teh above query. Is it possible to give that functionality another way, one that is less resource intensive? Thanks for any suggestions!

YOu should seperate the contains into it’s own query. Also, this is not supported by Microsoft but if you put an index hint on that table it will force the use of the nonclustered index on your like clause. It’s not as efficient as not using like, but it will considerably lessen your reads, etc. The biggest issues are: 1. It’s not officially supported.
2. You will break code if you change the name of the index or drop it without altering the code first. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Jeffery,
I’d have to respectively disagree with Derrick. If your LIKE query contains a leading wildcard percent sign (%), and even if the column you are searching with this LIKE query and leading wildcard, the SQL Server 2000 optimizer will never use that index and will always perform an index or table scan. This is by design and even the use of an index hint will not change this, supported or un-supported. However, what I’d recommend that you consider is to use SQL Server "Full-text Search" (FTS) and the new CONTAINS, FREETEXT, CONTAINSTABLE or FREETEXTTABLE query in place of using T-SQL LIKE. Depending upon the size of your table, you can get order of magitude of performance improvments. However, you should keep in mind that you most likely will get different results with FTS relative to LIKE as FTS is a language specific word-based search, while LIKE is a string-pattern based search. SQL Server 2000 BOL will have more information on query samples and setting up the FT Catalog, and populating it.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">I’d have to respectively disagree with Derrick. If your LIKE query contains a leading wildcard percent sign (%), and even if the column you are searching with this LIKE query and leading wildcard, the SQL Server 2000 optimizer will never use that index and will always perform an index or table scan. This is by design and even the use of an index hint will not change this, supported or un-supported.<hr height="1" noshade id="quote"></blockquote id="quote"> You are wrong and should test it before you disagree with me. I mean this respectfully of course. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] I was shocked when it actually worked. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Thanks for the help guys. I am going to investigate both and see where I end up. I do appreciate it!

quote:Originally posted by John Kane Jeffery,
…will always perform an index or table scan……

Actually it is a clustered index or table scan. What is important here is the SCAN.

quote:
However, what I’d recommend that you consider is to use SQL Server "Full-text Search" (FTS)…

Nope. Not for what I think the web front-end app (the user) is looking for. In this specific scenario LIKE rules. I believe this particular %xxxx% construct is one of those necessary evils. Your life would be easier if the C_NAME field were part of the clustered index. Nathan H.O.
Moderator
SQL-Server-Performance.com

The front end app is a web based sales portal CRM tool. In searching for the Company specifics users can search by using a picklist that allows for either a ‘starts with’ or ‘contains’ a comany name. The slow query happens when the ‘contains’ (THe most popular option of course)is chosen. This generates the "where C_Name_1 like ‘%Comapny%’
statement. I did some running of the entire query in QA and it does look like the query runs twice as fast, with 90% less reads and about 60% less CPU when using a fulltext index. However, even though this is *mostly* not an OLTP there is still an issue of rebuilding the catalogs whicvh I need to investigate further.
Also regarding an index, isn’t a seek better than a scan? I am serious in asking, I am still pretty new. Thanks!

Yes, I would say a SEEK is better than a SCAN. FTS catalogs can be rebuilt automatically on a schedule. Based on the usage/traffic patterns of the web app, you should be able to configure this appropriately if you eventually decide to go the FTS way. Profiling would come in handy here. I would suggest you do more performance with a larger sample of query test cases before you make up your mind. Data distribution can sometimes play havoc with your ‘benchmarks’. Remember to factor in issues like actual server load, execution plan caching and realisitic ‘network’ latency between your real clients and the server. Happy Testing. Nathan H.O.
Moderator
SQL-Server-Performance.com

"…it does look like the query runs twice as fast, with 90% less reads and about 60% less CPU when using a fulltext index. However, even though this is *mostly* not an OLTP there is still an issue of rebuilding the catalogs whicvh I need to investigate further." Well, in this case, it does seem that FTS is the way to go! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regarding rebuilding the FT Catalogs, and assuming SQL Server 2000, you can use "Change Tracking" with "Update Index in Background" and not have to worry about running a scheduled rebuild (Incremental Population) of the FT Catalog and get near real-time updates. Re: "You are wrong and should test it before you disagree with me." Yes, I have tested it, but it was sometime ago and I believe I was using SQL Server 7.0 and on a small table. I’ll re-test this again with SQL Server 2000 and a larger table and post my results.

]]>