INSERT上的SqlCommand运行缓慢 [英] SqlCommand slow on INSERT

问题描述

我有一个ac＃程序循环遍历DB2数据库上的表。


每次迭代它都会将数据分配给SqlParameter中的值

集合。命令文本是对Sql Server

数据库的INSERT语句，使用.ExecuteQuery运行我将循环包含在

SqlTransaction中并在最后提交它。 />

我计划了这个程序，它每秒插入大约70条记录......我认为b $ b认为有点慢......所以我设置了一些Debug。 WriteLines显示

花费的时间。


DB2表的DataReader循环是即时的。得到每条记录几乎花了
。与分配值相同。


缓慢的步骤是SqlCommand的实际执行。但是，我还是运行了一个SQL跟踪并监视了

服务器上语句的执行情况。执行需要0秒。 SqlCommand本身增加了一个额外的0.01s到0.03s的

，它可以累积数百个
数千条记录。


所以唯一的开销就是在SqlCommand上运行.ExecuteQuery

对象（！）无论如何都要减少或减少这个开销，或者

设置可以影响性能。


我的意思是如果我的外部源和目标运行在0s - 我的代码

不应该增加运行命令的开销！

I have a c# program that loops through a table on a DB2 database.

On each iteration it assigns data to values in the SqlParameter
collection. The command text is an INSERT statement to a Sql Server
database, run with an .ExecuteQuery I enclosed the loop in a
SqlTransaction and commit it at the end.

I timed the program and it inserts about 70 records a second...which I
think is sort of slow...so I set up some Debug.WriteLines to show where
the time was being spent.

The DataReader loop to the DB2 table is instantaneous. Almost 0s spent
getting each record. Same with assigning values.

The slow step is the actual execution of the SqlCommand. However, I
also ran a SQL Trace and monitored the execution of the statement on the
server. It took 0s to execute. The SqlCommand itself is adding an
extra 0.01s to 0.03s which can add up over the course of hundreds of
thousands of records.

So the only overhead is running .ExecuteQuery on the SqlCommand
object(!) Is there anyway to reduce or minimize this overhead, or a
setting that can affect performance.

I mean if my external source and target are running at 0s - my code
shouldn''t be adding overhead to run a command!

推荐答案

我有一辆福特模型，当我拖着65英尺的船时没有爬山。我该怎么办？

啊，ADO.NET（或任何数据访问接口）不是为了实现

批量插入而设计的。虽然2.0比以往更快（使用批处理模式），但它比最快的DAI INSERT循环慢了几个数量级。使用

SqlBulkCopy或DTS我可以在大约20秒内移动500,000行。


-

____________________________________

William（Bill）Vaughn

作者，导师，顾问

Microsoft MVP

INETA演讲者
www.betav.com/blog/billva
www.betav.com

请只回复新闻组，以便其他人受益。

此帖子按原样提供。没有保证，也没有赋予任何权利。

__________________________________


" John Bailo" < JA ***** @ texeme.com>在消息中写道

news：tf ******************** @ speakeasy.net ...

I have a Model A Ford that does not climb hills that well when towing my 65''
boat. What should I do?
Ah, ADO.NET (or any of the data access interfaces) are not designed to do
bulk inserts. While the 2.0 is faster than ever (with batch mode), it''s
still orders of magnitude slower than the fastest DAI INSERT loop. Using
SqlBulkCopy or DTS I can move 500,000 rows in about 20 seconds.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"John Bailo" <ja*****@texeme.com> wrote in message
news:tf********************@speakeasy.net...

<我有一个循环遍历DB2数据库表的ac＃程序。

在每次迭代时，它都会将数据分配给SqlParameter
集合中的值。命令文本是一个到Sql Server数据库的INSERT语句，使用.ExecuteQuery运行我将循环包含在一个SqlTransaction中并在最后提交它。

我定时程序，它每秒插入约70条记录......我认为它有点慢...所以我设置了一些Debug.WriteLines来显示花费时间的地方。 />
DB2表的DataReader循环是即时的。差不多花费了0分来获得每条记录。与赋值相同。

缓慢的步骤是SqlCommand的实际执行。但是，我还运行了一个SQL跟踪并监视了
服务器上语句的执行情况。执行需要0秒。 SqlCommand本身增加了额外的0.01s到0.03s，可以累积数十万条记录。

所以唯一的开销就是运行.ExecuteQuery在SqlCommand对象上（！）
是否有减少或最小化这种开销，或者一个可以影响性能的设置。

我的意思是如果我的外部源和目标是在0运行 - 我的代码
不应该增加运行命令的开销！

I have a c# program that loops through a table on a DB2 database.

On each iteration it assigns data to values in the SqlParameter
collection. The command text is an INSERT statement to a Sql Server
database, run with an .ExecuteQuery I enclosed the loop in a
SqlTransaction and commit it at the end.

I timed the program and it inserts about 70 records a second...which I
think is sort of slow...so I set up some Debug.WriteLines to show where
the time was being spent.

The DataReader loop to the DB2 table is instantaneous. Almost 0s spent
getting each record. Same with assigning values.

The slow step is the actual execution of the SqlCommand. However, I also
ran a SQL Trace and monitored the execution of the statement on the
server. It took 0s to execute. The SqlCommand itself is adding an extra
0.01s to 0.03s which can add up over the course of hundreds of thousands
of records.

So the only overhead is running .ExecuteQuery on the SqlCommand object(!)
Is there anyway to reduce or minimize this overhead, or a setting that can
affect performance.

I mean if my external source and target are running at 0s - my code
shouldn''t be adding overhead to run a command!

>我有一辆型号A福特，当我拖着65英寸的

