Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Table Spool/Eager Spool?
40 views
Skip to first unread message
Cole Shelton
Mar 22, 2001, 9:19:00 AM3/22/01
to
Hi everyone--
What is the Table Spool/Eager Spool that I see in explain plans in
SQL2K? Is this just a fancy table read? Anyway it makes up a large
part of some of my longer queries, and I wanted to see if there was
anyway to lessen the cost it incurs.
Thanks
Cole
Itzik Ben-Gan
Mar 22, 2001, 10:04:22 AM3/22/01
to
It means that the rows from the input (which appears to the right of the
Table Spool/Eager Spool operator) are stored in a hidden temporary table
available only to the query at hand. This temporary table can be read
several times during the query. For example, in a GROUP BY query with the
CUBE option, the base aggregations that the GROUP BY generates are stored in
such a table, and that table is read several times during the query and
sorted in several different ways to calculate the various supper aggregates
that the CUBE option generates. You'll have one Table Spool/Eager Spool for
the write operation, and one for each read.
As for lessening the costs...the optimizer decided to create the spool table
to generate a more efficient plan than other plans it considered. I don't
think there's a way to lessen the cost of table spools (other than
optimizing the use of tempdb in general by making sure tempdb is on a fast
raid system, and that tempdb is not a bottleneck in your system and so
on...). With that in mind, maybe you should focus on whether indexing is
sufficient, or whether your query is written in the most efficient way.
Table Spool/Eager Spool operator) are stored in a hidden temporary table
available only to the query at hand. This temporary table can be read
several times during the query. For example, in a GROUP BY query with the
CUBE option, the base aggregations that the GROUP BY generates are stored in
such a table, and that table is read several times during the query and
sorted in several different ways to calculate the various supper aggregates
that the CUBE option generates. You'll have one Table Spool/Eager Spool for
the write operation, and one for each read.
As for lessening the costs...the optimizer decided to create the spool table
to generate a more efficient plan than other plans it considered. I don't
think there's a way to lessen the cost of table spools (other than
optimizing the use of tempdb in general by making sure tempdb is on a fast
raid system, and that tempdb is not a bottleneck in your system and so
on...). With that in mind, maybe you should focus on whether indexing is
sufficient, or whether your query is written in the most efficient way.
--
BG
Hi-Tech College, Israel
http://sql.hi-tech.co.il
"Cole Shelton" <csh...@swbell.net> wrote in message
news:3ABA09D4...@swbell.net...
0 new messages