October 23, 2008 at 6:17 am
HI All,
I have a query that when I just it from
WHERE x like '%abc%'
to
WHERE x like '%xyz%'
The query plan changes and the query takes 10 times longer?
What should I be looking for?
Also should I post the slow execution plan here for you guys?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 23, 2008 at 7:51 am
It's kind of tough without a little more information. Can you tell us how the query plan changes? Does it use different indexes or change from a seek to a scan? Is this the complete where clause? Nothing else in it? With the percent on both sides, it's sure to do a scan, because it doesn't have enough information do do a seek. It might be using different indexes for each run, but that wouldn't be my first guess unless other parts of the query change too. If you change it back, does it run faster again, or does it always run slow once you've put in '%xyz%'?
October 23, 2008 at 7:57 am
You also may need to update the statistics on the table, possibly using a fullscan.
😎
October 23, 2008 at 8:14 am
Hi all,
OK I've updated the stats
The where clause has two likes in it,
If I change the first like from %x% to %y% for example always makes the the one query run slower .
HEre are both execution plans
The first one is the faster %3663%
The second is always slower %exel%
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 23, 2008 at 8:35 am
OK might have found a solution but I'd like to know the impact.
Adding a OPTION (FORCE ORDER)
to the end of the query makes it run super fast.
What are the draw backs of this?
Should I be using it ?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 23, 2008 at 8:43 am
Have you looked at the execution plan with this option on the query to see what is different?
😎
October 23, 2008 at 8:44 am
no
looks like I might not beable to implement the force plan.
So I'm hoping someone could help me with the difference in plans I already supplied
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 23, 2008 at 8:59 am
Them are some pretty big query plans mister... 🙂 Any possibility that you might be able to tell me which table the where criteria is filtering from so that I can look at that reference without digging through all of the tables referenced here. 😀
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 23, 2008 at 9:01 am
Nevermind, grew a brain and pulled up the query. Looking now..
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 23, 2008 at 9:14 am
what indexes are on the file
October 23, 2008 at 9:19 am
hash aggreate is taking 28% on the second plan
and 0 the fast one
October 23, 2008 at 9:20 am
Ok, stats are updated, so that shouldn't be the issue. Is the view that this is referencing indexed? Seems like everything is going directly against the base tables so, wondering if a) making this an indexed view wouldn't help or b) going to the base tables to see what you can find there.
Sorry, pretty hard to go beyond looking for the obvious when looking at this stuff from a distance.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 23, 2008 at 9:23 am
The views are not indexed
I'll have a look at indexing them and see if it makes a difference.
just wierd that the force order works 🙁
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 23, 2008 at 9:36 am
It is not that surprising to me that it works, see that a lot as well but typically by changing the query a bit I can get the same plan to come up. Again, difficult to see when you can't see the underlying view BUT, I can see that it is not a simple plan for the view so, the engine has to make the best determination based on what it has at runtime.
Curious, why can't you use the force order?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 23, 2008 at 10:02 am
OK two problems I'm told.
1 - Can't use the force casue our website builds the query so we can't use the force all the time incase it affects other queries. a bad system design I know, but nothing I can do.
2 - I'm told I'm not allowed to use indexed views either cause of our server settings.
this all sucks he he he
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply