数据调优,请经验丰富者帮我看看！-CSDN论坛

⋅深圳.net汇聚于此 ⋅深圳无工作想创业程序员 ⋅加薪了,高兴,散分 更多帖子 关注私聊空间博客 justxd 本版专家分：0 结帖率 98.57%: 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

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

0 2008-12-03 10:12:36

回复数 14 只看楼主引用举报楼主

⋅线性表（顺序表）的销毁问题 ⋅今天开始上班，愿新的一年大家都有质的飞越，散分！ ⋅GridView绑定大数据后，点击画面任意可提交的按钮后画面奔溃 更多帖子 关注私聊空间博客 hyde100 本版专家分：13843: 看看

0 2008-12-03 10:13:47

只看TA 引用举报 #1 得分 0

justxd 本版专家分：0: 这种方法极笨，希望得到大家的指教!谢谢

0 2008-12-03 10:14:33

只看TA 引用举报 #2 得分 0

⋅存储在mysql.slow_log里的慢查询分析

⋅散点分

⋅什么情况，国庆都没人散粉，接粉......

更多帖子

关注私聊空间博客

水族杰纶

本版专家分：122650

版主

银牌 2008年11月总版技术专家分月排行榜第二
2008年10月总版技术专家分月排行榜第二

微软MVP 2010年7月荣获微软MVP称号

红花 2008年11月 MS-SQL Server大版内专家分月排行榜第一
2008年10月 MS-SQL Server大版内专家分月排行榜第一

黄花 2009年2月 MS-SQL Server大版内专家分月排行榜第二
2009年1月 MS-SQL Server大版内专家分月排行榜第二
2008年12月 MS-SQL Server大版内专家分月排行榜第二
2008年9月 MS-SQL Server大版内专家分月排行榜第二

拆分表:



--> --> (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

*/

0 2008-12-03 10:14:35

只看TA 引用举报 #3 得分 25

生活真美好 本版专家分：1614: 呵呵，是否调整库结构，使用多字段，或再建立一个子表保存记录，感觉改结构更合理。

0 2008-12-03 10:16:30

只看TA 引用举报 #4 得分 10

⋅存储在mysql.slow_log里的慢查询分析

⋅散点分

⋅什么情况，国庆都没人散粉，接粉......

更多帖子

关注私聊空间博客

水族杰纶

本版专家分：122650

版主

银牌 2008年11月总版技术专家分月排行榜第二
2008年10月总版技术专家分月排行榜第二

微软MVP 2010年7月荣获微软MVP称号

红花 2008年11月 MS-SQL Server大版内专家分月排行榜第一
2008年10月 MS-SQL Server大版内专家分月排行榜第一

黄花 2009年2月 MS-SQL Server大版内专家分月排行榜第二
2009年1月 MS-SQL Server大版内专家分月排行榜第二
2008年12月 MS-SQL Server大版内专家分月排行榜第二
2008年9月 MS-SQL Server大版内专家分月排行榜第二

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

0 2008-12-03 10:19:06

只看TA 引用举报 #5 得分 0

⋅【psql区人太少，来这里求助！】postgresql 通过yum安装的如何安装tablefunc ⋅postgresql 通过yum安装的如何安装tablefunc？ ⋅copy 文件到表，但文件内有"\",导致无法完成入表 更多帖子 关注私聊空间博客 flairsky 本版专家分：5606: 如果不用循环就用个辅助表

逃不了的

0 2008-12-03 10:26:07

只看TA 引用举报 #6 得分 10

⋅深圳.net汇聚于此 ⋅深圳无工作想创业程序员 ⋅加薪了,高兴,散分 更多帖子 关注私聊空间博客 justxd 本版专家分：0: 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          备注

0 2008-12-03 10:28:55

只看TA 引用举报 #7    得分 0

justxd 本版专家分：0: 感谢3楼的兄弟，非常感谢，我测试一下速度！

0 2008-12-03 10:30:52

只看TA 引用举报 #8 得分 0

justxd 本版专家分：0: 测试过了，使用三楼的方法更慢

0 2008-12-03 11:29:19

只看TA 引用举报 #9 得分 0

⋅存储在mysql.slow_log里的慢查询分析

⋅散点分

⋅什么情况，国庆都没人散粉，接粉......

更多帖子

关注私聊空间博客

水族杰纶

本版专家分：122650

版主

银牌 2008年11月总版技术专家分月排行榜第二
2008年10月总版技术专家分月排行榜第二

微软MVP 2010年7月荣获微软MVP称号

红花 2008年11月 MS-SQL Server大版内专家分月排行榜第一
2008年10月 MS-SQL Server大版内专家分月排行榜第一

黄花 2009年2月 MS-SQL Server大版内专家分月排行榜第二
2009年1月 MS-SQL Server大版内专家分月排行榜第二
2008年12月 MS-SQL Server大版内专家分月排行榜第二
2008年9月 MS-SQL Server大版内专家分月排行榜第二: 引用 9 楼 justxd 的回复:
测试过了，使用三楼的方法更慢

條件字段建立索引

0 2008-12-03 11:36:43

只看TA 引用举报 #10 得分 0

justxd 本版专家分：0: 我用7700条数据进行测试了，我的方法用时2分36秒,三楼的方法用10分钟以上还未执行完

0 2008-12-03 11:55:51

只看TA 引用举报 #11 得分 0

justxd 本版专家分：0: 要拆的数据非常庞大.......

0 2008-12-03 12:09:11

只看TA 引用举报 #12 得分 0

⋅2011年最后一帖，祝各位阖家欢乐！ ⋅windows2008SP1,下载安装,MSSQL2011,denali,预览版,PS2.0(视频) ⋅恭喜zsh0809！！ 更多帖子 关注私聊空间博客 claro 本版专家分：34059: 帮顶

0 2008-12-03 12:40:03

只看TA 引用举报 #13 得分 2

⋅从一个表列的字符串中取以逗号分隔的ID，插入另外一个表 ⋅请教SQL2005日志传送的问题 ⋅SQL 如何最大限度匹配搜索值 更多帖子 关注私聊空间博客 dgdyq 本版专家分：77: 怎么拆都要时间的了，可以考虑换个思路，把A表按时间或其他分成N个表，你用N台机器一起拆分。。。哈哈

0 2008-12-03 13:13:23

只看TA 引用举报 #14 得分 3

数据调优,请经验丰富者帮我看看！ [问题点数：50分，结帖人justxd]