Eager spool performance issue

Question

question

Sharon 1 asked Jan 6, '10

Eager spool performance issue

Hi,

I have the following table:

CREATE TABLE #MachineMessageLog  
(  
    [ProgID]    	[INT] NOT NULL,  
    [MainOperationID]   [INT] NULL,  		
    [OperationID]   [INT] NULL,		
    [SeqID] 	[INT] NOT NULL,  
    [Timestamp] 	[DATETIME] NOT NULL,  
    [TriggerID] 	[INT] NULL,
    [OperationTypeID]   [INT] NULL,  
    [ToolCode]  	[NVARCHAR](50) NULL,  
    [ToolGroupNo]   [INT] NULL,  
    [ToolDiameter]  [FLOAT] NULL  

    CONSTRAINT [PK_#MachineMessageLog] PRIMARY KEY CLUSTERED 
	(
		[SeqID] ASC
	)
	WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_#MachineMessageLog_TriggerID_SeqID] ON [#MachineMessageLog] 
(
	[TriggerID] ASC,
	[SeqID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

and I also the following query:

UPDATE #MachineMessageLog   
SET OperationID =   
(  
    SELECT MAX(b.SeqID)  
    FROM #MachineMessageLog b  
    WHERE b.TriggerID = 4  
    AND b.SeqID  <= #MachineMessageLog.SeqID  		
)

I am using this query in a stored procedure in different variation. When I use it with another column (both are not indexed) and TriggerID it works fine.When I use it like this it takes a very long time (minutes). I have executed the query with SET STATISTICS PROFILE ON and the results are:

61258 1 UPDATE #MachineMessageLog SET OperationID = (SELECT MAX(b.SeqID) FROM #MachineMessageLog b WHERE b.TriggerID = 4 AND b.SeqID <= #MachineMessageLog.SeqID )
61258 1 |--Clustered Index Update(OBJECT:([tempdb].[dbo].[#MachineMessageLog]), SET:([#MachineMessageLog].[OperationID] = [Expr1007])) 0 0 |--Compute Scalar(DEFINE:([Expr1007]=[Expr1005]))
61258 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([#MachineMessageLog].[SeqID]))
61258 1 |--Table Spool
61258 1 | |--Top(ROWCOUNT est 0)
61258 1 | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#MachineMessageLog]), ORDERED FORWARD)
61258 61258 |--Stream Aggregate(DEFINE:([Expr1005]=MAX(#MachineMessageLog.[SeqID] as [b].[SeqID])))
61256 61258 |--Top(TOP EXPRESSION:((1)))
61256 61258 |--Filter(WHERE:(#MachineMessageLog.[SeqID] as [b].[SeqID]<=[#MachineMessageLog].[SeqID] AND #MachineMessageLog.[TriggerID] as [b].[TriggerID]=(4)))

1876487084 61258 |--Table Spool
61258 1 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#MachineMessageLog] AS [b]), ORDERED BACKWARD)

as you can see, the second eager spool is problematic.

So my questions are: 1. what am I doing wrong and how can i improve performance (I've tried order by + top)? 2. how come the same query with different parameters behaves differently?

thanks in adavance for your help?

Sharon.

query performance

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-07T00:29:26Z

Scot Hauder answered Jan 7, '10

CREATE TABLE #MachineMessageLog2 
(  
    [ProgID]            [INT] NOT NULL,  
    [MainOperationID]   [INT] NULL,             
    [OperationID]   [INT] NULL,         
    [SeqID]     [INT] NOT NULL,  
    [Timestamp]         [DATETIME] NOT NULL,  
    [TriggerID]         [INT] NULL,
    [OperationTypeID]   [INT] NULL,  
    [ToolCode]          [NVARCHAR](50) NULL,  
    [ToolGroupNo]   [INT] NULL,  
    [ToolDiameter]  [FLOAT] NULL  

    CONSTRAINT [PK_#MachineMessageLog] PRIMARY KEY CLUSTERED 
        (
                [SeqID] ASC
        )
        WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_#MachineMessageLog_TriggerID_SeqID] ON [#MachineMessageLog2] 
(
        [TriggerID] ASC,
        [SeqID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

INSERT #MachineMessageLog2
SELECT * FROM [#MachineMessageLog]

UPDATE #MachineMessageLog   
SET OperationID =
(  
    SELECT TOP 1 b.SeqID
    FROM #MachineMessageLog2 b  
    WHERE b.TriggerID = 4  
    AND b.SeqID  <= #MachineMessageLog.SeqID   
    ORDER BY b.SeqID DESC      
)

DROP TABLE #MachineMessageLog2

Above isn't the answer, but it is interesting that reading from a separate table removes the evil spool...try this instead...

DECLARE @x int

UPDATE MachineMessageLog 
SET @x = OperationID = CASE WHEN TriggerID = 4 THEN SeqID ELSE @x END

I've made some assumptions about the SeqID in my test data, let us know if this doesn't work with your data

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-01-06T12:39:20Z

Scot Hauder 7 answered Jan 6, '10

Personally, I believe Eager Spool should be renamed "Evil Spool". I have experienced similar performance issues where a query that normally takes a few seconds will instead take several minutes. Normally I solve this with a carefully placed index. The Eager Spool seems to create a temp index and will process, in my case, hundreds of millions of rows from the temp db. This normally goes away when I create an appropriate index or rewrite the query. How many rows are in your table and what indexes do you currently have in place?

7

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Jan 06, 2010 at 01:39 PM

I'm pretty sure the fact that there's no index for SQL Server to work with, it's creating a temporary table, loading it up with data, and then figuring out what data to return.

1 ·

Sharon 1 commented · Jan 06, 2010 at 01:20 PM

Evil indeed... The table has about 60,000 rows, but the eager spool (generously) makes it around 18,000,000,000. i have clustered index on SeqID and another one on TriggerID, SeqID. Thanks.

0 ·

Sharon 1 commented · Jan 06, 2010 at 01:50 PM

Maybe you are right, but i can't seem to find any missing index. The only thing I could think of is that at the last line of the query plan it performs clustered index scan backwards.

0 ·

Peso commented · Jan 06, 2010 at 04:42 PM

Can you post the indexes on the temporary table?

0 ·

Sharon 1 commented · Jan 06, 2010 at 04:54 PM

Of course. I've added them to the table code.

0 ·

Show more comments

Answer 3 · 2010-01-06T16:47:56Z

Peso answered Jan 6, '10

Instead if MAX operation, does TOP(1) give you same result? For me, TOP(1) gives a much better plan Also, I have posted a CURSOR solution for you to test.

CREATE TABLE    #MachineMessageLog
    	(
    		ProgID INT NOT NULL,
    		MainOperationID INT,
    		OperationID INT,
    		SeqID INT NOT NULL,
    		[Timestamp] DATETIME NOT NULL,
    		TriggerID INT,
    		OperationTypeID INT,
    		ToolCode NVARCHAR(50),
    		ToolGroupNo INT,
    		ToolDiameter FLOAT
    	)

-- Original
UPDATE  m
SET m.OperationID =	(
    			SELECT	MAX(b.SeqID)
    			FROM	#MachineMessageLog AS b
    			WHERE	b.TriggerID = 4
    				AND b.SeqID  <= m.SeqID
    		)
FROM    #MachineMessageLog AS m

-- Peso
UPDATE  m
SET m.OperationID =	(
    			SELECT TOP(1)	b.SeqID
    			FROM		#MachineMessageLog AS b
    			WHERE		b.TriggerID = 4
    					AND b.SeqID  <= m.SeqID
    			ORDER BY	b.SeqID DESC
    		)
FROM    #MachineMessageLog AS m

-- Cursor
DECLARE curYak CURSOR FOR
    		SELECT		SeqID,
    				TriggerID
    		FROM		#MachineMessageLog
    		ORDER BY	SeqID

DECLARE @SeqID INT,
    @TriggerID INT,
    @Dummy INT

OPEN curYak

FETCH   NEXT
FROM    curYak
INTO    @Dummy,
    @TriggerID

WHILE @@FETCH_STATUS = 0
    BEGIN
    	IF @TriggerID = 4
    		SET	@SeqID = @Dummy

    	UPDATE	#MachineMessageLog
    	SET	OperationID = @SeqID
    	WHERE	CURRENT OF curYak

    	FETCH	NEXT
    	FROM	curYak
    	INTO	@Dummy,
    		@TriggerID
    END

CLOSE curYak
DEALLOCATE curYak

SELECT  *
FROM    #MachineMessageLog

DROP TABLE  #MachineMessageLog

7

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharon 1 commented · Jan 06, 2010 at 04:59 PM

It sounds like a good idea, but unfortunately I've tried it and it had no effect.

0 ·

Peso commented · Jan 06, 2010 at 06:59 PM

How well did the CURSOR code perform?

0 ·

Sharon 1 commented · Jan 07, 2010 at 07:22 AM

i didn't try it. I wanted to try without it at first.

0 ·

Peso commented · Jan 07, 2010 at 09:28 AM

For 60,000 records, the CURSOR may outperform the set-based alternatives.

0 ·

Sharon 1 commented · Jan 07, 2010 at 09:39 AM

I guess you are right, but the number of rows could easily be twice or even ten times bigger. The number of rows is depended on the time frame and this one is for one week.

0 ·

Show more comments

Answer 4 · 2010-01-07T12:02:43Z

Scot Hauder 9 answered Jan 7, '10

Sharon, can you post your improvement over the quirky update (which runs in under a second on my test data of 60,000 records). Is this a temp table in your solution? You may find an improvement in performace if you make it a table in the db. If you are always updating the entire table and this is only a week's worth, you will want to do the update in smaller batches (1000-5000 rows) at a time

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharon 1 commented · Jan 07, 2010 at 12:58 PM

Yes, it is a temp table. About the query performance, i specified the whole SP execution time by mistake. The specific times are 2 seconds for the improvement and 5 second for the quirky. Improvement is simply to add additional filtering: UPDATE #MachineMessageLog SET OperationID = ( SELECT MAX(b.SeqID) FROM #MachineMessageLog b WHERE b.TriggerID = 4 AND b.SeqID <= #MachineMessageLog.SeqID AND b.MainOperationID = #MachineMessageLog.MainOperationID AND b.ProgID = #MachineMessageLog.ProgID )

0 ·

Sharon 1 commented · Jan 07, 2010 at 01:00 PM

I dont know how to add code in a comment (yet) in this site. The additional filter is: AND b.MainOperationID = #MachineMessageLog.MainOperationID AND b.ProgID = #MachineMessageLog.ProgID

0 ·

Sharon 1 commented · Jan 07, 2010 at 02:55 PM

I have made additional testing and in all of my recent tests the quirky updated was faster by 10-15% then any other improvement.

0 ·

question