SQL Server Performance

TEXT data type search takes too long

Discussion in 'Performance Tuning for DBAs' started by sql_er, Jan 31, 2008.

  1. sql_er New Member

    Guys,I have a table with ~500,000 records. It has a few columns - most of them INT types, but one of them is a TEXT data type. I need to search the TEXT column for certain strings.

    I have been doing searches like this:
    SELECT A.ID
    FROM A
    WHERE B LIKE '%ABC%'
    The return is usually ~ 100 records.Sometimes this query takes 10 seconds, but at other times the SAME query runs for 10 minutes and does not complete. I have been trying to figure out what is causing it, but still have no idea.

    So, my questions:

    1. Is there an efficient way of string search or this is the only thing I can do?
    2. Why would the same query take seconds at times, but minutes at other times?
    I checked to see if any heavy jobs were running at the time the query was taking long, but could not find anything. I wonder if I am overlooking something.
    Please advise.
    Thanks in advance!
  2. chopeen Member

    How much data is stored in these TEXT columns? Is it rather like 100 or 200 characters or thousands of them?
    What kind of data is it?
    [quote user="sql_er"]1. Is there an efficient way of string search or this is the only thing I can do?[/quote]
    You can use Full Text Search.
    You have to remember that regular indexes cannot be used when you use LIKE '%ABC%', so a full table scan has to be performed.
    [quote user="sql_er"]2. Why would the same query take seconds at times, but minutes at other times?[/quote]
    Many reasons - the table may be locked is some transaction (it doesn't have to be a job; it's enough that a few people run a query against this table at the same time), query optimizer may produce a different execution plans as the data changes (does this data change a lot?).

Share This Page