Long running queries due to table spooling

Yesterday a client of mine reported that one of the Stored procedures that they had written was running extremely slow. So after verifying that the statistics were updated and index were not fragmented, I started to look at the access plan that the SP was using. From the access plan it was evident that a table spooling operator was causing most of the delay. I found that the Table Spool physical operator scans the input and places a copy of each row in a hidden spool table (stored in the tempdb database and existing only for the lifetime of the query). If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

So any nested query or an Insert statement like 'INSERT INTO ....SELECT FROM....' would cause table spooling.  I could not find any index that would improve the performance, so I recommended the developer to use a temporary table instead of a subquery and that seemed to work perfectly. The temporary table increased the response of the query tremendously. It is strange that both the results of the subquery and the temporary table are indeed stored in the TEMPDB. So I am not sure what the reason is for the increased performance of the query. The use of temporary table and avoiding the tablespooling is just an observation. I don't know the reason and don't know it will work for everyone but wanted to share this experience.

For more details please refer to this excellent article explaining spooling.
http://www.simple-talk.com/sql/learn-sql-server/operator-of-the-week---spools,-eager-spool/