数据调优,请经验丰富者帮我看看！

[justxd]

A表数据
col1 col2 col3 col4 col5
abcd lllls 123,456,789,123 myw 备注
我要将A表中的数据按col3中的数据进行拆分成多条
A表进行拆分后应该是
col1 col2 col3 col4 col5
abcd lllls 123 myw 备注
abcd lllls 456 myw 备注
abcd lllls 789 myw 备注
abcd lllls 123 myw 备注

我现在的拆分方法是:
1.将A表数据进行顺序编号插入一个临时表中
2.按编号循环访问临时表，进行拆分操作

下面是我的存储过程,欢迎大家提出保贵意见:
CREATE PROCEDURE getData
AS
BEGIN
/*用于记录A表进行编号的总行数*/
DECLARE @rowCount INT
/*用于记循环访问的位置*/
DECLARE @n INT
/*从第一条记录开始*/
SET @n=1

/*最终数据记录表*/
CREATE TABLE #tempData
(
numberId INT IDENTITY(1,1) NOT NULL PRIMARY key,
col1 varchar(20),
col2 varchar(20),
col3 varchar(20),
col4 varchar(20),
col5 varchar(20),
)
/*已编号，待拆分数据记录表*/
CREATE TABLE #tempLog
(
numberId INT IDENTITY(1,1) NOT NULL PRIMARY key,
col1 varchar(20),
col2 varchar(20),
col3 varchar(20),
col4 varchar(20),
col5 varchar(20),
)

INSERT INTO #tempLog SELECT A表
/*记录编号后的总行数*/
SET @rowCount=@@ROWCOUNT

/*存储待拆列数据*/
DECLARE @col3 VARCHAR(50)
/**************如果编号后的总行数大于0则有记录可拆（直观理解的意思是A表中有数据）***********/
IF @rowCount>0
BEGIN
WHILE @n<=@rowCount
BEGIN
/*****获取待拆字段的值*********/
SELECT @col3=col3 FROM #tempLog WHERE numberId=@n

/*將一倏數據拆成多倏*/
DECLARE @col1 varchar(20)
DECLARE @col2 varchar(20)
DECLARE @col4 varchar(20)
DECLARE @col5 varchar(20)

SELECT @col1=col1 FROM #tempLog WHERE numberId=@n
SELECT @col2=col2 FROM #tempLog WHERE numberId=@n
SELECT @col4=col4 FROM #tempLog WHERE numberId=@n
SELECT @col5=col5 FROM #tempLog WHERE numberId=@n
/*进行拆分*/
WHILE(charindex(',',@col3)<>0)
begin

INSERT into #tempData values (@col1,@col2,substring(@col3,1,charindex(',',@col3)-1),@col4,@col5)
set @col3 = stuff(@col3,1,charindex(',',@col3),'')
end
INSERT into #tempData values (@col1,@col2,@col3,@col4,@col5)
END

END
SELECT * FROM #tempData
END


终于写完了，如果数据有上百万数，拆分的话速度极慢，哪位哥们能帮我看看，有没有什么好点的办法。

[dgdyq]

怎么拆都要时间的了，可以考虑换个思路，把A表按时间或其他分成N个表，你用N台机器一起拆分。。。哈哈

[hyde100]

看看

[justxd]

这种方法极笨，希望得到大家的指教!谢谢

[水族杰纶]

拆分表:

--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go

SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','


SQL2005用Xml:

select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b




SQL05用CTE:

;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)

生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

[生活真美好]

呵呵，是否调整库结构，使用多字段，或再建立一个子表保存记录，感觉改结构更合理。

[水族杰纶]

IF OBJECT_ID('TAB')IS NOT NULL DROP TABLE TAB
GO
CREATE TABLE  TAB(col1 VARCHAR(10),    col2  VARCHAR(10),       col3  VARCHAR(25),             col4 VARCHAR(10),          col5  NVARCHAR(5))
INSERT TAB SELECT 'abcd',    'lllls',        '123,456,789,123',    'myw',          N'备注' 
ALTER TABLE TAB
ADD ID INT IDENTITY
GO
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COL2,COl3=substring(a.Col3,b.ID,charindex(',',a.Col3+',',b.ID)-b.ID) ,COL4,COL5
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col3,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
/*Col1       COL2       COl3                      COL4       COL5  
---------- ---------- ------------------------- ---------- ----- 
abcd       lllls      123                       myw        备注
abcd       lllls      456                       myw        备注
abcd       lllls      789                       myw        备注
abcd       lllls      123                       myw        备注*/

[flairsky]

如果不用循环就用个辅助表

逃不了的

[justxd]

A表数据也有可能是下面这种(不一定每条都可拆出四条，有些可拆三条，有些可拆五条)，col3列要按","号进行拆分记录
col1 col2 col3 col4 col5
abcd lllls 123,456,789,123 myw 备注
abcd lllls 123,456,789 myw 备注
abcd lllls 123,456,789,123,23 myw 备注

[justxd]

感谢3楼的兄弟，非常感谢，我测试一下速度！

[justxd]

测试过了，使用三楼的方法更慢

[水族杰纶]

[Quote=引用 9 楼 justxd 的回复:]
测试过了，使用三楼的方法更慢
[/Quote]
條件字段建立索引

[justxd]

我用7700条数据进行测试了，我的方法用时2分36秒,三楼的方法用10分钟以上还未执行完

[justxd]

要拆的数据非常庞大.......

[claro]

帮顶