求！查询15亿条数据根据不同的条件插入不同的表。最快速。。求大蚊。

一个视图查出来15亿数据。（视图包含有10个视图，一个视图有100个表。一个表有150万行数据，）
跟据字段中的QQ号。分别插入150个表中。

我写了一个存储过程。一个小时才30万数据。


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE                 PROCEDURE  get_mycursor
AS

 --定义游标mycursor
DECLARE mycursor CURSOR FOR        

--使用游标的对象(QQ表)
SELECT QQNum,Nick,Age,Gender,Auth,QunNum FROM group1_100               

 --打开游标
OPEN mycursor                      

DECLARE 
@QQNum VARCHAR(50),
@Nick VARCHAR(50),
@Age VARCHAR(50),
@Gender VARCHAR(50),
@Auth VARCHAR(50),
@QunNum VARCHAR(50),
@sql VARCHAR(500)

  --将游标向下移1行，获取的数据放入之前定义的变量@QQNum,@Nick,@Age,@Gender,@Auth,@QunNum 中
FETCH NEXT FROM mycursor INTO @QQNum,@Nick,@Age,@Gender,@Auth,@QunNum 

 --判断是否成功获取数据
WHILE @@fetch_status=0           
 BEGIN
                         
  SET @sql=''

--判断QQ是不是七位数的还是八位的
IF(LEN(@QQNum)>7)     
BEGIN
--如果是八位数以上的。跟据条件动态拼成插入某表的sql语句
SET @sql=' INSERT newlist'+SUBSTRING(@QQNum,1,LEN(@QQNum)-7)+' (QQNum,Nick,Age,Gender,Auth,QunNum) 
VALUES( '+''''+@QQNum+''''+','+''''+@Nick+''''+','+''''+@Age+''''+','+''''+@Gender+''''+','+''''+@Auth+''''+','+''''+@QunNum+''''+')'
END
ELSE
BEGIN
--低于七位数，直接拼成插入newlist0这个表
SET @sql=' INSERT newlist0 (QQNum,Nick,Age,Gender,Auth,QunNum) 
VALUES( '+''''+@QQNum+''''+','+''''+@Nick+''''+','+''''+@Age+''''+','+''''+@Gender+''''+','+''''+@Auth+''''+','+''''+@QunNum+''''+')'
END
--运行运态语句
EXEC(@sql);

  FETCH NEXT FROM mycursor INTO @QQNum,@Nick,@Age,@Gender,@Auth,@QunNum  --将游标向下移1行
 END
 
--关闭游标
 CLOSE mycursor   

--删除游标                
 DEALLOCATE mycursor


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

24 个解决方案

#1

人呢。。人呢。。

#2

另外一个贴已经回复了

#3

引用 2 楼  的回复:

另外一个贴已经回复了

我要解决方案呢。

#4

首先，你最好使用高版本的数据库，特别是2008以后。然后使用分区表（05以后才出现），分区表可以实现并行插入，像你这种数据量，如果业务允许，可能分999个区都可以（999个分区是2008最大分区数）。这个很重要，代表你同时做999（这个数量只是举例而已）个插入动作。
然后，在插入时禁用表中所有索引和不必要的触发器，等插入完再启用。同时插入时数据库的恢复模式改成大容量日志（可以在插入时才改）。
第三，千万不要用游标，你的游标不把数据库弄死已经非常了得了。
第四，看看可以不可以使用到BULK INSERT这类动作，这类操作是目前为止插入速度最快的操作。基本上没有更快的了（除非科研级别的数据库）

#5

视图套视图，本来设计就不要性能

#6

尽量不要用游标，游标最多用于小数据量的表操作

#7

采用数据库分区表操作

#8

按QQ号的前7位，group后再循环插入对应的表
这样，每组插一次
次数就比现在的每条插一次，少多了

#9

哦，是按QQ号的减掉后7位之后的结果

不如把QQ号全部前面补'0'凑成统一的9位，再按前2位group

#10

引用 9 楼  的回复:

哦，是按QQ号的减掉后7位之后的结果

不如把QQ号全部前面补'0'凑成统一的10位，再按前3位group

再优化：

都不用插入150个表了
在前补0之后，直接按前3位分区就行了！
又方便，又高效！

#11

看你的视图这些表的结构像是一样的．像是UNION成的视图？
如果这样．你还不如一个表一个表的处理．

