SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
-
Jun 6, 2002, 07:01 #1
- 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
Thanks!-Joe
-
Jun 6, 2002, 07:18 #2
- 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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Jun 6, 2002, 07:29 #3
- 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
-
Jun 6, 2002, 08:08 #4
- 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?
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Jun 6, 2002, 08:46 #5
- 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
-
Jun 6, 2002, 08:49 #6
- 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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Jun 6, 2002, 09:45 #7
- 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