C＃-SQL：如何执行一批StoredProcedure？

Edit:
My problem is not a problem anymore: I have redo my performances tests and I have do a fatal stupid error: I had forget a x1000 to get seconds from milliseconds :/ Sorry for that guys.
For info:
- I do some 1900 updates per second from my PC to the DataBase server on local network.
- 3.200 updates per second if the programs is on same machine than DB.
- 3.500 updates per second from my PC on the DataBase server I do not re-create and re-open a new SQLConnection.
- 5.800 updates per second with a batch text. For my 10.000 rows, if it take 5 seconds, it is ok for my programs. Sorry to have worry you.

编辑:我的问题不再是问题:我重做了我的表演测试并且我做了一个致命的愚蠢错误:我忘记了x1000从毫秒获得秒:/对不起那些家伙。有关信息: - 我从PC到本地网络上的DataBase服务器每秒进行1900次更新。 - 如果程序与DB相同,则每秒3.200次更新。 - 我的PC上DataBase服务器每秒3.500次更新我不会重新创建并重新打开一个新的SQLConnection。 - 批量文本每秒5.800次更新。对于我的10.000行,如果需要5秒钟,我的程序就可以了。很抱歉让您担心。

Actually, I use a SQL stored prodedure to create a row in my database to avoid SQL-injection. In C# I have the following method:

实际上,我使用SQL存储的prodedure在我的数据库中创建一行以避免SQL注入。在C#中我有以下方法:

public void InsertUser(string userCode)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
   {
      SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      sqlCommand.Parameters.Add(new SqlParameter("@UserCode", userCode));
      sqlConnection.Open();
      sqlCommand.ExecuteNonQuery();///0.2 seconds !ERROR HERE! 0.2ms here,NOT 0.2sec!!!
   }
}

It woks great when i have one or two rows to insert. But if i need to create 1.000 users and 10.000 products and 5000 pets, it is not the best solution: I will loose a huge time in netwok transport.

当我有一行或两行要插入时,它会很棒。但如果我需要创建1.000个用户和10.000个产品以及5000个宠物,那么这不是最佳解决方案:我将在网络传输中浪费大量时间。

I believe, without checkin it, that I can use just a limited amount of callback. So I do not want to call 10.000 times:

我相信,没有签入,我只能使用有限的回调。所以我不想打电话10.000次:

sqlCommand.BeginExecuteNonQuery()

Another way will be to create a batch text, but there is a SQL-Injection risk (and it is ugly).

另一种方法是创建批处理文本,但存在SQL注入风险(并且很难看)。

Does there is a 'SqlCommandList' object that manage that in .Net? How do I do large writing in database? What the good patern for that?

是否有一个'SqlCommandList'对象在.Net中管理它?如何在数据库中进行大量写作?对此有什么好处?

9 个解决方案

#1

This should run a little faster:

这应该运行得快一点:

public void InsertUser(IEnumerable<string> userCodes)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString), 
             SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection))
   {
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);
      sqlConnection.Open();

      foreach(string code in userCodes)
      {
          param.Value = code;
          sqlCommand.ExecuteNonQuery();///0.2 seconds
      }
   }
}

That will only open one connection and only create one command, even if you pass it 1000 users. It will still do each insert separately, though. And of course if userCode isn't a string you'll want to re-factor it appropriately. You may also want to look into SQL Server's BULK INSERT command.

这只会打开一个连接,只创建一个命令,即使你传递了1000个用户。不过,它仍将分别执行每个插入操作。当然,如果userCode不是一个字符串,你需要适当地重新考虑它。您可能还想查看SQL Server的BULK INSERT命令。

#2

What about UpdateBatchSize of the SQLDataAdaptor?

SQLDataAdaptor的UpdateBatchSize怎么样?

Our front end guys use this to batch a few 10,000 proc calls into chunks

我们的前端人员使用它来批量处理几万个proc调用

Article

MSDN

Our environment disallows "bulkadmin" rights so we can't use BULKINSERT/bcp etc

我们的环境不允许“bulkadmin”权限,所以我们不能使用BULKINSERT / bcp等

#3

Personally, if I regularly expect to do fairly large inserts (10,000 rows would definitely qualify...), I might consider having a separate table for incoming data, and use SqlBulkCopy to populate this table. Then you just execute a single stored procedure that moves the data over into the real table.

