存储过程提速!! 大家帮帮忙!(12分)

现在我有一下表
表结构如下:
Style,color,qty1,qty2,qty3,qty4,qty5,desc1,desc2,desc3,desc4,desc5
表中的记录超过100000条!
现在我要将表的记录变成这样的结构进行查询
style,color,qty,desc
qty1对应desc1,qty2对应desc2........
例如:
表中某条记录为
Style ,color,qty1,qty2,qty3,qty4,qty5,desc1,desc2,desc3,desc4,desc5
A002 b01 2 3 4 5 0 aa bb cc dd ee
查询出来的结果是:
style, color, qty, desc
A002 b01 2 AA
A002 b01 3 BB
A002 b01 4 CC
A002 b01 5 DD
条件是:qty<>0
我现在的SQL语句是:
select * from (
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr1) as [3 size] from vewdwcop where qty1<>0
UNION
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr2) as [3 size] from vewdwcop where qty2<>0
UNION
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr3) as [3 size] from vewdwcop where qty3<>0
UNION
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr4) as [3 size] from vewdwcop where qty4<>0
UNION
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr5) as [3 size] from #vewdwcop where qty5<>0 ) aaa

但是这样的算法速度很慢,有没有更好更快的算法!!
大家帮帮忙!不会的帮顶一下!

D

隐身了!自己顶!!大家动动脑!写得很详细了!!看得懂吧!

直接这样，不需要再用Select　＊了
会快点点的。
select distinct Style as [1 Item Code] ,color as [2 color],UPPER(Descr1) as [3 size] from vewdwcop where qty1<>0
UNION
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr2) as [3 size] from vewdwcop where qty2<>0
UNION
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr3) as [3 size] from vewdwcop where qty3<>0
UNION
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr4) as [3 size] from vewdwcop where qty4<>0
UNION
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr5) as [3 size] from #vewdwcop where qty5<>0

谢谢,LZ2000帮顶!!

huiyue,我用select *是要把查询的数据放在临时表里面还有别的用途!
我把整个存储过程给大家修改一下吧!

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER PROC COP_GenFiles_ItemColorSize
(@copNO varchar(20),@Region varchar(10),@File_name varchar(200))
AS

--测试数据
/*
declare @CopNO varchar(20),@Region varchar(10),@Action varchar(10)
set @CopNO='TM5F07/02 '
set @Region='GZ'
set @Action='C'
--*/

declare @str varchar(1000),@remark integer

set @remark=0

select style,color,Descr1,Descr2,Descr3,Descr4,Descr5,Descr6,Descr7,qty1,qty2,qty3,qty4,qty5,qty6,qty7
into #vewdwcop from vewdwcop where cop= @copNO and Region =@Region and len(rtrim(cop))>8

if @@ROWCOUNT=0
begin
set @str='bcp "select a=null " queryout '+@File_name+' -c -S -U -P'
set @remark=1
goto Label
end

select * into ##ItemColorSize from (
select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr1) as [3 size],' ' as [4 Inseam]
from #vewdwcop
where qty1<>0

UNION

select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr2) as [3 size],' ' as [4 Inseam]
from #vewdwcop
where qty2<>0

UNION

select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr3) as [3 size],' ' as [4 Inseam]
from #vewdwcop
where qty3<>0

UNION

select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr4) as [3 size],' ' as [4 Inseam]
from #vewdwcop
where qty4<>0

UNION

select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr5) as [3 size],' ' as [4 Inseam]
from #vewdwcop
where qty5<>0

UNION

select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr6) as [3 size],' ' as [4 Inseam]
from #vewdwcop
where qty6<>0

UNION

select distinct Style as [1 Item Code],color as [2 color],UPPER(Descr7) as [3 size],' ' as [4 Inseam]
from #vewdwcop
where qty7<>0
) ItemColorSize where [3 size] is not NULL

--select * from ##ItemColorSize

set @str='bcp "select * from ##ItemColorSize " queryout '+@File_name+' -c -S -U -P'

Label:

EXEC master..xp_cmdshell @str

drop table #vewdwcop

if @remark=0
drop table ##ItemColorSize


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

大家帮忙看一下,这个存储过程哪里还可以改进的?我有一堆类似的存储过程放在一下总的存储过程中运行,太慢的!用ASP调用会出现timeout!

更改表结构..你结结构都不合理,光在SQL上想办法,提升有限的

晚起的小虫,更改表结构是不可能的!因为这是前人建的!都用了几年了!现在不可能改的!
我也知道表结构不合理,但没有办法!整个系统都是这样!现在只有在SQL上想办法!

帮忙看看!是不是没有提速了??

真的没有办改进了吗?大家帮忙想想吧!

用列转行嘛!