Menu
I need to find the best way to insert or update data in database using sql server and asp.net. It is a standard scenario if data exist it is updated if not it is inserted. I know that there are many topic here about that but no one has answered what i need to know.
So my problem is that there is really no problem when you update/insert 5k - 10k rows but what with 50k and more.
My first idea was to use sql server 2008 MERGE command, but i have some performance consideration if it will be 50k+ rows. Also i don't know if i can marge data this way based not on primary id key (int) but on other unique key in the table. (to be precise an product serial number that will not change in time).
My second idea was to first get all product serials, then compare the new data serials with that and divide it into data to insert and data to update, then just make one bulk insert and one bulk update.
I just don't know which will be better, with MERGE i don't know what the performance will be and it is supported only by sql server 2008, but it looks quite simple, the second option doesn't need sql 2008, the batches should be fast but selecting first all serials and dividing based on them could have some performance penalties.
What is you opinion, what to choose ?
644
The basic set-up data is as follows. We've purposely set up our source table so that the INSERTs it will do when merged with the target are interleaved with existing records for the first 500,000 rows. These indicate that MERGE took about 28% more CPU and 29% more elapsed time than the equivalent INSERT/UPDATE.
MERGE has more flexible OUTPUT . OUTPUT can refer to the merge source which is handy if you want the client to be able to match what it sent to what was actually inserted (e.g. IDENTITY values).
Both the MERGE and UPDATE statements are designed to modify data in one table based on data from another, but MERGE can do much more. Whereas UPDATE can only modify column values you can use the MERGE statement to synchronize all data changes such as removal and addition of row.
¶ Both 'Bulk insert with batch size' and 'Use single record insert' options are used for inserting records in a database table. The 'Bulk insert with batch size' option is used when you want the whole dataset to be loaded in batches of a specified size. Typically, larger batch sizes result in better transfer speeds.
Merge performace way better because "One of the most important advantage of MERGE statement is all the data is read and processed only once"
You dont need a primary key, you can join on one or more fields what makes your records unique
103
There should be no problem performing the merge on the serial number as you've described it. You may want to read Optimizing MERGE Statement Performance for Microsoft's recommended best practices when using MERGE.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