就个人而言,如果我经常期望做相当大的插入(10,000行肯定会有资格......),我可能会考虑为传入数据设置一个单独的表,并使用SqlBulkCopy来填充此表。然后,您只需执行一个存储过程,将数据移动到真实表中。

Another approach is to send down xml to the database, and use sqlxml to parse that (much easier with SQL2005 and above) - but this puts extra work on the db server.

另一种方法是将xml发送到数据库,并使用sqlxml来解析它(使用SQL2005及更高版本更容易) - 但这会在数据库服务器上进行额外的工作。

#4

If you really were concerned about this, you could (like you said) batch the commands up in strings like so:

如果你真的关心这个,你可以(就像你说的那样)用字符串批处理命令,如下所示:

var cmd = new SqlCommand();
cmd.Connection = sqlConnection;
for (int i = 0; i < batchSize; i++) {
    cmd.CommandText += String.Format("EXEC InsertUser @UserCode{0};", i);
    cmd.Parameters.AddWithValue("@UserCode" + i.ToString(), XXXXX);
    //... etc ...
}

Because in this scheme, you'd be using a parameter, you don't have more risk of SQL injection than if you used a stored proc. But I question whether or not you'll really save an appreciable amount of time doing this. IMO you should just keep it simple and do it the way you are doing it now.

因为在此方案中,您将使用参数,所以与使用存储过程相比,您没有更多的SQL注入风险。但我怀疑你是否真的会节省相当多的时间。 IMO你应该保持简单,就像你现在这样做。

#5

Based off Joel's answer, this is the fastest solution short of using either SqlBulkCopy or creating big strings of messy SQL and executing. (I added a transaction which will improve performance quite a lot)

根据Joel的回答,这是使用SqlBulkCopy或创建大字符串凌乱的SQL并执行的最快解决方案。 (我添加了一项可以提高性能的交易)

public void InsertUser(IEnumerabler<string> userCodes)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
   {
      sqlConnection.Open();
      SqlTransaction transaction = connection.BeginTransaction();
      SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
      sqlCommand.Transaction = transaction;
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);

      foreach(string code in userCodes)
      {
          param.Value = code;
          sqlCommand.ExecuteNonQuery();
      }      
      transaction.Commit();
   }
}

#6

I'm guessing this is a pretty old question.

我猜这是一个非常古老的问题。

With SQL Server 2008 the answer now is to use a Table Value Parameter. In short, pass in all your variables in a used defined type (table).

使用SQL Server 2008,现在的答案是使用表值参数。简而言之,将所有变量传入已使用的已定义类型(表)中。

In SQL, you can now process all of the records as individual items...Actually use set logic and get real performance.

在SQL中,您现在可以将所有记录作为单个项目处理...实际上使用设置逻辑并获得真实的性能。

#7

Have you considered passing an XML document to a stored procedure, then iterating through that to find the data to insert?

您是否考虑过将XML文档传递给存储过程,然后通过迭代来查找要插入的数据?

#8

"it is not the best solution: I will loose a huge time in netwok transport" Can you live with the loss?

“这不是最好的解决方案:我将在网络运输中浪费大量时间”你能忍受这种损失吗?

If this is something you don't do often, then does it matter? Measure it first, if it's a problem then fix it, personally probably I'd go with Marc Gravells table for incoming inserts. Another option is to fire the inserts asynchronously, then you're not waiting on each to finish before you start the next.

如果这是你不经常做的事情,那么它是否重要?首先测量它,如果它是一个问题,然后修复它,个人可能我会去Marc Gravells表进行插入。另一种选择是异步触发插入,然后在开始下一个插入之前不要等待每个插入完成。

It took me years, but finally I figured out that I shouldn't waste time optimising code that doesn't need it.

这花了我几年,但最后我发现我不应该浪费时间来优化不需要它的代码。

Hope this helps (even though I don't think it will, sorry).

希望这会有所帮助(即使我认为不会,对不起)。

#9

As per some of the answers above, the most noticeable performance increase for the least effort involves 2 changes to your existing code:

根据上面的一些答案,最小努力的最明显的性能提升涉及对现有代码的2次更改:

Wrapping the updates in a transaction

在事务中包装更新

Opening only one connection and calling the procedure multiple times with the different parameters.

仅打开一个连接并使用不同的参数多次调用该过程。

BULK INSERTs are an option but probably overkill for what you want to do.

BULK INSERT是一个选项,但可能对你想做的事情有点过分。

#1