how to optimize using SQL Server Merge statement for bulk records
2 posters
Page 1 of 1
how to optimize using SQL Server Merge statement for bulk records
I am using Merge statement in SProc to do all the modification on the target on comparing the data with the source. I have over 4000000 records in the source (which is view object) and on executing the SProc which uses the MERGE statement takes almost an hour for execution. I tried to dump the records in the view into a temporary table and then use that temporary table in the MERGE statement for source and tried to execute the Merge as a batch of 500000 records using Loop in the SProc, now that the execution of Merge is quite faster, but it takes about 25 mins to dump the temporary table which still costs the time to almost an hour... Is there any other better way of optimizing it? Appreciate your suggesstions.
Vikneshraj- Posts : 1
Join date : 2012-05-02
Re: how to optimize using SQL Server Merge statement for bulk records
I don't like the merge statement in SQL Server for big datasets.
I would try updating everything that is different, using a where statement to limit the records being updated to just those that are in fact different. And then appending any new records.
I tend not to create temp tables. I tend to created permanent tables in a staging area that I can truncate or drop/recreate. The truncate takes no time at all plus having the permanent makes it easier for me to track issues.
I would try updating everything that is different, using a where statement to limit the records being updated to just those that are in fact different. And then appending any new records.
I tend not to create temp tables. I tend to created permanent tables in a staging area that I can truncate or drop/recreate. The truncate takes no time at all plus having the permanent makes it easier for me to track issues.
Jeff Smith- Posts : 471
Join date : 2009-02-03
![-](https://2img.net/i/empty.gif)
» SCD using MERGE statement
» SSIS and the SQL 2008 MERGE Statement for Insert, Update and Delete
» How to load a Slowly Changing Dimension Type 2 with one SQL Merge statement in Oracle
» How to deal with non-durable NK with split/merge records
» To get max of top 10 records in SQL SERVER
» SSIS and the SQL 2008 MERGE Statement for Insert, Update and Delete
» How to load a Slowly Changing Dimension Type 2 with one SQL Merge statement in Oracle
» How to deal with non-durable NK with split/merge records
» To get max of top 10 records in SQL SERVER
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|