What is the most efficient / best practise to Upsert 5000+ rows without Merge in SQL Server?

Question

I have a web application which receives about 50 hits per second, and on each hit I am upsert'ing around 10 records in a central SQL Server database. Roughly once every 3 seconds I am upserting 5000+ rows for a single inbound connection.

Currently I have a stored procedure which takes XML as a parameter. I do an INSERT into my main table from my XML where a row field doesn't match, then update the whole table with values from my XML.

The operation isn't slow by any means, but I really would like to know the best way to do this. I am running on SQL Server 2005 so I don't have the MERGE operation.

Answer 1

I would do the UPDATE first otherwise you'll update the rows you've just inserted

SELECT .. INTO #temp FROM (shredXML)

BEGIN TRAN

UPDATE ... FROM WHERE (matches using #temp)

INSERT ... SELECT ... FROM #temp WHERE NOT EXISTS

COMMIT

I'd also consider changing the XML to a temp table and use SQLBulkCopy. We've found this to be more efficient then parsing XML generally for more than a few hundred rows. If you can't change this then do you shred the XML into a temp table first?