这段代码,大家看能不能优化一下速度?

ck_邬 2011-05-27 10:13:15

create   FUNCTION fn_GetPNumPPInfoState (@PNum varchar(30))  

RETURNS varchar(1000)  AS  

BEGIN 

	Declare   @s   varchar(8000)   

	Set   @s=''   



	Declare @Product varchar(1000)

	Declare @PartsName varchar(100)

	Declare @PPItem varchar(100)

	Declare @StatusDes varchar(100)



	declare @mytb table(id int identity(1,1),

				Product varchar(500),

				PartsName varchar(500),

				PPItem varchar(100),

				StatusDes varchar(250))



	insert into @mytb(Product,PartsName,PPItem,StatusDes)

	select distinct PnumPrintInfo.Product,case when PNumPrintInfo.PartsName='' then PNumPrintInfo.Product else PNumPrintInfo.PartsName end PartsName ,

		'印刷' as PPItem,IsNull(PlanArrage.StatusDes,'未排程') as StatusDes from (select * from PNumPrintInfo where PNumPrintInfo.PNum=@PNum) as PNumPrintInfo 

		left join (select * from PlanArrage where PlanArrage.PNum=@PNum) as PlanArrage on PlanArrage.PNum=PNumPrintInfo.PNum 

		and PlanArrage.ProductName=PNumPrintInfo.Product and PlanArrage.JSName='印刷' --where PNumPrintInfo.PNum=@PNum

	union all

	Select  distinct PNumPPInfo.Product,case when PNumPPInfo.PartsName='' then PNumPPInfo.Product else PNumPPInfo.PartsName end PartsName,

		PPItem,IsNull(PlanArrage.StatusDes,'未排程') as StatusDes from (select * from PNumPPInfo where PNumPPInfo.PNum=@PNum) as PNumPPInfo 

		left join (select * from PlanArrage where PlanArrage.PNum=@PNum) as PlanArrage on PlanArrage.PNum=PNumPPInfo.PNum --and PlanArrage.PNum=@PNum

		and PlanArrage.ProductName=PNumPPInfo.Product 

		and (PlanArrage.JSName=PNumPPInfo.TechReq or PlanArrage.JSName=PNumPPInfo.PPItem) --where PNumPPInfo.PNum=@PNum



	declare @i int

	set @i=-1

	while exists(select 1 from @mytb where id>@i)

	begin

		select top 1 @Product=Product,@PartsName=PartsName,

				@PPItem=PPItem,@StatusDes=StatusDes from @mytb where id>@i order by id desc



		if @s<>'' and @PPItem<>''

			set @s=@s+'、'

		if @PPItem<>''

			set @s=@s+@PartsName++'('+@PPItem+@StatusDes+')'

		--FETCH NEXT FROM ppCursor

		--into @Product,@PartsName,@PPItem,@StatusDes

	end



  	Return   @s 

END

...全文

150 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

kajaje 2011-06-09

打赏
举报

楼主写的这个函数是用在哪？

a850688308 2011-06-09

打赏
举报

长啊哥们我是菜鸟求个师父

vb_vs 2011-06-05

打赏
举报

尽量不要在select 里面嵌套select, 还有不要重复code

page number 是不是数字类型，如果不是，请换成数据类型，

尽量不要用tem table,



select distinct 

PnumPrintInfo.Product,

case when PNumPrintInfo.PartsName='' then PNumPrintInfo.Product else PNumPrintInfo.PartsName end PartsName ,

        '印刷' as PPItem,

IsNull(PlanArrage.StatusDes,'未排程') as StatusDes 



from  PNumPrintInfo  as PNumPrintInfo

        left join PlanArrage as PlanArrage on PlanArrage.PNum=PNumPrintInfo.PNum 

        and PlanArrage.ProductName=PNumPrintInfo.Product 

        and PlanArrage.JSName='印刷

        and PNumPrintInfo.PNum=@PNum

fallingme 2011-06-04

打赏
举报

这种方式会快点

fallingme 2011-06-04

打赏
举报

select Product,PartsName,PPItem,StatusDes,(直接计算成@s) from (select Product,PartsName,PPItem,StatusDes from table    union all

    Select  Product,PartsName,PPItem,StatusDes from table) a

--肯定快很多

xlwjnalk 2011-06-03

打赏
举报

不要使用distinct

ck_邬 2011-06-02

打赏
举报

[Quote=引用 8 楼 cscj21 的回复:]
1.函数内部是个死循环
while exists(select 1 from @mytb where id>@i)
@mytb表的数据都没变化;@mytb表的查询结果条数应该不多，还是用游标处理吧。

2.表PNumPrintInfo,PNumPPInfo查询条件的字段是否有索引
[/Quote]
死循环的问题我知道了，是我写漏了一句。两个表加了索引，速度快了那么一点点，并不太明显。。