Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Big Performance Problem with Merge Statement Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 2:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 218, Visits: 769
Problem Summary:

Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?

Details:

I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.

When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.

Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. However, I would like to understand what is happening here. I've read some forum posts that talk about various bugs in the MERGE operation. Has anyone run into this particular problem before? Does anyone know why this might happen?

The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.

Please let me know if you need any further information, and thank you very much for your help!


Server Configuration:
SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM


SQL Code:

Target Table:
USE TPS;

IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;

CREATE TABLE dbo.ParticipantResponse
( RegistrationID INT NOT NULL
,ParticipantID INT NULL
,ResponseDate SMALLDATETIME NULL
,CallNumber INT NULL
,ElementCategory NVARCHAR(80) NULL
,ElementNameID INT NULL
,ElementName NVARCHAR(80) NULL
,Response VARCHAR(3000) NULL
,SourceTable VARCHAR(30) NOT NULL
,SourceTableID INT NOT NULL
,SpecialistName VARCHAR(80) NULL
,ID BIGINT NOT NULL
,TransferKey INT NOT NULL
,CONSTRAINT [PK_ParticipantResponse]
PRIMARY KEY ( SourceTableID
,ID
)
)
WITH ( DATA_COMPRESSION = PAGE );


/* Index IX_PersonElement*/
CREATE NONCLUSTERED INDEX IX_PersonElement
ON dbo.ParticipantResponse ( RegistrationID ASC,
ParticipantID ASC,
ElementName ASC
)
WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );

/*Index IX_ParticipantResponse*/
CREATE NONCLUSTERED INDEX IX_ParticipantResponse
ON dbo.ParticipantResponse ( RegistrationID ASC,
ParticipantID ASC,
ResponseDate ASC,
ElementName ASC
)
WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );

/*Index IX_ParticipantID*/
CREATE NONCLUSTERED INDEX IX_ParticipantID
ON dbo.ParticipantResponse ( ParticipantID ASC )
WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );

/*Index IX_ParticipantCall*/
CREATE NONCLUSTERED INDEX IX_ParticipantCall
ON dbo.ParticipantResponse ( ParticipantID ASC,
CallNumber ASC
)
WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );

/*Index IX_ElementParticipant*/
CREATE NONCLUSTERED INDEX IX_ElementParticipant
ON dbo.ParticipantResponse ( ElementName ASC,
ParticipantID ASC
)
WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );


Source Table:
  IF OBJECT_ID('ETL.ParticipantResponseBuild') IS NOT NULL
DROP TABLE ETL.ParticipantResponseBuild;

CREATE TABLE ETL.ParticipantResponseBuild
( RegistrationID INT NOT NULL
,ParticipantID INT NULL
,ResponseDate SMALLDATETIME NULL
,CallNumber INT NULL
,ElementCategory NVARCHAR(80) NULL
,ElementNameID INT NULL
,ElementName NVARCHAR(80) NULL
,Response VARCHAR(3000) NULL
,SourceTable VARCHAR(30) NOT NULL
,SourceTableID INT NOT NULL
,SpecialistName VARCHAR(80) NULL
,ID BIGINT NOT NULL
,TransferKey INT NOT NULL
);


ALTER TABLE ETL.ParticipantResponseBuild
ADD PRIMARY KEY ( SourceTableID, ID );

CREATE INDEX [IX_TransferKey] ON ETL.ParticipantResponseBuild ( TransferKey )
WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON );

CREATE INDEX [IX_ParticipantID] ON ETL.ParticipantResponseBuild ( ParticipantID )
WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON );


Combined MERGE Statement:
USE TPS;

DECLARE @LastKeyCompleted INT = 476161;

SET STATISTICS IO,TIME ON;

