LIKE '%value%' | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

LIKE ‘%value%’

Hi, <br /><br />I am trying to help a team member resolve this SQL performance issue. Any help would be appreciated. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />1. He is running a query that needs to search a string value for any occurance of another string value. Presently, the SQL is LIKE ‘%value%’, which does NOT use indexs due to the % at the beginning AND end of the value. <br />2. This lack of index usage is obviously a bad thing. <br />3. How could we restructure the query to still be able to search a string in that fashion, but make/help it use proper indexes? <br /><br />Any thoughts would be appreciated.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by scornelius</i><br /><br />Hi, <br /><br />I am trying to help a team member resolve this SQL performance issue. Any help would be appreciated. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />1. He is running a query that needs to search a string value for any occurance of another string value. Presently, the SQL is LIKE ‘%value%’, which does NOT use indexs due to the % at the beginning AND end of the value. <br />2. This lack of index usage is obviously a bad thing. <br />3. How could we restructure the query to still be able to search a string in that fashion, but make/help it use proper indexes? <br /><br />Any thoughts would be appreciated.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />I think we need to look at the whole join conditions since there is no magic to replace this wild characters. Can you post your query? Someone here should be able to suggest some improvements for your query.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Thanks,<br /><br />Name<br />——— <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
If the business alllows to use a the condition LIKE ‘value%’, you can make use of the indexex. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
]]>