Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

how to optimize using SQL Server Merge statement for bulk records

Go down

how to optimize using SQL Server Merge statement for bulk records Empty how to optimize using SQL Server Merge statement for bulk records

Post  Vikneshraj 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

Back to top Go down

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

Post  Jeff Smith 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

Back to top Go down

Back to top

- Similar topics

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