June 20, 2025 at 6:02 pm
In Azure SQL DB, i want to merge records from the staging table to the production table. In the production table, there are 300 million records. In staging, Delta records are 40 million. there are 100+ columns in each table.... If the column value changes from staging to production, update the record. If it is a new record need to insert in the production table. Your help is much appreciated... with performance optimisation
June 20, 2025 at 9:48 pm
Merge is generally a slow operation. I think if you are wanting fastest performance, your best bet is to grab all of the delta's that are going to be INSERTs and insert them, then the remaining ones are UPDATEs, so do an update. Means you need to select from the 300 million record table with a good join or a good where clause so you are filtering things out properly.
I assume the delta contains ONLY new and updated records and no duplicates, correct? If so, then you just have to pick your primary key to filter/join the data.
I am assuming you are using SSIS to handle this. If this is correct, depending on your server configuration, you MAY get a performance boost by tossing the delta records into a table on the DB then have a stored procedure do the merging OR you may benefit from pulling everything into SSIS memory to do the work. Not sure which method would work better in your environment, but my GUESS with the data volumes is that putting it all in the DB to do the work will be a bit faster. But it depends on how much memory SSIS.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 23, 2025 at 8:40 am
I tend to use Brian's approach, doing an update then insert. One of the things that sometimes helps if it's a lot of records is I'll add a bit col to the table with the data I am merging from and mark records as I might batch them over. Then I can use that flag along with the PK/join columns to handle the update/delete/inserts as needed
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply