几个对象的几个更新 - 性能 [英] Several updates from several objects - performance

问题描述

我想首先道歉，因为这不是我的自然语言，我可以用英语给出任何错误。

一点上下文：

我正在解析一个csv文件。文件中的每一行代表一个对象，之后用于更新SQL Server数据库中的表。我已经构建了解析器以及遍历每一行并将其转换为对象的过程。几个对象存储在一个数组中。



这是我希望你帮助我的问题：

我需要去通过数组中的每个对象，并针对数据库中的表进行更新。更新将仅在一个表中完成。每个对象都包含用于搜索我需要更新的记录和要更新的值的数据。此外，我需要连接3个表来到达目标表。

我应该遍历数组中的每个对象，并且对于每个对象运行针对数据库的更新，对数据库进行多次访问。我们在数组中拥有的对象数量（它可以是数组中的数千个对象），或者，我是否应该只使用一次数据库并运行更新？例如，在表示对象的数据库中创建一个新表，然后将每个对象作为该行中的一行插入，然后运行，让一个存储过程循环通过该表进行必要的更新。



PS我正在使用Visual Studio 2015和C＃4.5



非常感谢。



我尝试过：



我在MSDN（SQl服务器，C＃，LINQ），codeproject，stackoverflow和c＃corner。

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.

推荐答案

我推荐一个SQL-batch-statement：在一个SQL命令中用分号分隔的多个语句：

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

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 */

- >单个数据库调用，不需要临时表或存储过程。它没有（比）快得多。



如果你只想在每个语句成功时执行整个批处理，那么比较<$的返回值c $ c> .ExecuteNonQuery（）到您的记录计数，如果它们相等则只提交事务。

-> 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.

根据您的SQL Server版本而定重新使用，您可能需要查看表值参数 [ ^ ]。您将整个数据集作为单个参数传递，然后可以将其用作表变量来执行更新。

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.

这篇关于几个对象的几个更新 - 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！