July 26, 2012 at 12:22 pm
Hi All,
I have a query which is using Like operator 4 times in it. I look for "name" with specific criteria using LIKE in the where clause. Now,
when I use -- Name like '%MRJOHN%' it runs in 2 seconds.
when I use -- Name like '%MRJOHNS%' it takes 40 seconds or more sometimes. So, as soon as I add one more character (7th character) to the search criteria it starts taking more time. I tested everything else and seems having 7 or more chars in like clause drops the performance down significantly. Any suggestions?
Thanks in advance...
July 26, 2012 at 12:48 pm
Is it something where you could use full-text indexing? Like looking in a string made up of separate words, as opposed to a solid string.
FTI will work on, "Today, Mr Jones ate breakfast", it won't work on "TodayMrJonesAteBreakfast".
If FTI will work, it can be very, very fast for looking inside strings.
Otherwise, you're pretty much stuck with Like, and yes, it does get slow under certain circumstances.
- 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
July 26, 2012 at 12:53 pm
Thanks for the reply Gsquared.
Yes, this field has a firstname and it will always be like 'John' with almost no space. So, full text index wont probably work in this scenario. Also string is not long enough; its max 10 characters. But when it goes more than 7 then slows down otherwise works perfectly fine.
July 26, 2012 at 1:54 pm
Do you really need the first '%'?
That prevents you from using an index (As far as I know).
If you could drop it, the query would speed up.
July 26, 2012 at 1:59 pm
Luis, Yes application is designed in such a way that I need initial % too. I know using that will do index scan. But like I said when I use firstname like '%mrjohn%' its just 2 seconds. Same query when I use firstname like '%mrjohnS%' its 40 seconds. only difference is adding one more character in the search. Don't know what else to try?
July 27, 2012 at 6:35 am
The sudden cut-off at 7 characters will be something specific to the use of memory addressing, most likely. Nothing you can do about that in the code.
- 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply