Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following query which join pivoted data of several very big tables and it runs very slow. I tried to created the temp table for each pivoting and it runs much faster. Is it a way to force index/table spooling without create temp tables?

select ...
from T1 ....
left join (select ... from (select ... from T2....) pivot (sum(C1) on C2 in (...))) p1 
    on ...
left join (select ... from (select ... from T3....) pivot (sum(...) on ... in (...))) p2
    on ...
left join (select ... from (select ... from T4....) pivot (sum(...) on ... in (...))) p3
    on ...
share|improve this question

2 Answers 2

up vote 1 down vote accepted

SQL Server does not have a force_spool hint.

You might be able to hand-"code" an execution plan that includes a spool and then force its use with a plan hint. However, I have not tried that method yet.


Edit:

Hand coding an execution plan is beyond the scope of this forum. Here are a few hints to get you started:

  • The SQL Server execution plan is represented as XML. You can think of it as a complex programming language.
  • The XML schema specification explains the syntax of this language: http://schemas.microsoft.com/sqlserver/2004/07/showplan/
  • within each execution plan you have a hierarchy of "RelOp" nodes with a single root node. This root node is where the execution starts. The relational operator (RelOp) is executed and requests rows from its child (or children). Those in turn execute to then recursively call their children.

The best way to get started is to read simple XML execution plans.

To get a spool in there you should be just able to inject a spool at any place that makes sense. The execution plan however will be checked by SQL Server to be valid for the query at hand and might get rejected.

Again, this is just a research suggestion and I have not yet tried it myself.

share|improve this answer
    
How to hand code an execution plan? –  u23432534 Oct 23 '13 at 16:44
    
See my edit above. –  Sebastian Meine Oct 23 '13 at 18:25

What's wrong with persisting the intermediate results to a temp table? It's often a solid approach when performance tuning. It may not give you the fastest query in comparison with a "perfect" in-lined query but will make for more predictable results.

I suspect you are running into a cardinality issue due to the complexity of the query. Do you have large discrepancies between your Estimated and Actual rowcounts in your execution plan? Can you post your Actual Execution Plan XML? In the meantime, one of the query hints might help, eg

OPTION ( HASH JOIN )

In my simple rig, this generated a quicker but more CPU intensive query. I can't really recommend cutting up your own execution plan. It would take me about 30 seconds to refactor your query to work with temp tables where you could spend hours messing around with execution plan xml and still not get a result.

share|improve this answer
    
For example, sometimes you need put the query in a view which doesn't support temp table. –  u23432534 Oct 24 '13 at 18:34
    
I can't think of a situation where you absolutely have to put things into a view. It's a design choice that you make. Options would be for example: stored proc, view, batch process to persist resultset to table, indexed view. There are pros and cons for each, eg batch process introduces latency / has to be updated on regular basis but would be quick, indexed views destroy insert/update/delete performance to underlying tables, not compatible with PIVOT etc I can't think of any cons for the stored proc option, other than the one you've mentioned, that they can't be easily in-lined. –  wBob Oct 25 '13 at 8:07
    
...but I don't think that's a massive problem. –  wBob Oct 25 '13 at 8:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.