0

I am trying to figure out the best way to design my C# application which utilizes data from a SQL Server backend.

My application periodically has to update 55K rows each one at a time from a loop in my application. Before it does an update it needs to check if the record to be updated exists.

If it exists it updates one field. If not it performs an insert of 4 fields.

The table to be updated has 600K rows.

  1. What is the most efficient way to handle these updates/inserts from my application?
  2. Should I create a data dictionary in c# and load the 600K records and query the dictionary first instead of the database?
  3. Is this a faster approach?
  4. Should I use a stored procedure?
  5. What’s the best way to achieve maximum performance based on this scenario?
Milligran
  • 3,031
  • 9
  • 44
  • 56
  • 1
    Does this upsert occur as the result of some user interaction? If so, it might be worthwhile to see if the business rules allow for some latency to be introduced as a result of running the process as part of some scheduled service during off-hours. Either way, you'd still want to optimize performance if there was some bottleneck. – Jonathan S. Feb 14 '12 at 03:38
  • Recently asked question, see here: http://stackoverflow.com/questions/8981451/efficient-sql-update-command-for-many-rows/8981498#8981498 – RBarryYoung Feb 14 '12 at 03:45

3 Answers3

1

You could use SqlBulkCopy to upload to a temp table then have a SQL Server job do the merge.

Austin Salonen
  • 49,173
  • 15
  • 109
  • 139
0

You should try to avoid "update 55K rows each one at a time from a loop". That will be very slow.

Instead, try to find a way to batch the updates (n at a time). Look into SQL Server table value parameters as a way to send a set of data to a stored procedure.

Here's an article on updating multiple rows with TVPs: http://www.sqlmag.com/article/sql-server-2008/using-table-valued-parameters-to-update-multiple-rows

bryanmac
  • 38,941
  • 11
  • 91
  • 99
0

What if you did something like this, instead?

By some means, get those 55,000 rows of data into the database; if they're not already there. (If you're right now getting those rows from some query, arrange instead for the query-results to be stored in a temporary table on that database. (This might be a proper application for a stored procedure.)

Now, you could express the operations that you need to perform, perhaps, as two separate SQL queries: one to do the updates, and one or more others to do the inserts. The first query might use a clause such as "WHERE FOO IN (SELECT BAR FROM @TEMP_TABLE ...)" to identify the rows to be updated. The others might use "WHERE FOO NOT IN (...)"

This is, to be precise, exactly the sort of thing that I would expect to need to use a stored procedure to do, because, if you think about it, "the SQL server itself" is precisely the right party to be doing the work, because he's the only guy around who already has the data on-hand that you intend to manipulate. He alone doesn't have to "transmit" those 55,000 rows anywhere. Perfect.

user106701
  • 175
  • 3