sqlserver 2005 大数据插入优化

Question

可以考虑用函数来拆分：if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
   drop function dbo.f_splitSTR
gocreate 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  
--效果
select c.col
from dbo.f_splitSTR('a,b,c,d',',') c
/*
col
a
b
c
d
*/

Answer 1

也可以这样拆if OBJECT_ID('test') is not null
drop table test 
go 
create table test 
( 
    id int, 
    name varchar(10), 
    [key] varchar(20) 
) 
go 
insert test 
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23'
go 
  
select
    id, 
    a.name, 
    SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key] 
from
    test a,master..spt_values 
where
    number >=1 and number<=len([key])  
    and type='p' 
    and substring(','+[key],number,1)=','
/* 
id    name    key
----------------------------- 
1    lisa    li 
1    lisa    is
1    lisa    sa 
2    sophia    ab 
2    sophia    cd 
2    sophia    ef 
3    lori    12 
3    lori    34 
3    lori    23 
*/ 

Answer 2

我就简单写个例子，看你给我分析一下。
比如说“15874，154874，15474，15474，1547，154748，1584847……”里面有2000个“xxx,”。然后insert到数据里面的A表中的字段spnum，A表的另外一个字段ct插入“内容”。
写SQL的话，就是insert into A（spnum,ct）value(xxx,'内容')。这样写就每一次都连接一次数据库。速度很慢。
怎么优化这个写法

Answer 3

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                                                                                                                                                                                                                                                          内容 */

Answer 4

这样直接插入：  insert into A（spnum,ct）
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)=','

Answer 5

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
*/

Answer 6

成本在于写操作和繁琐的字串拆分操作，特别是字串拆分, 这个是tsql的弱项。
你觉得与系统表连表后这样的集合式插入会快么？个人建议老老实在在前端代码里拼出insert语句， 或者插入到datatable类似于这样的容器里，然后bulkcopy\bulkinsert或者其它的方案bcp等等

Answer 7

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