how to optimize using SQL Server Merge statement for bulk records

View previous topic View next topic Go down

how to optimize using SQL Server Merge statement for bulk records

Post  Vikneshraj on Wed May 02, 2012 6:28 am

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

View user profile

Back to top Go down

Re: how to optimize using SQL Server Merge statement for bulk records

Post  Jeff Smith on Wed May 02, 2012 10:25 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum