使用 ADO.Net 进行 SQL 事务

Question

我是 C# 数据库交互的新手，我试图借助 SqlCommand 和 SqlConnection 对象在 SqlTransaction 的帮助下循环写入数据库中的 10000 条记录，并在 5000 条后提交。处理需要 10 秒。

SqlConnection myConnection = new SqlConnection("..Connection String..");
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = "exec StoredProcedureInsertOneRowInTable Param1, Param2........";
myCommand.Connection = myConnection;
SqlTransaction myTrans = myConnection.Begintransaction();
for(int i=0;i<10000;i++)
{
mycommand.ExecuteNonQuery(); 
if(i%5000==0) 
{
myTrans.commit();  
myTrans = myConnection.BeginTransaction();  
mycommand.Transaction = myTrans;
}
}

上面的代码在数据库中每秒只写入 1000 行。

但是当我尝试在 SQL 中实现相同的逻辑并使用 SqlManagement Studio 在数据库上执行它时，它给了我 10000 次写入/秒。 当我比较上述两种方法的行为时，它表明在使用 ADO.Net 执行时存在大量逻辑读取。

我的问题是： 1. 为什么ADO.Net执行中会出现逻辑读？ 2. 交易时是否有手抖？ 3. 为什么管理工作室不可用？ 4. 如果我想在数据库上非常快速地插入事务，那么该采取什么方法？ 。

有关数据库对象的更新信息

表： tbl_FastInsertTest 无主键，只有 5 个字段，前 3 个为 int 类型（F1,F2,F3），后 2 个（F4,F5）为 varchar(30) 类型

存储过程：

create proc stp_FastInsertTest 
{ 
@nF1 int,
 @nF2 int,
 @nF3 int,
 @sF4 varchar(30),
 @sF5 varchar(30) 
 }
 as  
 Begin
 set NoCOUNT on
       Insert into tbl_FastInsertTest
       {
         [F1],
         [F2],
         [F3],
         [F4],
         [F5]
       }
       Values
       {
         @nF1,
         @nF2,
         @nF3,
         @sF4,
         @sF5,
       } end
 --------------------------------------------------------------------------------------

SQL 块执行于SSMS--

当我在 SSMS 上执行以下代码时，它每秒写入次数超过 10000 次，但是当我尝试在 ADO 上执行相同的 STP 时，它每秒写入次数为 1000 到 1200 次

--同时读取没有锁

begin trans 
declare @i int 
set @i=0

While(1<>0) 
begin  
 exec stp_FastInsertTest 1,2,3,'vikram','varma'  
 set @i=@i+1 

 if(@i=5000)   
  begin    
   commit trans   
   set @i=0
   begin trans
  end

 end

Answer 1

如果您正在运行类似的内容：

exec StoredProcedureInsertOneRowInTable 'blah', ...
exec StoredProcedureInsertOneRowInTable 'bloop', ...
exec StoredProcedureInsertOneRowInTable 'more', ...

在 SSMS 中，这是一个完全不同的场景，其中所有这些都是单个批次。使用 ADO.NET，您需要为每个 ExecuteNonQuery 支付一次往返费用 - 实际上，它管理的 1000/s 给我留下了深刻的印象。

对于逻辑读取，可能只是查看查询计划缓存，但如果不了解有关 StoredProcedureInsertOneRowInTable 的更多信息，就无法评论是否正在执行特定于查询的操作。但我怀疑 SSMS 和 ADO.NET 之间有一些不同的 SET 条件，迫使它使用不同的计划 - 这是特别诸如持久计算索引列和从 sql-xml 字段“升级”的列等问题。

重新使其更快 - 在这种情况下，它听起来像一个表值参数 正是这样，但您还应该查看 此处有其他选项

Answer 2

• 对于高性能插入，请查看 SqlBulkCopy 类，如果它适合您，它应该很快。
• 正如 Sean 所说，使用参数化查询始终是一个好主意。

• 使用 StringBuilder 类，在单个查询中批处理数千个 INSERT 语句并提交事务是插入数据的一种行之有效的方法：

  <块引用>

  var sb=new StringBuilder();

  for(int i=0;i < 1000;i++)
  { 
    sb.AppendFormat("插入表(col1,col2)
  

  VALUES({0},{1});",值1[i],值2[i]);
  }

  sqlCommand.Text=sb.ToString();
  

  使用StringBuilder

• 您的代码对我来说看起来不正确，您没有在每个批次中提交事务。您的代码不断打开新事务。

• 在插入大量数据时删除索引并稍后添加它们始终是一个好习惯。索引会减慢你的写入速度。
• Sql Management Studio 没有事务，但 Sql 有，请尝试以下操作：

 开始交易 MyTransaction
    插入表（Col1，Col1）值（Val10，Val20）；
    插入表（Col1，Col1）值（Val11，Val21）；
    插入表（Col1，Col1）值（Val12，Val23）；
    提交交易

Answer 3

您需要使用参数化查询，以便可以处理和缓存执行路径。由于您使用字符串连接（颤抖，这很糟糕，谷歌 SQL 注入）来构建查询，SQL Server 将这 10,000 个查询视为单独的、单独的查询，并为每个查询构建一个执行计划。

MSDN：http://msdn.microsoft.com/en-us/library/yy6y35y8 .aspx 尽管您希望稍微简化其代码，并且必须重置命令上的参数。

如果您真的非常想快速获取数据库中的数据，请考虑使用 bcp...但您最好首先确保数据是干净的（因为没有真正的错误检查/处理。