> I have a Model A Ford that does not climb hills that well when towing my 65''

船时，它不会很好地爬山。我该怎么办？


在模型A上放置较小的轮胎。


我不是John，但感谢SqlBulkCopy的提示！我们中的一些人没有得到很多机会偷看战壕，找到2.0中的新珠宝。


-Mike

" William（Bill）Vaughn"写道：

我有一辆型号A福特，在拖着我的65英寸船时不会爬山。我该怎么办？
啊，ADO.NET（或任何数据访问接口）不是为了进行批量插入而设计的。虽然2.0比以往更快（使用批处理模式），但它仍然比最快的DAI INSERT循环慢几个数量级。使用SqlBulkCopy或DTS我可以在大约20秒内移动500,000行。

-
____________________________________
William（Bill）Vaughn
作者，导师，顾问
微软MVP
INETA演讲者
www.betav .com / blog / billva
www.betav.com <请回复新闻组，以便其他人可以受益。
此帖子按原样提供。没有保证，也没有赋予任何权利。
__________________________________

John Bailo < JA ***** @ texeme.com>在消息中写道
新闻：tf ******************** @ speakeasy.net ...

boat. What should I do?
Put smaller tires on the Model A.

I''m not John, but thanks for the tip on SqlBulkCopy! Some of us don''t get
very many chances to peek over the trenches to find the new jewels in 2.0.

-Mike
"William (Bill) Vaughn" wrote:
I have a Model A Ford that does not climb hills that well when towing my 65''
boat. What should I do?
Ah, ADO.NET (or any of the data access interfaces) are not designed to do
bulk inserts. While the 2.0 is faster than ever (with batch mode), it''s
still orders of magnitude slower than the fastest DAI INSERT loop. Using
SqlBulkCopy or DTS I can move 500,000 rows in about 20 seconds.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"John Bailo" <ja*****@texeme.com> wrote in message
news:tf********************@speakeasy.net...

我有一个循环遍历DB2数据库表的ac＃程序。

在每次迭代时，它都会将数据分配给SqlParameter
集合中的值。命令文本是一个到Sql Server数据库的INSERT语句，使用.ExecuteQuery运行我将循环包含在一个SqlTransaction中并在最后提交它。

我定时程序，它每秒插入约70条记录......我认为它有点慢...所以我设置了一些Debug.WriteLines来显示花费时间的地方。 />
DB2表的DataReader循环是即时的。差不多花费了0分来获得每条记录。与赋值相同。

缓慢的步骤是SqlCommand的实际执行。但是，我还运行了一个SQL跟踪并监视了
服务器上语句的执行情况。执行需要0秒。 SqlCommand本身增加了额外的0.01s到0.03s，可以累积数十万条记录。

所以唯一的开销就是运行.ExecuteQuery在SqlCommand对象上（！）
是否有减少或最小化这种开销，或者一个可以影响性能的设置。

我的意思是如果我的外部源和目标是在0运行 - 我的代码
不应该增加运行命令的开销！

I have a c# program that loops through a table on a DB2 database.

On each iteration it assigns data to values in the SqlParameter
collection. The command text is an INSERT statement to a Sql Server
database, run with an .ExecuteQuery I enclosed the loop in a
SqlTransaction and commit it at the end.

I timed the program and it inserts about 70 records a second...which I
think is sort of slow...so I set up some Debug.WriteLines to show where
the time was being spent.

The DataReader loop to the DB2 table is instantaneous. Almost 0s spent
getting each record. Same with assigning values.

The slow step is the actual execution of the SqlCommand. However, I also
ran a SQL Trace and monitored the execution of the statement on the
server. It took 0s to execute. The SqlCommand itself is adding an extra
0.01s to 0.03s which can add up over the course of hundreds of thousands
of records.

So the only overhead is running .ExecuteQuery on the SqlCommand object(!)
Is there anyway to reduce or minimize this overhead, or a setting that can
affect performance.

I mean if my external source and target are running at 0s - my code
shouldn''t be adding overhead to run a command!

好​​的，我很好奇。


为什么BULK INSERT这么快？我的意思是，它是不是通过

SQL DBMS本身并以某种方式直接写入.mdf文件？什么

是它使用的机制？

另外，在四进制上运行我的代码（SQL 2000，W2K）我得到了5000
$ 10 $ b记录在10-14s（或每秒约450到500 recs）。不是太破旧了！

威廉（比尔）沃恩写道：

Ok, I''m curious.

Just why is BULK INSERT so fast? I mean, does it not go through the
SQL DBMS itself and somehow write directly to the the .mdf file? What
is the mechanism it uses?

Also, running my code on a quad proc (SQL 2000, W2K) I am getting 5,000
records in 10-14s ( or about 450 to 500 recs per second ). Not too shabby!
William (Bill) Vaughn wrote:

我有一辆模型A福特，当我拖着我的65'时没有很好地爬山。
船。我该怎么办？
啊，ADO.NET（或任何数据访问接口）不是为了进行批量插入而设计的。虽然2.0比以往更快（使用批处理模式），但它仍然比最快的DAI INSERT循环慢几个数量级。使用SqlBulkCopy或DTS我可以在大约20秒内移动500,000行。

I have a Model A Ford that does not climb hills that well when towing my 65''
boat. What should I do?
Ah, ADO.NET (or any of the data access interfaces) are not designed to do
bulk inserts. While the 2.0 is faster than ever (with batch mode), it''s
still orders of magnitude slower than the fastest DAI INSERT loop. Using
SqlBulkCopy or DTS I can move 500,000 rows in about 20 seconds.

这篇关于INSERT上的SqlCommand运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！