MERGE INTO TPS.dbo.ParticipantResponse WITH (HOLDLOCK) AS Production
USING TPS.ETL.ParticipantResponseBuild AS Build
ON Production.SourceTableID = Build.SourceTableID
AND Production.ID = Build.ID
WHEN MATCHED AND Build.TransferKey > @LastKeyCompleted
THEN UPDATE SET
RegistrationID = Build.RegistrationID
,ParticipantID = Build.ParticipantID
,ResponseDate = Build.ResponseDate
,CallNumber = Build.CallNumber
,ElementCategory = Build.ElementCategory
,ElementNameID = Build.ElementNameID
,ElementName = Build.ElementName
,Response = Build.Response
,SourceTable = Build.SourceTable
,SpecialistName = Build.SpecialistName
,TransferKey = Build.TransferKey
WHEN NOT MATCHED BY TARGET
THEN INSERT
( RegistrationID ,ParticipantID ,ResponseDate ,CallNumber ,ElementCategory ,ElementNameID ,ElementName
,Response ,SourceTable ,SourceTableID ,SpecialistName ,ID ,TransferKey )
VALUES
( Build.RegistrationID ,Build.ParticipantID ,Build.ResponseDate ,Build.CallNumber ,Build.ElementCategory
,Build.ElementNameID ,Build.ElementName ,Build.Response ,Build.SourceTable ,Build.SourceTableID
,Build.SpecialistName ,Build.ID ,Build.TransferKey )
WHEN NOT MATCHED BY SOURCE AND Production.ParticipantID IN ( SELECT ParticipantID FROM ETL.ParticipantResponseBuild )
THEN DELETE;

Statistics for MERGE Statement:
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 83 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'ParticipantResponse'. Scan count 1, logical reads 162202, physical reads 0, read-ahead reads 137853, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 5, logical reads 41074, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParticipantResponseBuild'. Scan count 35964835, logical reads 72281824, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(9731 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 138981 ms, elapsed time = 143911 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.



Separate UPDATE, INSERT & DELETE Statements:
USE TPS;

DECLARE @LastKeyCompleted INT = 476161;

SET STATISTICS IO,TIME ON;

BEGIN TRANSACTION

UPDATE dbo.ParticipantResponse
SET RegistrationID = Build.RegistrationID
,ParticipantID = Build.ParticipantID
,ResponseDate = Build.ResponseDate
,CallNumber = Build.CallNumber
,ElementCategory = Build.ElementCategory
,ElementNameID = Build.ElementNameID
,ElementName = Build.ElementName
,Response = Build.Response
,SourceTable = Build.SourceTable
,SpecialistName = Build.SpecialistName
,TransferKey = Build.TransferKey
FROM dbo.ParticipantResponse AS Production
JOIN ETL.ParticipantResponseBuild AS Build
ON Production.SourceTableID = Build.SourceTableID
AND Production.ID = Build.ID
WHERE Build.TransferKey > @LastKeyCompleted;

INSERT dbo.ParticipantResponse
( RegistrationID ,ParticipantID ,ResponseDate ,CallNumber ,ElementCategory ,ElementNameID ,ElementName
,Response ,SourceTable ,SourceTableID ,SpecialistName ,ID ,TransferKey )
SELECT Build.RegistrationID
,Build.ParticipantID
,Build.ResponseDate
,Build.CallNumber
,Build.ElementCategory
,Build.ElementNameID
,Build.ElementName
,Build.Response
,Build.SourceTable
,Build.SourceTableID
,Build.SpecialistName
,Build.ID
,Build.TransferKey
FROM dbo.ParticipantResponse AS Production
RIGHT JOIN ETL.ParticipantResponseBuild AS Build
ON Production.SourceTableID = Build.SourceTableID
AND Production.ID = Build.ID
WHERE Production.SourceTableID IS NULL;

DELETE dbo.ParticipantResponse
FROM dbo.ParticipantResponse AS Production
LEFT JOIN ETL.ParticipantResponseBuild AS Build
ON Production.SourceTableID = Build.SourceTableID
AND Production.ID = Build.ID
WHERE Build.SourceTableID IS NULL
AND Production.ParticipantID IN ( SELECT ParticipantID
FROM ETL.ParticipantResponseBuild
);

COMMIT TRANSACTION

Statistics for Separate UPDATE, INSERT & DELETE Statements:
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 77 ms, elapsed time = 77 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'ParticipantResponse'. Scan count 0, logical reads 39541, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 5, logical reads 41074, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParticipantResponseBuild'. Scan count 1, logical reads 794, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(9731 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 483 ms.
Table 'ParticipantResponseBuild'. Scan count 9, logical reads 873, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParticipantResponse'. Scan count 9, logical reads 53986, physical reads 0, read-ahead reads 15, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 13, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 15290 ms, elapsed time = 1955 ms.
Table 'ParticipantResponse'. Scan count 2313, logical reads 7497, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 5, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParticipantResponseBuild'. Scan count 2, logical reads 332, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 102 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 87 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.




  Post Attachments 
