SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LIKE clause of SQL Query too slow

    Hey all, I'm having a problem with the QuickSearch feature of a site I have to maintain. It's using ASP to hit a SQL Server database. The DB is on a really, really slow server (a P Pro 180 with 224 MB RAM, believe it or not) and the query times out when connecting through ASP. The query that the site is using looks like this:

    Code:
    "SELECT * FROM table WHERE Field1 LIKE '%keyword%' OR Field2 LIKE '%keyword%'" [...] " OR FieldN LIKE '%keyword%'"
    WHERE N = number of fields in the table
    I tried making the Timeout period unlimited in SQL Server properites, but that didn't help. When I run the query in Query Analyzer, it works, but it takes about 35 seconds. Also, if I try the same query on the same database on a much, much faster server, it only takes 8 seconds. I'm going to move the site over to that server soon, but I would still like to get the query to be a little faster. We've got about 5000 records so far, and we're only about halfway through document uploading. Any advice?

    Thanks!
    -Joe

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because LIKE '%something' results in a full table scan. You should read the SQL Server documentation and/or database fundamentals.

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's what I'm doing right now. I understand why it's so slow, I just don't know how to make it quicker. I'm currently reading about Full-Text indexes, and if that sounds like something that will work, I might try that.
    -Joe

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, why are you running like queries like that then? How is your database designed? What are you trying to retrieve?

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to retrieve records that pertain to docs, but I want the user to be able to do one search on all fields. I've setup and enabled Full-Text indexing,and that works MUCH, MUCH more quickly, so I'll probably stick with that.

    Thanks for the advice!
    -Joe

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Full-text indexing is far faster, as you have noticed. I'd question the original design because it appears to be, ahem, nogood(TM). Glad you got it resolved.

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you are correct, the design is not quite up to my standards, but unfortunately, I did not design it, and the guy who did design it is gone now. I'd LOVE to redesign the db, but that would also require redoing a lot of the site (which is about as good as the database). If only there was more time.

    Ce la vie...
    -Joe


Bookmarks

Posting Permissions

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