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

justxd 2008-12-03 10:12:36
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


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

...全文
127 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
claro 2008-12-03
  • 打赏
  • 举报
回复
帮顶
justxd 2008-12-03
  • 打赏
  • 举报
回复
要拆的数据非常庞大.......
justxd 2008-12-03
  • 打赏
  • 举报
回复
我用7700条数据进行测试了,我的方法用时2分36秒,三楼的方法用10分钟以上还未执行完
水族杰纶 2008-12-03
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 justxd 的回复:]
测试过了,使用三楼的方法更慢
[/Quote]
條件字段建立索引
justxd 2008-12-03
  • 打赏
  • 举报
回复
测试过了,使用三楼的方法更慢
justxd 2008-12-03
  • 打赏
  • 举报
回复
感谢3楼的兄弟,非常感谢,我测试一下速度!
justxd 2008-12-03
  • 打赏
  • 举报
回复
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 备注
flairsky 2008-12-03
  • 打赏
  • 举报
回复
如果不用循环就用个辅助表

逃不了的
水族杰纶 2008-12-03
  • 打赏
  • 举报
回复
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 备注*/
生活真美好 2008-12-03
  • 打赏
  • 举报
回复
呵呵,是否调整库结构,使用多字段,或再建立一个子表保存记录,感觉改结构更合理。
水族杰纶 2008-12-03
  • 打赏
  • 举报
回复
拆分表:

--> --> (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
*/
justxd 2008-12-03
  • 打赏
  • 举报
回复
这种方法极笨,希望得到大家的指教!谢谢
hyde100 2008-12-03
  • 打赏
  • 举报
回复
看看
dgdyq 2008-12-03
  • 打赏
  • 举报
回复
怎么拆都要时间的了,可以考虑换个思路,把A表按时间或其他分成N个表,你用N台机器一起拆分。。。哈哈

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