难 ，难，sql 函数优化

[So_CooL]

怎么样优化
dbo.f_getProduceTimeEveryDay这个函数

/****************************************

测试方法：
-- exec Rpt_ProduceTimeForEveryMonth 'PDTN','2010-02-01'
****************************************/
Alter Procedure [dbo].[Rpt_ProduceTimeForEveryMonth]( @IsDept varchar(30),@IsMonth datetime )
As
Begin

if len(@IsDept ) = 0
set @IsDept = NULL

select distinct B.Name as IsDept,C.Name as IsGroup,A.StaffId,D.Name as StaffName,F.SalaryTypeName,NULL as complement,NULL as Amountsalary,cast(datepart(year,@IsMonth) as varchar(4)) +'年'+ cast(datepart(month,@IsMonth) as varchar(2)) +'月员工工时统计' as Title,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,1) as day1,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,2) as day2,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,3) as day3,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,4) as day4,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,5) as day5,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,6) as day6,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,7) as day7,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,8) as day8,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,9) as day9,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,10) as day10,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,11) as day11,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,12) as day12,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,13) as day13,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,14) as day14,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,15) as day15,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,16) as day16,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,17) as day17,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,18) as day18,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,19) as day19,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,20) as day20,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,21) as day21,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,22) as day22,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,23) as day23,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,24) as day24,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,25) as day25,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,26) as day26,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,27) as day27,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,28) as day28,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,29) as day29,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,30) as day30,
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,31) as day31,
(
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,1)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,2)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,3)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,4)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,5)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,6)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,7)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,8)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,9)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,10)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,11)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,12)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,13)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,14)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,15)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,16)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,17)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,18)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,19)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,20)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,21)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,22)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,23)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,24)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,25)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,26)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,27)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,28)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,29)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,30)
+ dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,31)
) as AmountProduceTime,
(
dbo.f_getStaffBasePriceByStaffId(A.StaffId) *
(
dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,1)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,2)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,3)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,4)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,5)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,6)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,7)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,8)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,9)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,10)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,11)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,12)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,13)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,14)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,15)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,16)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,17)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,18)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,19)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,20)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,21)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,22)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,23)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,24)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,25)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,26)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,27)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,28)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,29)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,30)
+dbo.f_getProduceTimeEveryDay(A.StaffId,A.ProduceDate,31)
)
) as Amountsalary
from ProduceTimeBomForMaster A
left join Value B on A.isDept = B.Code and B.ListCode ='Isdept'
left join ProduceTimeGroupManage C on C.Id = A.isGroup
left join [EP-S01\SQLEXPRESS].[sstMRP.NET.DB].dbo.Staff D on A.staffId collate Chinese_PRC_CI_AS = D.StaffId collate Chinese_PRC_CI_AS
left join Value E on A.IsType = E.Code and E.ListCode = 'IsType'
left join [EP-S01\SQLEXPRESS].[sstMRP.NET.DB].dbo.SalaryType F on A.SalaryType = F.SalaryTypeID
left join Value H on A.IsStatus = H.Code and H.ListCode = 'IsStatus'
where A.isDept = isnull(@IsDept,A.isDept) and convert(varchar(7),A.ProduceDate,120) = Convert(varchar(7),@IsMonth,120)

[criedshy]

头都看大了

太长了啊

可以考虑行转列，像交叉报表那样

[mbh0210]

这么长。。。。。。。。。。。。。。。。。

[大飞飞虫]

这个巨慢的原因是你用了太多的函数，每一行的每一个列都会调用一次这个函数。
如果取出的列之间有规律的，建议你用用临时表

用函数先确定下第一列的值，然后用UPDATE语句按照规律更新后面列的值