与ADO.Net的SQL事务 [英] SQL Transaction with ADO.Net

问题描述

我是不熟悉C#的数据库交互方法，我试图借助SqlCommand和SqlConnection对象借助SqlTransaction在一个循环中向数据库中写入10000条记录，并在5000年后提交.处理需要10秒钟.

I am new to Database interection with C#, I am trying to writing 10000 records in database in a loop with the help of SqlCommand and SqlConnection objects with the help of SqlTransaction and committing after 5000. It is taking 10 seconds to processed.

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

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

我的问题是:
1.为什么ADO.Net执行中存在逻辑读取?
2.交响动作有些颤抖吗?
3.为什么在Management Studio中不可用?
4.如果我想在数据库上进行非常快速的插入事务，那将采用什么方法呢?

Above code is giving me only 1000 rows write/sec in database.

But when i tried to implement same logic in T-SQL and execute it on Database with SqlManagement Studio the it gave me 10000 write/sec.
When I compare the behaviour of above two approch then it showes me that while executing with ADO.Net there is large number of Logical reads.

my questions are:
1. Why there is logical reads in ADO.Net execution?
2. Is tansaction ahve some hand shaking?
3. Why they are not available in case of management studio?
4. If I want very fast insert transactions on DB then what will be the approach?

推荐答案

在很多情况下总是如此. SQL调用比进行SQL调用慢.这就是为什么您不应该做自己正在做的事情的原因.相反，我将要推送的数据放入XML文档中，然后使用OpenXML将其传递给SQL Server.

It''s always going to be the case that making a ton of SQL calls is slower than making one. That''s why you should never do what you''re doing. Instead, I''d put the data I was pushing in to an XML document and use OpenXML to pass it to SQL Server.

这篇关于与ADO.Net的SQL事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！