c# 一次性插入SQL SERVER 的表700万行记录！！慢得要死，不知道有什么好的解决方法？
c# 一次性插入SQL SERVER 的表700万行记录！！慢得要死，不知道有什么好的解决方法？--------------------编程问答-------------------- 你用的插入语句还是ADO --------------------编程问答-------------------- adn.net 的批量更新 update(表） --------------------编程问答-------------------- ADO.net 的批量更新 update(表）
--------------------编程问答-------------------- 有没有高手出来？ --------------------编程问答-------------------- 楼主标题不妥，不要用那么刺眼的“字”。 --------------------编程问答-------------------- 2个刺眼的"字" 放在一起 --------------------编程问答-------------------- 实出无奈，这个问题困了好几天，不知那位能给我少许思路？ --------------------编程问答-------------------- 不知你是什么程序，一次性干嘛要插入这么多条记录呀，难道是恢复数据，那用Sql Server的还原备份或者导入导出，要不用语句bcp吧。 --------------------编程问答-------------------- 什么业务需求，一次性操作这么多数据。需要好好考虑一下分解操作了。
而且对于大数据量的操作，任何一个看似不消耗资源的问题都可能被放大，需要调试一下数据才知道瓶颈，找到瓶颈进行优化就ok了。
不过优化的问题比较难说，需要一点一点地经验积累。 --------------------编程问答-------------------- 简单的就是提高硬件，
我想任何普通的操作都不可能做这么大的数据量，那么可以考虑sql server 数据转换工具 --------------------编程问答-------------------- 开多线程呢,后台分次插入数据,前台就不会死在那儿了. --------------------编程问答-------------------- 提高硬件，使用多线程. --------------------编程问答-------------------- 任务的具体场景，发生频率，数据源和目的表的情况，还有事务，容错等需求
这样子别人才好作答 --------------------编程问答-------------------- 700w条数据一起插入肯定慢，好比你一次查处700w数据一样
你把它分段执行 --------------------编程问答-------------------- 分布式事务处理 --------------------编程问答-------------------- 可以利用SQLServer fastloader接口，100万条记录时间大概1，2分钟左右。 --------------------编程问答-------------------- 直接用数据库导入了 --------------------编程问答-------------------- 需要用户界进行操作，用SQL导入达不到要求。
jacket84(jac)
可以利用SQLServer fastloader接口，100万条记录时间大概1，2分钟左右。
所说如果可以做到愿听其详！ --------------------编程问答-------------------- BCP --------------------编程问答-------------------- 具体需求：平常我们用的车票每张车票都有一个唯一的号码，这个号码不是随机而是有一定规则的，先按照规则生成每一个号码然后插入到数据库的表。（比如：NO.000256812) --------------------编程问答-------------------- 有点笼统。
一般一条一条的插，一般的服务器，700万条可能要20-30分钟。 --------------------编程问答-------------------- 具体需求：平常我们用的车票每张车票都有一个唯一的号码，这个号码不是随机而是有一定规则的，先按照规则生成每一个号码然后插入到数据库的表。（比如:规则为在号码前加"NO.",流水号的长度为9位数，当录入 1-50000 时自动分解为 NO.000000001,NO.000000002,NO.000000003,...，NO.000050000,然后再把生成的每个号码插入数据库的表）
--------------------编程问答-------------------- 呃七嘴八舌，可否易做图？ --------------------编程问答-------------------- mark. --------------------编程问答-------------------- 700万条数据，还在程序中实现导入，确实牛，如果是我，可能会自己 手机方式导入。不过，你700万条数据来源于那里啊。
--------------------编程问答-------------------- 学习一下.
--------------------编程问答-------------------- 不理解为啥要一次插入700w
按正常的理解，生成一张票，产生一个no.xxxxxxxxx即可
如果实在要这样
也可以在另外的数据库生成这个700w的数据
然后售出车票后再写入正式的数据库 --------------------编程问答-------------------- 如果你保证这700w条数据库主键不重复的话，你在插入之前先用程序把表的主键，索引等去掉，然后插入，等插入完毕后，再用程序加上主键，索引 --------------------编程问答-------------------- 思路就是：生成批量处理语句，一次尽可能处理多的数据。采用多线程调用
把sql 语句拼成 insert(a,b) (select 1,2 from ‘系统表（或者随便已经有的表）’ unit (select 2,3 from ‘系统表（或者随便已经有的表）’ ....... )
存储过程接受字符串执行
调用线程池处理该方法
ThreadPool.QueueUserWorkItem
--------------------编程问答-------------------- 学习ing．． --------------------编程问答-------------------- nicholascheung(我要的幸福)
批量一次是可以插入多条记录，但是
在数据库没有任何与插入相关的内容，内容正是待插入去生成。 --------------------编程问答-------------------- mark --------------------编程问答-------------------- 用SqlBulkCopy类试试
--------------------编程问答-------------------- 用触发器 --------------------编程问答-------------------- SqlBulkCopy 类不行吧，这个用于从一表转移到另一个表。现在的情况是数据库中并没有任何与插入相关的内容。 --------------------编程问答-------------------- SqlBulkCopy 类是从DataTable到SQL Server数据库，非常快。 --------------------编程问答-------------------- 没说清楚细节. --------------------编程问答-------------------- rl0516() 听起来不错，转移700万条记录估计需需多少时间。 --------------------编程问答-------------------- 具体需求：平常我们用的车票每张车票都有一个唯一的号码，这个号码不是随机而是有一定规则的，先按照规则生成每一个号码然后插入到数据库的表。（比如:规则为在号码前加"NO.",流水号的长度为9位数，当录入 1-50000 时自动分解为 NO.000000001,NO.000000002,NO.000000003,...，NO.000050000,并把生成的记录放在一个DataTable表里，然后再把生成的每个号码插入数据库的表）
--------------------编程问答-------------------- 用语句是最快的 --------------------编程问答-------------------- 批量一次是可以插入多条记录，但是
在数据库没有任何与插入相关的内容，内容正是待插入去生成。
---------------------------------------------------------------------
to ht1868()
看来你还是没有明白我的意思
把sql 语句拼成 insert(a,b) (select 1,2 from ‘系统表（或者随便已经有的表）’ unit (select 2,3 from ‘系统表（或者随便已经有的表）’ ....... )
select 1,2 select 2,3
这些数据本来就是你要插入的数据 1，2，3又不是列明，就是数值。这个地方只不过欺骗一下sql
这样说你能明白么？
下面是很久以前 写过的片段
//拼写批量处理 sql
//批次的标识
needcardCount=dv.Table.Rows.Count;
string a = "insert into DIGI_Products(OPERATOR_NAME ,Card_Sn,Card_Pwd,PRODUCT_SUMMARY,STATUS,PRODUCT_CODE,NATCH_NO) (";
string b = "";
int sleepTime=90000;
string endStr=")";
DataRow[] drList=dv.Table.Select("卡号<>'' or 密码<>'' or 卡号<>'卡号' or 密码<>'密码' ");
if(drList.Length==0)
{
JScript.Alert("系统提示:没有数据！");
return;
}
NeedUpCardCount=drList.Length;
for(int i=0;i<drList.Length;i++)
{
//是第一行
if( b == "")
{
b=b+" select 'root','"+drList[i]["卡号"]+"','"+drList[i]["密码"]+"','"+flagStr+"','1','"+pID.ToString()+"','"+drList[i]["批次号"]+"' from dual ";
}
else
{
b=b+" union select 'root','"+drList[i]["卡号"]+"','"+drList[i]["密码"]+"','"+flagStr+"','1','"+pID.ToString()+"','"+drList[i]["批次号"]+"' from dual ";
}
//判断已经超出长度限制
if(a.Length+b.Length+endStr.Length>=LIMIT_SQL_LANGTH)
{
//超出长度限制执行
//执行
if(i==0)
{
break;
//b=" select 'root','"+drList[i]["卡号"]+"','"+drList[i]["密码"]+"','批量导入数字卡','1','"+labPRODUCT_CODE.Text+"','"+drList[i]["批次号"]+"'";
}
else
{
a= a+b+endStr;
ExeString(a);
a= "insert into DIGI_Products(OPERATOR_NAME ,Card_Sn,Card_Pwd,PRODUCT_SUMMARY,STATUS,PRODUCT_CODE,NATCH_NO) (";
b="";
//b=" select 'root','"+drList[i]["卡号"]+"','"+drList[i]["密码"]+"','批量导入数字卡','1','"+labPRODUCT_CODE.Text+"','"+drList[i]["批次号"]+"' from dual ";
}
}
}
if(b.Length>0)
{
//执行
a= a+b+endStr;
ExeString(a);
}
--------------------------
//异步执行数据库操作
private static void ExeString(string str)
{
System.Threading.ThreadPool.QueueUserWorkItem(new WaitCallback(ExeString_CallBack),str);
}
--------------------编程问答-------------------- 就着你这个问题，请教一下楼主或者众高手。
一次插入700万条数据。楼主C#觉得很慢。那么不知道，java快吗？
到底是因为C#本身慢？还是别的原因？否则这个标题会给人很大误解的。 --------------------编程问答-------------------- nhibernate似乎解决批量数据问题 --------------------编程问答-------------------- 很高深不明白 --------------------编程问答-------------------- 我试过在SQL查询分析器里需要20-25分钟左右。在C#写的程序执行拖入操作一个小时过去了都没有反应,用的是
update(表）提交，不知有没有没更好的方法？ --------------------编程问答-------------------- 用存储过程，你传入范围，然后用存储过程计算，再插入 不知道能不能快一点 --------------------编程问答-------------------- sql server 数据转换 --------------------编程问答-------------------- 用存储过程会快点 --------------------编程问答-------------------- 使用存储过程来执行插入，在存储过程用一个循环来根据规则计算号码。 --------------------编程问答-------------------- SqlBulkCopy 批量从DataTable 表复制，会了出现内容不足！ --------------------编程问答-------------------- 怀疑过sqlconnection这个连接的性能吗？能保证700w条数据在这么的连接里面还是激活状态？
我感觉应该换个思路来解决。分批，多线程。什么的 --------------------编程问答-------------------- 这种逻辑应该封装到存储过程里去，用任何数据连接和算法都不会比存储过程快。
不过700w的记录操作起来可能在性能方面会有问题呢，建议从功能上看看能不能绕开这个问题，例如
分段存储：
每100W存一张表，不再使用范围内的票等用到之前在生成？
拆成主从表：
每100张作为一组，生成组号存在主表中，7W记录。每组票在子表中都有100个子记录对应原来的700W记录。子表不是一次生成。在组号被调用，而且子表中没有记录的时候，在生成对应的100条子记录
--------------------编程问答-------------------- 还是直接存储过程，或者是导入算了， --------------------编程问答-------------------- 关注 --------------------编程问答-------------------- 这个很慢的原因其实就是读入的时候和更新(update(表）)的时候.
我也有个类似楼主的,就是30辆汽车的每个月的GPS数据.从GPS服务商的系统将入自己的系统.
我的方法是:利用update
四个线操作:一个是分批读入.每次只读入5万条,利用Top限制,并作好记录.
如 第一次 Top 50000
第二次 以后按分页原理
第二个线程,将数据交换,和进度条处理.以待第三个线程更新完成后再读入(第一个线程)
第三个线程,将数据更新,数据交换成完以后,再更新.
第四个主线程,处理其他事务.
我觉得这种方法不是很慢,并且有进度指示.让人知道更新进度.
如果一次读取入和一次更新的话,有很多机子就死的,并且不知道是否更新成功.
我的这种也有不好的地方,就是不能使用事务.只能在记录每一次更新成功以后,保存行数,如果后面的失败!重新更新时就不再更新以前的了.
--------------------编程问答-------------------- SqlBulkCopy 进行分批（如：100万为一批）我看可行，再利用多线程没有办法知道每一线程进度没有办法看见，一但其中一个线线程出错，可有办法？ --------------------编程问答-------------------- 帮你顶
up --------------------编程问答-------------------- lz的用词在下佩服
插
插入
性 插入
一次 性 插入
难怪帖子这么红了 --------------------编程问答-------------------- 用DataSet 试试看 --------------------编程问答-------------------- 感觉这个方案本身就有问题，用insert怎么也快不了，存储过程能提高一些，但对于数据量700w的话，还是应该考虑用数据库本身的工具
比如sqlserver的bulk insert或者oracle的sqlldr，这绝对是效率最高的
如果需要ui的话，可以考虑提供一个文件上传的界面，指定文件和必要的参数，然后在后台启动命令 --------------------编程问答-------------------- 觉得没有必要
一定要这么做的话，用C#相对来说是不会慢的
insert cmd参数化,指定size
conn.open();
cmd.Prepare();
while(<700w)
cmd.Parameters[0]=...;cmd.execut...
conn.close();
最好用BulkCopy，开两个线程，一个用于产生序列号，存到datatable，一个用于BulkCopy
分批不要超过1w， --------------------编程问答-------------------- 建议用SqlBulkCopy，处理起来相对适配器是要快得多的。
本人测试过： 10万数据有几十倍的差距。 --------------------编程问答-------------------- 我上次的GPS数据有十万条,然后在程序中还经过了一些判断后在每相邻两行间再插入九行新数据,最后数据量在一百万左右,从查询出十万条记录到判断再到插入写回数据库表中整个过程,3至5秒钟内解决.因为我使用BCP.
LZ可试试. --------------------编程问答-------------------- up --------------------编程问答-------------------- 是的，用bcp命令，上千万行也就几分钟的事情，bcp命令具体格式请登陆我的qq空间，上面有详细说明。qq:241732345 --------------------编程问答-------------------- SqlBulkCopy非常快，感谢各位仁兄给我出主意。10万行1分种左右完全可以插入数据库的表。
不足之处是DataTable 好像只能增加到10W行，多了无法增加，不知是不是有这个说法？ --------------------编程问答-------------------- 学习 --------------------编程问答-------------------- 楼主可以动态建一张临时表，没有索引跟主键，然后用SQL Server的IRowsetfastload接口导入数据库，其实就是bulk copy，DTL用的就是这个接口。你看看SQL server的帮助，贴一个里面的demo。
--------------------编程问答-------------------- 如何使用 IRowsetFastLoad 大容量复制数据 (OLE DB)
使用者通过将 SQLOLEDB 提供程序专用属性 SSPROP_ENABLEFASTLOAD 设置为 VARIANT_TRUE，通知 SQLOLEDB 需要进行大容量复制。使用者通过在数据源上设置该属性创建一个 SQLOLEDB 会话。这个新会话使使用者得以访问 IRowsetFastLoad。
将数据大容量复制到 SQL Server 表中
建立与数据源的连接。
将 SQLOLEDB 提供程序专用数据源属性 SSPROP_ENABLEFASTLOAD 设置为 VARIANT_TRUE。将该属性设置为 VARIANT_TRUE 即创建一个新会话，这个新会话使使用者得以访问 IRowsetFastLoad。
创建一个会话以请求 IOpenRowset 接口。
调用 IOpenRowset::OpenRowset 打开包含表中（通过大容量复制操作将数据复制到该表中）所有行的行集。
做必要的绑定并用 IAccessor::CreateAccessor 创建存取程序。
设置内存缓冲区，数据将从该缓冲区复制到表中。
调用 IRowsetFastLoad::InsertRow 将数据大容量复制到表中。
--------------------编程问答-------------------- 具体的代码可以到SQL 2000的帮助中查找IRowsetfastRow，我们数据导入时也碰到这个瓶颈，后来用就是用这个接口进行数据导入的，其原理我想应该是直接操作了SQL的数据表文件。Oracle里面也有类似的接口SQLLoader，只要使用这些接口就可以了。
不过还有一个问题，700万条记录放在DataSet中，光是从第一条遍历到最后一条都要耗时蛮久，说不定遍历的耗时比数据插入的耗时还要长。
--------------------编程问答-------------------- 是插入测试数据吗?
如果是的话 那就别用C# 用 sql 差
while 循环可以办到 --------------------编程问答-------------------- 写个存储过程。用调度去执行。定到晚上数据库闲的时候进行。 --------------------编程问答-------------------- 真强!学习中~~ --------------------编程问答-------------------- LZ是要用程序生成700W条记录
然后一次性写入数据库
真的需要这么做吗？如果是火车票的话，LZ程序肯定有问题的。这样会浪费很多号码的。 --------------------编程问答-------------------- 从未一次性导入过这么多的数据，用存储过程试了吗 --------------------编程问答-------------------- 帮你顶 --------------------编程问答-------------------- 没太好的办法,一个字:"等" --------------------编程问答-------------------- 关掉事务，或分批插 --------------------编程问答-------------------- 一次性插入...你用的词也 太刺眼了 --------------------编程问答-------------------- 龌龊的人啊。。。。
--------------------编程问答-------------------- 那么老的贴子，尽然还有人把它翻出来 --------------------编程问答-------------------- 据说事物能够加快效率 --------------------编程问答-------------------- SSIS --------------------编程问答-------------------- 说得很有道理，如果真有这么大数量的用户记录的话，最好是不要单纯考虑使用程序操作数据库的方法。用程序生成记录文件以后，使用数据库本身的工具来导入记录肯定是最有效率的，电信的大容量用户数据就是这样进行维护的。而且在有海量用户数据的情况下，还有可能采取分库的方法，将用户数据分散到几个数据库中存储。
--------------------编程问答-------------------- 程序插700W,汗!
直接人工插吧 --------------------编程问答-------------------- 顶，，我会用存储过程。。 --------------------编程问答-------------------- mark --------------------编程问答-------------------- 700W行就要用存储过程了~!逻辑的问题吧,一次插入700W行???? --------------------编程问答-------------------- 用程序插,怎么都不会快.
700万太大了,可以每天分批执行.
关于大容量导入的可以用BCP --------------------编程问答-------------------- 74楼的真强 --------------------编程问答-------------------- 首先你的数据源来自表吗？打开表用
虚拟模式，表每次的行数 自己定义（10000行，差不多了吧，打开 约 1－2秒。插数据：可以分页呀，分个几次不就行了）
SqlConnection con = new SqlConnection(sqlcon);
con.Open();
SqlBulkCopy bcp = new SqlBulkCopy(con);
bcp.DestinationTableName = "职工2007年度照片";
bcp.WriteToServer(dt); //DataTable dt;
bcp.Close();
con.Close(); --------------------编程问答-------------------- 1、就sql server 可以考虑利用它本身的特性 bcp 或 SqlBulkCopy 都是为解决海量数据诞生的；
2、建立存储过程或新建临时表；
3、分拆业务，分批处理；
4、多线程操作；
目前就想到这些；
前三个是自己想到了，第4个是看楼上的答复才想到的。
性能优化的我写过几篇文章，可以看看：
http://blog.csdn.net/alonesword/ --------------------编程问答-------------------- 在存储过程中利用分页的原理,讲700W分解成几万条一次试试,应该没问题的 700W有不多,而你就是更新一个字段的数据而已.如果是7000W就得晕一下了 哈哈 --------------------编程问答-------------------- 这个问题我也想过，导入效率比较高的应该是sqlbulkcopy，最快的是bcp命令，但后者是手工操作。我在想如果能够调用这个命令传个参数导入速度应该快的要命，本人尚未得知，再此顶一下。希望那位高手能够指点迷津。 --------------------编程问答-------------------- BCP命令
BCP AdventureWorks.sales.currency out c:\currency1.txt -c -T --使用信任连接
--------------------编程问答-------------------- ETL --------------------编程问答-------------------- 莫非是 性插 --------------------编程问答-------------------- 使用搜索搜一下。。我记得有看过一前辈高人使用存储过程生成类似数据100万条只要一分钟左右，印像中！！！搜一下吧。 --------------------编程问答--------------------
生成100万条8位不重复数据的示例
USE tempdb
GO
-- 创建测试表
CREATE TABLE tb(id char(8))
-- 创建用于自动过滤重复值的唯一索引
CREATE UNIQUE INDEX IX_tb ON tb(id)
WITH IGNORE_DUP_KEY
GO
-- 测试数据插入的处理时间, 记录开始处理的时间点
DECLARE @dt datetime
SET @dt = GETDATE()
-- 插入随机数据
SET NOCOUNT ON
DECLARE @row int
SET @row = 1000000 -- 设置总记录数
WHILE @row >0
BEGIN
-- 显示提示信息, 表示还需要插入多行数据
RAISERROR('need %d rows', 10, 1, @row) WITH NOWAIT
-- 插入随机的位编码数据
SET ROWCOUNT @row
INSERT tb SELECT
id = RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8)
FROM syscolumns c1, syscolumns c2
SET @row = @row - @@ROWCOUNT
END
-- 显示插入数据使用的时间
SELECT BeginDate = @dt, EndDate = GETDATE(),
Second = DATEDIFF(Second, @dt, GETDATE()),
GO
-- 显示最终的结果记录是否正确
SELECT COUNT(*) FROM tb
GO
-- 删除测试
DROP TABLE tb
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zjcxc/archive/2006/08/20/1099215.aspx
补充：.NET技术 , C#
