Table Spool/Eager Spool - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Table Spool/Eager Spool

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2001-12-19 : 13:04:58

Why does the Table Spool/Eager Spool take up 60% of the time it takes to generate the SELECT INTO statement ?

It was generating fine in about 3 seconds and now it takes about 90 seconds to generate.

The only thing I changed was a VIEW but the view speed difference is only about 1-2 seconds.

Any ideas?

- Onamuji

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-10-19 : 12:12:25

Hmm - a VERY OLD unanswered query that I found while googling eager spool. Strange that this has "disappeared" of radar. The only other hit on EAGER spool was not very forthcoming, so I am hoping that someone else can comment on this.

I (obviously) also seeing some interesting EAGER SPOOL'ing in a delete statement:


delete 	[FinRecon].[dbo].[gcmstransactions]
from 	gcms..offline_file a
	inner join 
	[FinRecon].[dbo].[gcmstransactions] b
	on b.offline_file_id = a.offline_file_id
where 	a.datetime_created < '2003-09-19 00:00:00.000'

With:


CREATE TABLE [dbo].[GCMSTransactions] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[de2] [varchar] (19) NULL ,
	[de6] [varchar] (12) NULL ,
	[de3s1] [varchar] (2) NULL ,
	[mti] [varchar] (4) NULL ,
	[de24] [varchar] (3) NULL ,
	[pds0025s1] [varchar] (1) NULL ,
	[datetime_processed_iwv] [datetime] NULL ,
	[msg_id_ica] [varchar] (33) NULL ,
	[de12] [varchar] (12) NULL ,
	[de43s1] [varchar] (83) NULL ,
	[de43s2] [varchar] (83) NULL ,
	[de43s3] [varchar] (83) NULL ,
	[de43s4] [varchar] (10) NULL ,
	[de43s5] [varchar] (3) NULL ,
	[de43s6] [varchar] (3) NULL ,
	[de25] [varchar] (4) NULL ,
	[de95] [varchar] (10) NULL ,
	[de42] [varchar] (15) NULL ,
	[de38] [varchar] (6) NULL ,
	[de41] [varchar] (8) NULL ,
	[de30s1] [char] (12) NULL ,
	[message_id] [int] NULL ,
	[offline_file_id] [int] NULL ,
	CONSTRAINT [PK_GCMSTransactions] PRIMARY KEY  CLUSTERED 
	(
		[id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
)

index_name               index_description                                       index_keys

gcmstransactions_PIX1    nonclustered located on PRIMARY                         offline_file_id
IX_Message_id            nonclustered, unique located on PRIMARY                 message_id
IX_msg_id_ica            nonclustered located on PRIMARY                         msg_id_ica
IX_mti                   nonclustered located on PRIMARY                         mti
PK_GCMSTransactions      clustered, unique, primary key located on PRIMARY       id

What I am getting as an access path:

--RG 20041019 - Access path:
-- Cost of 140:		10% = Index Scan on GCMSTransactions.PK_GCMStransaction (cost of 14.4). 565K row on gcmstransactions.
--		2x 	8% = Table Spool/Eager Spool (cost of 11.7)
--		3x	sorts (25 % -cost 47: GCMSTransactions.Msg_id_ica asc, ), (

--Adding: create index gcmstransactions_PIX1 on gcmstransactions (offline_file_id)
--improved cost to 112.	1% = Index Scan on GCMSTransactions.gcmstransactions_PIX1 (cost 1.39)
--		2x	4% = Table Spool/Eager Spool

Wonder what (if anything) I can do about the Eager Spool's ...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources