Optimizing insert/update

Last post 02-29-2008 12:00 PM by Sam_. 1 replies.

Page 1 of 1 (2 items)
	Sort Posts: Previous Next

02-28-2008 7:14 PM

mikesown
Joined on 02-28-2008
Posts 2

Optimizing insert/update

Reply Contact

I have a table with two columns, the first with a string and the second with an integer.

Given a set of input strings, I want to perform this operation 50,000+ times preferably in a single transaction: "If the string doesn't exist in the table, create a new row with the string in the first column and 1 in the second column. If the string does exist in the table, increment the second column by 1"

What I am doing now is using the UPDATE statement, and checking (via C#) the number of rows affected, and if this is zero, running an insert statement. Doing this 50,000 times, without an encapsulating transaction, is an expensive operation.

Is there any way to encapsulate this into SQL so that no outside interaction is needed to perform this operation, and so I can put 50,000 of these into a transaction and commit all at once?

Thanks,

Mike

02-29-2008 12:00 PM In reply to

Sam_
Joined on 02-14-2007
Washington DC
Posts 63

Re: Optimizing insert/update

Reply Contact

You can do this with far less SQL statements with a little pre-processing.

First use an IN query to identify which of the strings you have are brand new and which are existing.

Create a Dictionary<string, int> and count up within your 50,000+ new entries how many are for each string. Then for all the new ones, do a single insert for each.

Then for all the updates, do a single update for each adding the value in the dictionary to the existing value in the table.

That way, assuming the new 50,000+ entries already has a lot of duplication, you'll end up with far less than 50,000 total SQL statements.

Do it all within one transaction.

HTH,

Sam

We're hiring! B-Line Medical is seeking .NET Developers for exciting positions in medical product development in MD/DC. Work with a variety of technologies in a relaxed team environment. See ads on http://careerbuilder.com.

Page 1 of 1 (2 items)