March 12, 2009 at 1:51 am
Hi everyone
I'm still struggling with someone else's database: In the application the guy wrote a query for searching and it looks something like this:
select field1, field2, field3, field4, field5, field6, field7 from TABLE where field1 like '%SOMETHING%' or field2 like '%SOMETHING%' or field3 like '%SOMETHING%' or field4 like '%SOMETHING%' or field5 like '%SOMETHING%' or field6 like '%SOMETHING%' or field7 like '%SOMETHING%'.
This particular search is very slow (takes about 1 minute to return results) and I know that using LIKE is always slow, but the problem is that field1 to field7 are all VARCHAR(255). I can't make the fields smaller cause they're actually all filled to the max. This particular query is executed by about 100 users about 20 times per user a day, so about 2000 times in total a day.
My question is, how can I create an index for these 7 fields so speed up searching - I get an error when I try that the index can't be more than 900 bytes - what other option do I have?
Unfortunately I can't change the query in the application as we do not have the source code, so I need to optimise the SQL 2000 DB as much as possible for this query. The table has about 9 million records in it - we add about another 10 000 - 20 000 records each day to this particular table.
If I create an index for field1 and another index for field2 and another for field3 etc etc... will it help, or will it only help if all 7 fields are indexed together?
March 12, 2009 at 2:02 am
An index is not going to help you there. All of those LIKEs have leading wildcards. An index seek is not possible when there's a leading wildcard. That query will tablescan, there's no other way that it can run.
May I suggest you take a look at FullText indexing? It's designed for that kind of search problem. (However fulltext will require app changes)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2009 at 2:10 am
Hi Gail
Thanks, I also started thinking maybe tablescan will be the only solution here. I'll see if I can do a full text catalog and maybe write an external app to do this particular search.
By the way, I did what we discussed yesterday and gave all columns indexed primary keys and set relationships in place and today everyone can work and my life is back to normal again! Thanks a lot!
March 12, 2009 at 2:16 am
Do you mean to say u put indexes on the columns and the query worked better?
"Keep Trying"
March 12, 2009 at 2:21 am
Chirag (3/12/2009)
Do you mean to say u put indexes on the columns and the query worked better?
He's talking about a different problem that I advised him on yesterday.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2009 at 2:22 am
Yes.
March 12, 2009 at 2:26 am
Gail - I just saw an article by accident on you on Simple Talk - WOW you're like the only SQL Celebrity I know haha - ...and you're from South Africa - me too! I'm impressed.
March 12, 2009 at 2:33 am
barcode (3/12/2009)
...and you're from South Africa - me too! I'm impressed.
Whereabouts? If JHB, there's a usergroup meeting next week tuesday. Interested? (details on my blog)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2009 at 2:35 am
Yeah wow absolutely I'm from Bryanston - I'm gonna check it out now.
March 12, 2009 at 2:46 am
I checked it out and filled in a comment that I'll be joining and bringing a friend along. Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply