1

I created MERGE statements to transfer differences with a minimal set of required columns from production db into a staging database. The merged tables are planned to be used by different reporting and analysis scenarios (readonly). The whole process of getting the differences into the staging tables should be fast (at least a lot faster then the current way of getting the whole table contents entirely every day by doing a DELETE followed by INSERT INTO for all data regardless of the amount of indeed changed data that I estimate to be about 5%).

Here is a sample:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET NOCOUNT ON

IF NOT exists(select 1 from StagingDB.sys.tables t join StagingDB.sys.schemas s on t.schema_id = s.schema_id WHERE t.name like 'A' and s.name like 'staging') 
BEGIN
        SELECT Top 0 [ID],[OID]
        INTO [StagingDB].[staging].[A]
        From [LiveDB].[dbo].[A];
END;

MERGE INTO [StagingDB].[staging].[A] AS Target
USING ( 
    SELECT [ID], [OID] From [LiveDB].[dbo].[A] where X = 0
) AS Source ([ID],[OID])
ON (Target.[ID] = Source.[ID])
WHEN MATCHED AND (Target.[OID] <> Source.[OID]) THEN
    UPDATE SET [OID] = Source.[OID]
WHEN NOT MATCHED BY TARGET  THEN
    INSERT([ID],[OID]) VALUES(Source.[ID],Source.[OID])
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE;


IF NOT EXISTS(
    SELECT * FROM sys.indexes ix Where ix.name = 'PK_A' AND ix.object_id = OBJECT_ID('[StagingDB].[staging].[A]')
)
BEGIN
        ALTER TABLE [StagingDB].[staging].[A] add constraint PK_A primary key CLUSTERED ([ID])
END

This runs VERY QUICKLY for most of my tables. Unfortunately there are two tables that have much more rows and additionaly many more columns of those two tables are being used lateron in the process of reporting, so I have to transfer much more data.

While I can run this kind of merge command for 71 tables in within less than a minute, the two problematic MERGE statements run for about 2 hours each. I could not yet find out why.

I suspect the amount of date but also the way I compare the tables differences. To find out if I have to run an UPDATE statement I do a comparison for each column that is included in the Merge Statement. So in case I have 10 colums the UPDATE condition of the MERGE looks like that:

WHEN MATCHED AND (Target.[OID] <> Source.[OID] OR Target.[OID1] <> Source.[OID2]  OR Target.[Text1] <> Source.[Text1] OR Target.[varcharlong1] <> Source.[varcharlong1] )  etc...

So it turns out that this kind of update condition check includes all colums and will not have an index. The staging tables always have a clustered primary key in uniqueidentifier and (so far) no additional indexes.

My question is: While the MERGE statement is running very quickly and efficient for most of our tables, it looks like this procedure is not suitable for the two larger tables including multiple columns. Am I using the MERGE statement correctly in this case using the OR clause to make sure the rows need to be updated or is there a better way? Is there a better way to quickly get the delta (changes) for a subset of columns into a staging table? all of my tables do have a ROWVERSION, maybe this could be used to figure out the rows that have been changed somehow?

  • You say that the staging tables always have clustered index, but it looks like this script would create a table as a heap if it doesn't already exist. Is there a chance a clustered index is missing? – mendosi Nov 10 '16 at 14:02
  • @mendosi sorry I wiped this part of the procedure out. It is chacking the existance and creates it initially. I'll add it to the question. – Magier Nov 10 '16 at 14:05
  • Could you share an estimated execution plan (I guess it would take a long time to collect actual plan). brentozar.com/pastetheplan – mendosi Nov 10 '16 at 14:09
  • Doesn't seem to be anything wrong with the way you wrote the merge. I have MERGE statements with many more items in the UPDATE condition than that that run fine over large tables. – mendosi Nov 10 '16 at 14:11
2

Don't use merge, it has always had performance problems on large tables. This has been an on going issue since the key word merge was put into SQL Server. See Microsoft Connect and one other site below:

https://connect.microsoft.com/SQLServer/feedback/details/635778/not-matched-and-matched-parts-of-a-sql-merge-statement-are-not-optimized

http://www.sqlservercentral.com/Forums/Topic1465931-391-1.aspx

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

  • ...and a handful of other reasons to be careful with MERGE... mssqltips.com/sqlservertip/3074/… – Aaron Bertrand Nov 10 '16 at 16:07
  • Thanks for this helpful input it's new to me. However since those statements are easy and fast for smaller (<100K rows) tables and I havea generic script to generate any kind of merge statement with different options I just love them. I could improve the performance a lot by using a ROWVERSION to compare the requirement to make UPDATES instead of OR-ing all column contens between source and target table because I have rowversion in almost every table. The two larger table ~10 Mio rows are still a problem maybe I will change the procedure for those two... – Magier Nov 14 '16 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.