Delete.sqlplan (5 views, 321.87 KB)
Insert.sqlplan (3 views, 321.87 KB)
Merge.sqlplan (19 views, 309.69 KB)
Update.sqlplan (5 views, 321.87 KB)
Post #1465931
Posted Friday, June 21, 2013 9:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 218, Visits: 769
Nothing? I was sure that one of those brilliant SQL veterans like Gail Shaw, Jeff Moden, Grant Fritchey, Steve Jones or Adam Machanic would have some insight into this problem.

Help me, Obi-Wan, you're my only hope!
Post #1466237
Posted Friday, June 21, 2013 9:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 4,929, Visits: 8,740
David Moutray (6/21/2013)
Nothing? I was sure that one of those brilliant SQL veterans like Gail Shaw, Jeff Moden, Grant Fritchey, Steve Jones or Adam Machanic would have some insight into this problem.

Help me, Obi-Wan, you're my only hope!


Gail, Grant and Steve are on stage at SQLInTheCity in London right now.
If you content yourself with my help, I can try to take a look.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1466258
Posted Friday, June 21, 2013 9:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 218, Visits: 769
Oh, I am perfectly content with your help. I was hoping one of these people might subscribe to Google alerts. I am pretty sure Adam Machanic does, for one.
Post #1466264
Posted Friday, June 21, 2013 9:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 4,929, Visits: 8,740
OK, looking at your code, looks like you're trying to do everything in a single pass (which is often a good idea).
In this case, you're using the WHEN NOT MATCHED predicate, which requires a Full Outer Join to include matched rows and unmatched rows in a single pass.
The individual DELETE and UPDATE statements do not suffer from this issue, so they actually perform better.

Hope this helps
Gianluca


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1466265
Posted Friday, June 21, 2013 9:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 4,929, Visits: 8,740
Also, the plans don't look very similar.
The DELETE plan is way different. It's a parallel plan, while the MERGE plan is serial. There's a hash match and... it's a different plan!
I think most of the magic is in there.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1466266
Posted Friday, June 21, 2013 9:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 218, Visits: 769
Hmmm ... if I understand you correctly, this problem severely limits the usefulness of MERGE.

The WHEN NOT MATCHED clause is required for INSERTs and DELETEs, but getting all of that data in a single pass essentially requires a full table scan. That might save you time if the individual DELETE and INSERT queries require a table scan anyway. It might also be OK if the table is fairly small.

If you are trying to do a targeted UPDATE/INSERT/DELETE on a large table, though, you are better off with separate statements.

Perhaps Microsoft implemented the MERGE operator only because it is part of the ANSI standard, and they wanted to check that box and to be able to say, "Yeah, our software does that, too." How well does MERGE perform in other implementations of SQL, Oracle? Does anyone know?

This seems like a pretty fundamental limitation on an operation that is trying to everything at the same time.
Post #1466277
Posted Friday, June 21, 2013 10:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 4,929, Visits: 8,740
I dont' think it's a limitation in the MERGE statement itself. It's just that the optimizer decided to implement yours in an inefficient way.
Actually, anti-joins are often a pain, not only with MERGE.

SQL Server's MERGE has some limitations and some bugs, but I can tell for sure that Oracle's MERGE is not any better.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1466284
Posted Friday, June 21, 2013 10:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 218, Visits: 769
spaghettidba (6/21/2013)
Also, the plans don't look very similar.
The DELETE plan is way different. It's a parallel plan, while the MERGE plan is serial. There's a hash match and... it's a different plan!
I think most of the magic is in there.


Interesting. That goes well with something I've found: if you make the query too complex, the optimizer has trouble creating an optimal execution plan. If that happens, it is better to break your query up into multiple steps.

Thank you for your insight.
Post #1466290
Posted Friday, June 21, 2013 9:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
For best performance with a MERGE, its a good idea to have both source and target tables indexed on the columns used to determine a MATCH. Other suggestions are found below.


http://msdn.microsoft.com/en-us/library/cc879317%28v=sql.105%29.aspx



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1466433
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse