sqlserver 2005 大数据插入优化解决方案

2013-12-16

sqlserver2005 大数据插入优化现在有个需求，一次性insert 2000条以上的数据。我用了存储过程来处理。代码如

sqlserver 2005 大数据插入优化
现在有个需求，一次性insert 2000条以上的数据。我用了存储过程来处理。
代码如下

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SendChitOfHead] 
@CsendmemberId int,
@CfrommemberIds varchar(8000),---
@CpersonIds varchar(8000),---
@CpersonType varchar(500),
@Ctitle varchar(500),
@CContent text,
@Ctype varchar(500),
@CoperateIP varchar(500),
@Cstate varchar(500),
@CSid int,
@CCids varchar(8000) ,---
@CphoneNums varchar(8000)---
as
begin
declare @CfrommemberId varchar(8000)
declare @CpersonId varchar(8000)
declare @CCid varchar(8000)
declare @CphoneNum varchar(8000)
set@CfrommemberIds=@CfrommemberIds+','
set@CpersonIds=@CpersonIds+','
set@CCids=@CCids+','
set@CphoneNums=@CphoneNums+','
    while(@CfrommemberIds<>'')
        begin
set @CfrommemberId=convert(VARCHAR,left(@CfrommemberIds,Charindex(',',@CfrommemberIds,1)-1))
set @CpersonId=convert(VARCHAR,left(@CpersonIds,Charindex(',',@CpersonIds,1)-1))
set @CCid=convert(VARCHAR,left(@CCids,Charindex(',',@CCids,1)-1))
set @CphoneNum=convert(VARCHAR,left(@CphoneNums,Charindex(',',@CphoneNums,1)-1))
            -----print @id  
insert into Sys_chit(CsendmemberId,CfrommemberId,CpersonId,CpersonType,Ctitle,CContent,Ctype,CoperateIP,Csendtime,Cstate,CSid,CCid,CsendState,CphoneNum,COperator) values(@CsendmemberId,@CfrommemberId,@CpersonId,@CpersonType,@Ctitle,@CContent,@Ctype,@CoperateIP,getdate(),@Cstate,@CSid,@CCid,' ',@CphoneNum,' ')
set @CfrommemberIds=stuff(@CfrommemberIds,1,charindex(',',@CfrommemberIds,1),'')
set @CpersonIds=stuff(@CpersonIds,1,charindex(',',@CpersonIds,1),'')
set @CCids=stuff(@CCids,1,charindex(',',@CCids,1),'')
set @CphoneNums=stuff(@CphoneNums,1,charindex(',',@CphoneNums,1),'')
        end
end

本地测试2300条数据，时间大概是1分钟左右。
代码中是将字符串中包含逗号的分割，然后循环插入。
本人初学者，哪位高手能再优化下代码，提高效率。
或者其他更优写法。
在线等。
[解决办法]

DROP TABLE test
CREATE TABLE test (NAME VARCHAR(max))
  INSERT INTO test VALUES('15874,154874,15474,15474,1547,154748,1584847')
  go

select

    SUBSTRING(a.NAME,number,CHARINDEX(',',a.NAME+',',number)-number) as spnum,'内容' ct
from
    test a,master..spt_values
where
    number >=1 and number<=len(a.NAME)
    and type='p'
    and substring(','+a.NAME,number,1)=','

/*
spnum                                                                                                                                                                                                                                                            ct
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----
15874                                                                                                                                                                                                                                                            内容

154874                                                                                                                                                                                                                                                           内容
15474                                                                                                                                                                                                                                                            内容
15474                                                                                                                                                                                                                                                            内容
1547                                                                                                                                                                                                                                                             内容
154748                                                                                                                                                                                                                                                           内容

1584847 内容

*/

[解决办法]


if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
   drop function dbo.f_splitSTR
go
 
create function dbo.f_splitSTR
(
    @s varchar(8000),     --要分拆的字符串
    @split varchar(10)    --分隔字符
) 
returns @re table(                      --要返回的临时表
                     col varchar(1000)  --临时表中的列 
                 )
as
begin   
  declare @len int
   
  set @len = LEN(@split)      --分隔符不一定就是一个字符，可能是2个字符
   
  while CHARINDEX(@split,@s) >0
  begin
    insert into @re 
    values(left(@s,charindex(@split,@s) - 1))
     
    set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'')    --覆盖：字符串以及分隔符
  end
   
  insert into @re values(@s)
   
  return   --返回临时表
end
go  
 
 
--效果
insert into 你的表(列)
select c.col
from dbo.f_splitSTR('15874,154874,15474,15474,1547,154748,1584847',',') c
/*
col
a
b
c
d
*/

[解决办法]
成本在于写操作和繁琐的字串拆分操作，特别是字串拆分, 这个是tsql的弱项。
你觉得与系统表连表后这样的集合式插入会快么？

个人建议老老实在在前端代码里拼出insert语句，或者插入到datatable类似于这样的容器里，然后bulkcopy\bulkinsert或者其它的方案bcp等等

[解决办法]
楼主的代码不是真正的一次性插入，而是2000次插入，需要频繁写磁盘，要做到一次性插入，分两步，先声明一个表变量(这个是在内存中操作的)，把数据写入表变量，然后再isnert into objtable (column1,column2...) select column1,column2... from 表变量

你测试一下

热点排行

SQL Server

sqlserver 2005 大数据插入优化解决方案