Friday, January 27, 2006
Join Optimization
Tuning queries is a required skill for a Sybase specialist. One of the many ways to tune a query is to make sure that the very best query plan is being used. One clear advantage one plan may have over another is the join order that it uses when querying several tables. This advantage can be based on such factors as the where clauses, the number of rows in the tables, how they are stored, and the available indexes.
The Sybase query optimizer is in charge of evaluating the available join orders and choosing the best one. You know that we can determine what method it used using showplan:
set showplan on
But did you also know that we can use certain DBA tracing parameters to see the various plans it evaluated, and the estimated costs of each one? Here's how:
dbcc traceon(3604, 302, 310, 317)
Incidentally, the 3604 sends the output to the monitor, or to a specified file. That prevents it from filling up your error log (3605).
You may already be familiar with 302, but 310 and 317 are the trace commands that can tell you which join orders were considered and rejected, and what the costs were. Together they'll show you ALL the plans, so be prepared for a lot of output. Otherwise use 310 by itself.
There is a way to force the optimizer to choose a certain plan. Specifically, the join order specified by the query itself, in the "from" clause.
set forceplan [on|off]
The advantage is that it saves you the time the optimizer would have spent choosing a join order. The bad news is, that time is negligible anyway.
The disadvantage is that there is usually a reason the optimizer chooses its plan, and it can vary from execution to execution depending on the data and the variables of the query. Instead of forcing a plan you think is best, you may want to consider why the optimizer is choosing the "wrong" plan. That's where the aforementioned tracing can help.
As a quick fix, though, make sure your statistics and stored procedures are up to date! The optimizer depends on its accuracy for the best results.
update statistics table_name [index_name]
exec proc_name with recompile.
There is much more to know about join optimization than I can do justice to in this short article. If you find yourself examining join optimization as part of your query tuning, you'll definitely want to review Sybase's Performance and Tuning Guide, Chapter 38. It talks about dbcc 302, 310, 317 as well as the principles of join optimization.
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/ptallbk
I also found a really good article from Sybase entitled "Analyzing and Resolving Optimizer Problems/Symptoms":
http://www.sybase.com/detail?id=2602
The Sybase query optimizer is in charge of evaluating the available join orders and choosing the best one. You know that we can determine what method it used using showplan:
set showplan on
But did you also know that we can use certain DBA tracing parameters to see the various plans it evaluated, and the estimated costs of each one? Here's how:
dbcc traceon(3604, 302, 310, 317)
Incidentally, the 3604 sends the output to the monitor, or to a specified file. That prevents it from filling up your error log (3605).
You may already be familiar with 302, but 310 and 317 are the trace commands that can tell you which join orders were considered and rejected, and what the costs were. Together they'll show you ALL the plans, so be prepared for a lot of output. Otherwise use 310 by itself.
There is a way to force the optimizer to choose a certain plan. Specifically, the join order specified by the query itself, in the "from" clause.
set forceplan [on|off]
The advantage is that it saves you the time the optimizer would have spent choosing a join order. The bad news is, that time is negligible anyway.
The disadvantage is that there is usually a reason the optimizer chooses its plan, and it can vary from execution to execution depending on the data and the variables of the query. Instead of forcing a plan you think is best, you may want to consider why the optimizer is choosing the "wrong" plan. That's where the aforementioned tracing can help.
As a quick fix, though, make sure your statistics and stored procedures are up to date! The optimizer depends on its accuracy for the best results.
update statistics table_name [index_name]
exec proc_name with recompile.
There is much more to know about join optimization than I can do justice to in this short article. If you find yourself examining join optimization as part of your query tuning, you'll definitely want to review Sybase's Performance and Tuning Guide, Chapter 38. It talks about dbcc 302, 310, 317 as well as the principles of join optimization.
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/ptallbk
I also found a really good article from Sybase entitled "Analyzing and Resolving Optimizer Problems/Symptoms":
http://www.sybase.com/detail?id=2602
