SQL Server Performance

WHERE expression with LIKE '%

Discussion in 'T-SQL Performance Tuning for Developers' started by utiger, Jan 20, 2003.

  1. utiger New Member

    I have to tune a query of an SQL Server DB. The problem is, that the WHERE expression is build according to the request from the client.

    A Query could look like this:
    Select top 100 ' ',
    LOCAL_ID,
    UNIT_STATUS_CD,
    KIND_LOCAL_CD,
    STREET_TX,
    ZIP_CD,
    TOWN_TX,
    MUNICIPALITY_CD,
    CANTON_CD,
    NOGA_CD,
    OID,
    UPPER_NAME_TX
    FROM BurWeb.BurWebDbo.TI_LOCAL_UNIT
    WHERE UPPER_NAME_TX LIKE ('%ABB%')

    or also like this:

    Select top 100 ' ',
    LOCAL_ID,
    UNIT_STATUS_CD,
    KIND_LOCAL_CD,
    STREET_TX,
    ZIP_CD,
    TOWN_TX,
    MUNICIPALITY_CD,
    CANTON_CD,
    NOGA_CD,
    OID,
    UPPER_NAME_TX
    FROM BurWeb.BurWebDbo.TI_LOCAL_UNIT
    WHERE UPPER_NAME_TX LIKE ('%GARAGE%')
    AND
    CANTON_CD = 'BE'

    Now I found out, that when I have only the column UPPER_NAME_TX in the WHERE expression the query runs faster with the table hint "WITH (INDEX(index_upper_name_tx))". When the WHERE expression is like the second example (with or without "upper_name_tx" and some other columns) then it is faster without the table hint.

    Does somebody have an idea how I can solve this performance problem without a table hint? If there is no other solution then I think the client application have to add this table hint to the select statement according to the WHERE expression.
  2. bradmcgehee New Member

    The problem is with the like % in the WHERE clause, such as %GARAGE%. When you have a wildcard as the first character in the search string, SQL Server, by default, can't use an index to locate the data you want, so a table scan must be performed, which hurts performance. If you use GARAGE%, then the query will be able to use an index because it can narrow down the number of rows.

    If you no choice but to use a like clause starting with a %, you might want to look into a third party product from www.imptechnology, which is designed to overcome such problems. You may also want to consider using the SQL Server Full-Text Search Service.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. utiger New Member

    Why does SQL Server, by default, not perform an index scan, in case the WHERE expression is like my first example? My experience is that such queries (only one column in the WHERE expression and with LIKE ('%XYZ')) are always faster with the "table hint" "WITH (INDEX(index_name))".
    Another question for me is whether it is better not to use hints. What are the disadvantages?
  4. x002548 New Member

    I guess it also would have to do with the distribution of your data. For example how many rows does CANTON_CD = 'BE' Have? As Brad mentioned, you're stuck with a scan because of Like '%anything'. My first question is why is it this way? Are you looking for any occurance of the word in the string? If it has to be that way. You might try this:

    Select * from (Select * from table where CANTON_CD = 'BE')
    WHERE UPPER_NAME_TX LIKE ('%ABB%')


    At least this way, you've now reduced the scan to just the rows that have a CANTON_CD of 'BE'

    Happy Hunting

    Brett

  5. bradmcgehee New Member

    Hints have their pros and cons. Generally speaking, I avoid them if I can and there is another way around the problem. But if there are no other options, then a hint is appropriate. The biggest problems with hints is that you hard code the way the query is run. Over time, it is possible that your data will change enough that the hard coded hint may no longer be the best way to run the query. Because of this, if you use a hint, I suggest that you review it over time to see if is is still helping, or if it is hurting performance. I don't know why SQL Server performs the way it does in many cases. This information is generally hidden from us, unless you want to spend a lot of time figuring it out through trial and error.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page