Several updates from several objects - performance

Question

0.00/5 (No votes)

See more:

Hi,

I would like to start by apologising for any mistake i could give using English as it is not my natural language.
A bit of Context:
I'm parsing a csv file. Each line in the file represents an object that is used afterwards to update a table in a SQL Server database . I've built the parser already and the process that goes through each line and converts it into an object. The several objects are stored in an array.

Here is the issue I would like you to help me with:
I need to go through each object in the array and hit an update against a table in the database. The update will be done in one table only. Each object contains the data to search for the record I need to update and the value to update to. Also I need to join 3 tables to get to the target table.
Should I go through each object in the array and for each object run an updated against the database, hitting the database as many times as the number of objects we have in the array(and it can be thousands of objects in the array) or, should I get a way to go only once to the database and run the update? For example create a new table in the database representing the objects and then insert each object as a row in that table and afterwards run let say a stored procedure that cycles through that table doing the necessary updates.

P.S I'm using Visual Studio 2015 and C#4.5

Many thanks.

What I have tried:

I did readings on MSDN(SQl server,C#,LINQ), codeproject, stackoverflow and c#corner.

Posted 20-Apr-16 6:20am

jonatan_556

Updated 20-Apr-16 7:00am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Answer 1 · 2016-04-20T06:33:00

I recommend an SQL-batch-statement: Multiple statements, separated by semicolons, in a single SQL-command:

SQL

UPDATE myTable SET col1 = @p1 WHERE col2 = @p2;
UPDATE myTable SET col1 = @p3 WHERE col2 = @p4;
UPDATE myTable SET col1 = @p5 WHERE col2 = @p6;
/* etc */

-> A single database-call, no need for a temporary table or stored procedure. It doesn't get (much) faster than this.

If you only want to execute the whole batch if every single statement succeeds then compare the return value of .ExecuteNonQuery() to your record count and only commit the transaction if they're equal.

Richard Deeming · Answer 2 · 2016-04-20T07:00:00

Solution 2

Depending on the version of SQL Server you're using, you might want to look at Table-Valued Parameters[^]. You pass the entire set of data as a single parameter, which you can then use as a table variable to perform the update.

Posted 20-Apr-16 7:00am

Richard Deeming