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

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

SQL code

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

SQL code

select Product,PartsName,PPItem,StatusDes,(直接计算成@s) from (select Product,PartsName,PPItem,StatusDes from table    union all    Select  Product,PartsName,PPItem,StatusDes from table) a--肯定快很多
[解决办法]
尽量不要在select 里面嵌套select, 还有不要重复code  

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

尽量不要用tem table,  



SQL code
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