Sql server bulk insert/update vs MERGE in insert or update scenario

Question

Hi.

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 ?

Answer 1

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.

Answer 2

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