query plan
-
Does anyone have a good explanation of how the query plan works. We are
checking through some queries here at work and are finding that if we take a
query that contains a few joins and several statements in the where clause,
if we make a self join on one of the tables, we get a different query plan
and it runs faster than the query without the self join even though they
return the same result set. The slow plan always seems to return hash
tables. This is frustrating as it seems there is no consistency as to how
sql server creates its plans.
Thanks,
Eddie
-
The key to query plans are what steps did the Query Manager decide to take to get the results. For instance is the query uses a table scan instead of and index scan or better an index seek you need to look back at your query to see what could change that to make it run faster. You can also see the order of operations in the execution which if you expect a certain order you may need to move your join around and if this does not work do a subquery on what will return the smallest set then allow the rest outside. It really takes watching the query plan to become familiar with what is going on and seeing the options you may have. It is not uncommon for self joins to be helpfull but usually it is something specific in that join that is the key to why. The best you can do is read what each action means and what actions are the best. Sorry this may not have been of much help but the key to a Query Plan is what is SQL doing then deciding if that is what you want it to do. If not then what changes can you make to correct this (indexes, like or =, join methods, etc).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Sometimes it is even easier than re-arranging the joins.
We had a query that should have returned one result. The query contained one single inner right join.
Using the Query Anlayzer it took around one second to return the result set (one record). Using the Enterprise Manager it again took one second to return the result.
Running the same query in the Websphere Application over the Merant JDBC driver it took around 15 Minutes.
The Query Plan wasn't much help, because the plan was the same for each query.
What we did to speed up the result set was to add the DISTINCT clause to the query. Et voilá, the query needed only a second to return the result.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply