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

时间:2022-07-25 23:31:46
一个视图查出来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


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

思考中...

#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,也会影响你现在要写的数据的时间。

#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


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

思考中...

#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,也会影响你现在要写的数据的时间。