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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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.


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:
  • 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

A very similar question was asked and answered on StackOverflow: How can I force a subquery to perform as well as a #temp table? To summarize,

  1. The most reliable method is simply to use a #temp table and materialize it yourself.
  2. Failing that, see Provide a hint to force intermediate materialization of CTEs or derived tables. The use of TOP(large_number) ... ORDER BY can often encourage the result to be spooled rather than repeatedly re evaluated. Even if that works however there are no statistics on the spool.
  3. Another option is to use a user-defined function. Multi-statement functions (as described in How to Share Data between Stored Procedures) appear to force the SQL Server to materialize the results of your subquery. In addition, they allow you to specify primary keys and indexes on the resulting table to help the query optimizer. This function can then be used in a select statement as part of your view. For example:
CREATE FUNCTION SalesByStore (@storeid varchar(30))
   RETURNS @t TABLE (title varchar(80) NOT NULL PRIMARY KEY,
                     qty   smallint    NOT NULL)  AS
   INSERT @t (title, qty)
      SELECT t.title, s.qty
      FROM   sales s
      JOIN   titles t ON t.title_id = s.title_id
      WHERE  s.stor_id = @storeid

SELECT * FROM SalesByStore('6380')
share|improve this answer

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


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


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.