LIKE clause of SQL Query too slow

[jfiorini]

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:

"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!

[MattR]

Because LIKE '%something' results in a full table scan. You should read the SQL Server documentation and/or database fundamentals.

[jfiorini]

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.

[MattR]

Well, why are you running like queries like that then? How is your database designed? What are you trying to retrieve?

[jfiorini]

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!

[MattR]

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.

[jfiorini]

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...