Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2002
    Posts
    87

    Exclamation Unanswered: Slow query with LIKE

    Hello everyone..
    I've got a little bit trouble with a query I need on a table with has > 1.5 million rows.
    I need to do a LIKE query on a varchar(150) field in it - it's indexed, but that doesn't seem to help...

    LIKE '%USA%'
    -> takes about 6-15 seconds
    LIKE 'USA%'
    -> takes about 1 second


    I need the '%USA%' query, but I can't seem to get it any faster than those 6 seconds.

    Does anybody have a tip how I could fasten up this thing?


    Thanks!!

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Slow query with LIKE

    RE:
    Hello everyone..
    I've got a little bit trouble with a query I need on a table with has > 1.5 million rows. I need to do a LIKE query on a varchar(150) field in it - it's indexed, but that doesn't seem to help...
    LIKE '%USA%'
    -> takes about 6-15 seconds
    LIKE 'USA%'
    -> takes about 1 second

    I need the '%USA%' query, but I can't seem to get it any faster than those 6 seconds. Does anybody have a tip how I could fasten up this thing? Thanks!!


    Q1 Does anybody have a tip how I could fasten up this thing?
    A1 If this is really more of a design issue, e.g. (and each row contains some country); migrating to a schema with a populated, indexed seperate Country column would be expected to yield better perfromance.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    What data is in the field you are searching on ? Why do you have to use like ? Take a look at your execution plan - a table scan is being used in your query - which is very expensive.

  4. #4
    Join Date
    Oct 2002
    Posts
    9

    Re: Slow query with LIKE

    I will assume that you are looking in a field that has data like the following: "I live in the USA." If you have a string data type (varchar, char, text, etc.) and will need to search against this column often, I would suggest that you look into using MS Full text search engine. Creating search catalogues on that many rows will add quite a bit more data to your hard drive, but your need for quicker queries seems like that might be the appropriate trade off.

  5. #5
    Join Date
    Jul 2002
    Posts
    87

    Re: Slow query with LIKE

    Originally posted by britton
    I will assume that you are looking in a field that has data like the following: "I live in the USA." If you have a string data type (varchar, char, text, etc.) and will need to search against this column often, I would suggest that you look into using MS Full text search engine. Creating search catalogues on that many rows will add quite a bit more data to your hard drive, but your need for quicker queries seems like that might be the appropriate trade off.
    I've already tried trough full-text search catalogs - the problem is, that you cannot search for a *WORD* - it seems full text only supports BEGINS ( CONTAINS(Field,' "TEXT*" ') ) - correct?

    It are sub title fields for an article - e.g. you have a main article and 10 sub-articles with titles (e.g. hard-drive - sub 1: 1GB - sub 2: 2GB and so on...)...
    now I would like my customers to be able to do a search on that table - but as written above - the query takes 6-12 seconds (doesn't matter how much results there are) and takes full cpu (4 processors, 2.5GB RAM) - so I cant take this costs for such a search engine....

  6. #6
    Join Date
    Jul 2002
    Posts
    87
    Originally posted by rnealejr
    What data is in the field you are searching on ? Why do you have to use like ? Take a look at your execution plan - a table scan is being used in your query - which is very expensive.
    the data is varchar(150) - mainly descriptions... for main articles
    table design
    idSub - identity of sub-article (numberic - identity field)
    idMain - identity of main article (numeric)
    Title - varchar(150) - contains title of sub-article

    I'm pretty new to SQL server - where can i take a look at the execution plan - and how does this affect my ASP query?

    Thanks!

  7. #7
    Join Date
    Jul 2002
    Posts
    87

    Re: Slow query with LIKE

    Q1 Does anybody have a tip how I could fasten up this thing?
    A1 If this is really more of a design issue, e.g. (and each row contains some country); migrating to a schema with a populated, indexed seperate Country column would be expected to yield better perfromance. [/SIZE][/QUOTE]

    nope, I think I've put this the wrong way - the fields I want to search are title fields of a sub-article, I need to search LIKE *SEARCH* because my customers should be able to find:
    Harddisk - 5GB
    and
    Disk Drive - 1.44 MB - IDE
    with the query *DISK*
    with FULLTEXT-Search I cannot do this (didn't find out how - just got DISK* searches) and LIKE queries are pretty slow...

    Thanks for your reply!

  8. #8
    Join Date
    Oct 2002
    Posts
    9
    I guess you are right about not being able to preface a search condition with a wild card in MS Full Text Search. That sucks.

    To look at a visual of the query plan, open your Query Analyzer cut and paste your query into the window and hit "CTRL + L" (there is also a button at the top to do this).

  9. #9
    Join Date
    Jul 2002
    Posts
    87
    You were right - it uses a Table-Scan, but is it possible to prevent this?

  10. #10
    Join Date
    Oct 2002
    Posts
    9
    No. Prefacing a search string with a wild card is an automatic table scan.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    Sorry for the delay in response - but I have been traveling. Anyway, britton is correct - an index is only useful if it knows what to search for, and it starts from the beginning of your string and goes until it hits the %. Since it has no starting point, it has to do an table scan. That is why when you did 'abc%' it was so fast.

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    FYI - Take a look at the sql books online and look for "Designing an Index". This will give you an actual statement regarding using wildcards.

    The following is an excerpt from that article:
    "Queries using LIKE comparisons can benefit from an index if the pattern starts with a specific character string, for example 'abc%', but not if the pattern starts with a wildcard search, for example '%xyz'."

  13. #13
    Join Date
    Jul 2002
    Posts
    87
    okay, but isnt it strange that I've just upgraded to a (est) 5 times faster server (before i had a self-built-server, now a real dell machine w. 4 cpus and over 1.5 GB Ram) and the same query doesn't run faster????

  14. #14
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb

    RE: okay, but isnt it strange that I've just upgraded to a (est) 5 times faster server (before i had a self-built-server, now a real dell machine w. 4 cpus and over 1.5 GB Ram) and the same query doesn't run faster????
    Q1 Isn't it strange that I've just upgraded to a (est) 5 times faster server (before i had a self-built-server, now a real dell machine w. 4 cpus and over 1.5 GB Ram) and the same query doesn't run faster?
    A1 Not necessarily, what is likely happening is that (due to the incessant table scan nature of the query) the bottleneck is actually IO related.

    S1 If that is the case, (you can get an idea by enabling diskperf, the y switch, and monitoring IO related parameters, que lengths, page faults, etc.); you would probably get better performance by implementing strategies to maximize IO.

    S2 Some possibilities (examples) that might help in maximizing IO for the offending table include:
    i PIN the table if you have sufficient operational RAM
    ii Alter swap file parameters (if you are getting a lot of disk thrashing related to your current OS swap files),
    iii Partition the 'offending' table e.g. (to a DB file on different filegroup) to a file that resides on an extreamly fast RAID 10 array (a RAM disk might perform even better, but that has potentially fatally serious ramifications for DB integrity)

  15. #15
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb Re: Slow query with LIKE

    [SIZE=1
    Q1 Does anybody have a tip how I could fasten up this thing?
    A1 If this is really more of a design issue, e.g. (and each row contains some country); migrating to a schema with a populated, indexed seperate Country column would be expected to yield better perfromance.

    nope, I think I've put this the wrong way - the fields I want to search are title fields of a sub-article, I need to search LIKE *SEARCH* because my customers should be able to find:
    Harddisk - 5GB
    and
    Disk Drive - 1.44 MB - IDE
    with the query *DISK*
    with FULLTEXT-Search I cannot do this (didn't find out how - just got DISK* searches) and LIKE queries are pretty slow...
    Thanks for your reply! [/SIZE]
    RE: Thanks for your reply!
    S1 You are welcome.

    S2 I don't fully understand your design, however I think I would likely consider implementing a "KeyWord" table to address your performance issues.

    S3 Implementing a KeyWord Table amounts to the following:

    i Insert each word (character strings seperated by spaces) into the KeyWord table along with a FK identifying the original source monstrosity i.e., (the offending VarChar 150 column) that is causing the performance issue.

    ii When a search is passed from the web client e.g.( *USA* or *DISK*) search the (fully indexed) KeyWord table "WordColumn" e.g.(Select MonstrosityFK From KeyWord Where [WordColumn] = 'USA' or [WordColumn] = 'DISK')

    iii Return the original source monstrosity to the web client (based on a MonstrosityFK Join to the original source table)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •