March 25, 2008 at 10:33 am
I have a basic query that is searching a text field (nvarchar(25)) and I noticed when I use LIKE %nn% where nn is alphanumeric, the search times are going out to 12-15 seconds where if I specify anything longer than 2 characters the result comes back in less than a second.
Does anyone have any idea what could cause this?
March 25, 2008 at 10:43 am
What do you execution plans look like? You may be doing a table scan with 2 chars and maybe your 3 chars is using index seeks.
March 25, 2008 at 11:29 am
That's the really odd part. The execution plan and the client stats between both trials are almost identical. It is actually telling me that the client execution time is less than a second when it took over 5 mins to return the result set (which was 2 records).
March 25, 2008 at 11:45 am
Adam Haines (3/25/2008)
What do you execution plans look like? You may be doing a table scan with 2 chars and maybe your 3 chars is using index seeks.
Not sure how it could possibly be a seek with the leading % in the LIKE statement....
Is there a OPTION (FAST N) statement anywhere? All of these should end up creating some kind of table scan.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2008 at 11:57 am
Not sure how it could possibly be a seek with the leading % in the LIKE statement....
Is there a OPTION (FAST N) statement anywhere? All of these should end up creating some kind of table scan.
Your right, I missed that, however, the query should result in an index scan.
March 25, 2008 at 12:03 pm
Adam Haines (3/25/2008)
Not sure how it could possibly be a seek with the leading % in the LIKE statement....
Is there a OPTION (FAST N) statement anywhere? All of these should end up creating some kind of table scan.
Your right, I missed that, however, the query should result in an index scan.
Actually - we're both making assumptions there.
It will be some kind of scan, but what kind (whether table scan, clustered index scan, or "just plain" index scan) would depend on the indexing and what the query might have in the SELECT part....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2008 at 12:11 pm
Ahhhh wait. Now I'm getting somewhere. Based on what you just said I ran 3 different scenerios:
1) LIKE %nn%
2) LIKE %nn
3) LIKE nn%
1 and 2 took the usual 5 minutes and 3 took less than a second....why would 2 take longer than 3???
Does option 3 use an index seek and 1 and 2 would not?
March 25, 2008 at 12:20 pm
After taking a better look at the execution plan, in one instance it was trying to use the wrong index and in another it was using the correct one.
Which honestly just baffles me. I guess the search criteria creates a weight that helps the query analyzer choose which index to use. I forced the correct index in the query with the INDEX= in the WITH and now all is back to normal.
Ugh....database design may be an art but index tuning is some type of magic.....
March 25, 2008 at 12:27 pm
Chris Gierlack (3/25/2008)
Ahhhh wait. Now I'm getting somewhere. Based on what you just said I ran 3 different scenerios:1) LIKE %nn%
2) LIKE %nn
3) LIKE nn%
1 and 2 took the usual 5 minutes and 3 took less than a second....why would 2 take longer than 3???
Does option 3 use an index seek and 1 and 2 would not?
3 is the only one that can actually use indexing on the field, since it looks at the beginning of the field (meaning all of the "nn%" values would be bunched together in the index - think looking up all of the people whose last name start with "AB" in the phone book - they're all together in the alphabetical portion). The other ones would be spread all over the place in the index, so it essentiall doesn't get used efficiently.
If you wanted 2. to also be a scan, you could create a computed column that REVERSES the "real" column, create an index on the reverse, and you could then run a LIKE 'nn%' against the reversed value.
1. can't really be helped. That's always a scan. You might do some good with a FULL-text index, but then the query syntax changes altogether (you'd be using CONTAINS instead of LIKE).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2008 at 12:31 pm
Chris Gierlack (3/25/2008)
Ahhhh wait. Now I'm getting somewhere. Based on what you just said I ran 3 different scenerios:1) LIKE %nn%
2) LIKE %nn
3) LIKE nn%
1 and 2 took the usual 5 minutes and 3 took less than a second....why would 2 take longer than 3???
Does option 3 use an index seek and 1 and 2 would not?
The usual analogy for index use is a phone book. The difference between 2 and 3 is: Imagine having to look up everyone in the phone book whose last name starts with "nn". Pretty simple, right? You open to the right pages and you're good to go. Now imagine trying to look up everyone in the phone book whose last name ends in "nn". You have to start at the beginning and work your way through.
It's the same thing for indexes. "Starts with" is much easier to find than "contains" or "ends with".
If you routinely have to find "ends with", try creating a calculated column with the Reverse() function, and index that. Then do your select against that column instead of the main column. You'll see the difference.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2008 at 12:36 pm
You also have to keep in mind that the selectivity of an index determines whether or not it is used. There is a direct relationship between the the number of characters you put in your contain statement to the selectivity of an index. If you search for "AN", your query could return items like "an", "and", "ant" ext... ,but if you search for "ANTIDO", there is a lot less data in your table that contains that text, thus the selectivity goes up.
March 25, 2008 at 1:02 pm
Kinda funny. As your reply came in I had just finished running a selectivity analysis against that field and lo and behold it dropped way down under my 2 char criteria....should have done that from the start.
Guess you get so focused on one thing that you forget the basics.....
March 25, 2008 at 1:08 pm
Yeah, selectivity is always a good place to start. At least you have identified your problem and can now concentrate on resolving it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply