插入1,000,000条记录的最有效方式？ [英] Most efficient way to insert 1,000,000 records?
问题描述
我需要在一天结束时在C＃
申请中插入超过500,000条记录。我需要尽快完成它。
我创建了一个存储过程并从ADO调用它来逐个插入。它有点慢（看起来比使用DTS包从文件导入要慢）。
只是一个普遍的问题，在ADO中，会是什么是这项工作最有效的方式。
。我通常按照我的描述去做。我正在使用.NET framework 1.1
和SQL Server 2000.
非常感谢
Chris
Chris，
我会批量发送参数...不幸的是，ADO.NET
在.NET 2.0之前没有得到SQL Server的命令批处理。
因此，我会尝试使用DTS。是什么意思？
文件的格式是什么？你可能会有更快的结果...
希望这会有所帮助。
-
- Nicholas Paldino [。 NET / C＃MVP]
- mv*@spam.guard.caspershouse.com
" chrisben" < CH ****** @ discussions.microsoft.com>在消息中写道
新闻：20 ********************************** @ microsof t.com ...
我需要在一天结束时在C＃
应用程序中插入超过500,000条记录。我需要尽快完成它。
我创建了一个存储过程并从ADO调用它来逐个插入。
它有点慢（看起来比较慢）而不是使用DTS包从
文件导入。
只是一个普遍的问题，在ADO中，最有效的方法是做什么这项工作。我通常按照我的描述去做。我正在使用.NET framework 1.1
和SQL Server 2000.
非常感谢
Chris
" chrisben" < CH ****** @ discussions.microsoft.com>在消息中写道
新闻：20 ********************************** @ microsof t.com ...
我需要在一天结束时在C＃
应用程序中插入超过500,000条记录。我需要尽快完成它。
我创建了一个存储过程并从ADO调用它来逐个插入。
它有点慢（看起来比较慢）而不是使用DTS包从
文件导入。
只是一个普遍的问题，在ADO中，最有效的方法是做什么这项工作。我通常按照我的描述去做。我正在使用.NET framework 1.1
和SQL Server 2000.
非常感谢
Chris
最有效，最快捷的方法是使用Sqlserver的bcp实用程序，你可以使用T-SQL BULK INSERT，但其他任何东西都会慢得多。
Willy。
" chrisben" < CH ****** @ discussions.microsoft.com>在消息中写道
新闻：20 ********************************** @ microsof t.com ...
我需要在一天结束时在C＃
应用程序中插入超过500,000条记录。我需要尽快完成它。
在C＃应用程序或C＃应用程序使用的数据库中？
我创建了一个存储过程并从ADO调用它来逐个插入。
它有点慢（似乎比使用DTS包从
文件导入慢）。
只是一个普遍的问题，在ADO中，会是什么是最有效的方式来完成这项工作。我通常按照我的描述去做。我正在使用.NET framework 1.1
和SQL Server 2000。
简而言之，没有，ADO.NET并不打算这样做。你有两个选择，使用带有SQL服务器的附带的
批量复制（bcp.exe）或作为Paldino建议的
（这也是我推荐的解决方案）使用DTS，DTS可以从
C＃编写脚本，这样你就可以灵活地根据需要进行更改。
只需从企业管理器创建一个DTS，选择写入包到磁盘和
以后你可以加载并执行它。
如果你需要代码，请告诉我，我在几个部署中这样做。
-
Ignacio Machin，
ignacio.machin AT dot.state.fl.us
佛罗里达州交通部
Hi,
I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.
I created a stored procedure and called it from ADO to insert one by one. It
is kind of slow (seems slower than using a DTS package to import from a file).
Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.
Thanks a lot
Chris
Chris,
I would send the parameters over in batches... Unfortunately, ADO.NET
doesn''t get command batching for SQL Server until .NET 2.0.
Because of this, I would try and use DTS. What is the format of the
file to begin with? You might have much faster results doing that...
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"chrisben" <ch******@discussions.microsoft.com> wrote in message
news:20**********************************@microsof t.com...Hi,
I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.
I created a stored procedure and called it from ADO to insert one by one.
It
is kind of slow (seems slower than using a DTS package to import from a
file).
Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.
Thanks a lot
Chris
"chrisben" <ch******@discussions.microsoft.com> wrote in message
news:20**********************************@microsof t.com...Hi,
I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.
I created a stored procedure and called it from ADO to insert one by one.
It
is kind of slow (seems slower than using a DTS package to import from a
file).
Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.
Thanks a lot
Chris
The most efficient and fastest way is using Sqlserver''s bcp utility, you can
also use T-SQL BULK INSERT, but anything else will be much slower.
Willy.
Hi,
"chrisben" <ch******@discussions.microsoft.com> wrote in message
news:20**********************************@microsof t.com...Hi,
I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.
In a C# app or in a DB used by a C# app?
I created a stored procedure and called it from ADO to insert one by one.
It
is kind of slow (seems slower than using a DTS package to import from a
file).
Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.
In short, none, ADO.NET is not intented for this. You have two options, use
bulk copy (bcp.exe ) that comes with SQL server or as Paldino suggested
(that is also my recommended solution) using DTS, DTS can be scripted from
C# so you will have flexibility to change it as needed.
Just create a DTS from enterprise manager, select write package to disk and
later you can load and execute it.
Let me know if you need code, I do this in a couple of deployments.
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
这篇关于插入1,000,000条记录的最有效方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
