HAVING & Bind Variables
Submitted by dave on Tue, 11/22/2011 - 17:34
Had an odd one come across my desk today. I have seen Bind variables used in the WHERE clause thousands of times. I have seen Binds in the SELECT lists hundreds of times. I have seen it used in the HAVING clause zero times... until today when it turns out it was causing a performance issue.
With the Bind variable in the HAVING clause we were seeing an execution plan with lots of Full Table Scans and 12+ second runtimes. When editing the query and using a literal in the HAVING clause we saw 100ms or less performance and very efficient Execution Plans.
I chased down Bind Variable datatypes to make sure no implicit conversions were sneaking in and wrecking my execution plans. Found nothing there so continued with a 10053 trace. The 10053 trace for the Bind in HAVING was 4X larger than the one with the literal in the HAVING. I won't go into a full description here, but I can tell you the Bind/HAVING scenario was causing the optimizer to at least CONSIDER a lot more possibilities when it parsed the query.
The only difference in HAVING processing I could find was below (see highlighted line):
HAVING clause with bind variable in expression:
HAVING selectivity: 6.9706e-05 -> GROUPS: 1 SORT resource Sort statistics Sort width: 178 Area size: 156672 Max Area size: 31457280 Degree: 1 Blocks to Sort: 16 Row size: 258 Total Rows: 496 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 10995455 Total Temp space used: 0HAVING clause with literal expression:
HAVING selectivity: 6.9706e-05 -> GROUPS: 1 SORT resource Sort statistics Sort width: 178 Area size: 156672 Max Area size: 31457280 Degree: 1 Blocks to Sort: 1 Row size: 271 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 10795355 Total Temp space used: 0I rewrote the statements to logical equivalents that kept the 2 bind variables but eliminated the HAVING clause. They all now run in
Tags:
Add new comment