What is the best / best practice for Upsert 5000+ strings without Merge in SQL Server?

Question

I have a web application that receives about 50 hits per second, and with each hit I raise about 10 records in a central SQL Server database. About every 3 seconds, I raise 5,000 lines for one incoming connection.

I currently have a stored procedure that accepts XML as a parameter. I insert INSERT into my main table from my XML, where the row field does not match, and then updates the whole table with the values ​​from my XML.

The operation is not slowed down by any means, but I really would like to know how to do it. I am working on SQL Server 2005, so I do not have a MERGE operation.

Answer 1

I would do UPDATE first, otherwise you will update the rows you just inserted

SELECT .. INTO #temp FROM (shredXML)

BEGIN TRAN

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

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

COMMIT

I would also think about changing the XML in the temp table and using SQLBulkCopy. We found this to be more efficient than parsing the XML as a whole by more than a few hundred lines. If you cannot change this, did you first mess up the XML into a temporary table?