#12

这样用游标一行一行的去查到、再处理、再插，肯定是很慢的，而且里面还有字符串函数，如果表上有触发器还要过触发器，所以建议用临时表，批量插入。

#13

尽量别用游标 效率真的不高

#14

用临时表代替游标，然后想办法用bluck insert一次插入1000条好了！

#15

视图如此嵌套就不要谈性能了

#16

用表分区是好办法

#17

视图不会提高任何效率，避免使用游标。
1、15亿数据，你必须要做表分区，通过分区来缩小查询范围，
2、建立高效索引，尽可能的利用上索引，提高查询速度，以空间换取时间。
3、优化查询的T-sql。
4、优化程序。

#18

创建分布式分区视图
http://msdn.microsoft.com/zh-cn/library/ms188299.aspx

#19

思考中...

#20

这样的情况，表分区处理、应已数据集的方式插入数据，而不是数据行的方法插入操作；也可以通过把大数据量数据先写入内存，然后再拷贝内存数据集到数据库表中。

#21

该回复于2013-02-21 08:51:47被管理员删除

#22

这么大的数据量，首先不能用游标，那个太慢了，尽量用sql来实现，其次通过建立分区表，不要用视图来实现，最后，你在实际运行插入的sql之前，先考虑把要插入的表所在的文件扩大，15亿条数据，我觉得你把文件大小扩大到100GB左右吧，不然到时候插入会等待IO完成

#23

引用 4 楼 DBA_Huangzj 的回复:

首先，你最好使用高版本的数据库，特别是2008以后。然后使用分区表（05以后才出现），分区表可以实现并行插入，像你这种数据量，如果业务允许，可能分999个区都可以（999个分区是2008最大分区数）。这个很重要，代表你同时做999（这个数量只是举例而已）个插入动作。
然后，在插入时禁用表中所有索引和不必要的触发器，等插入完再启用。同时插入时数据库的恢复模式改成大容量日志（可以在插入时才改）。
第三，千万不要用游标，你的游标不把数据库弄死已经非常了得了。
第四，看看可以不可以使用到BULK INSERT这类动作，这类操作是目前为止插入速度最快的操作。基本上没有更快的了（除非科研级别的数据库）

mark

#24

引用 23 楼 daiyueqiang 的回复:

Quote: 引用 4 楼 DBA_Huangzj 的回复:

首先，你最好使用高版本的数据库，特别是2008以后。然后使用分区表（05以后才出现），分区表可以实现并行插入，像你这种数据量，如果业务允许，可能分999个区都可以（999个分区是2008最大分区数）。这个很重要，代表你同时做999（这个数量只是举例而已）个插入动作。
然后，在插入时禁用表中所有索引和不必要的触发器，等插入完再启用。同时插入时数据库的恢复模式改成大容量日志（可以在插入时才改）。
第三，千万不要用游标，你的游标不把数据库弄死已经非常了得了。
第四，看看可以不可以使用到BULK INSERT这类动作，这类操作是目前为止插入速度最快的操作。基本上没有更快的了（除非科研级别的数据库）

mark

这个回答很好？但是还需要告诉别人为什么。就像面试的时候技术部经理那样。每一个问题回答之后都要问为什么？
1. 有几个问题要问，把索引禁用。还怎么分区，还有如果是聚集索引这张表直接不能使用了（聚集索引就是数据本身吗）。我的数据插在哪里？
2. 改成大容量恢复模式很对（只写undo不写redo，写数据唯一和磁盘交互的就是日志写入，日志写入的快，事务提交的才会快。但是日志这里肯定还可以优化。比如我如果用bcp或bulk的时候带batch参数。我做成循环每次单批执行多少条。这样也能最小化日志写入。而且不会造成一个很大很长的事务。还可以优化，就是日志放在RAID10 上，还可以优化就是日志文件和数据文件不要放在同一块RAID上。because，日志是连续写入，而数据则是随机写入（如果他不是一个栈结构，也就是他没有聚集索引的话））
3. 千万不要用游标，因为sql2000 游标是逆序扫描日志来实现的（我在写log的时候磁盘在吱吱的往前写的时候突然往后读，以前的版本。这样会影响事务的提交。）sql2005以上根据事务id把以前的行的老版本吸入到存储区域。写入就会影响大量IO,也会影响你现在要写的数据的时间。