案例优化

Question

我们有一个共同的情况，用来计算工作班次。有时我们在函数中使用它，有时在代码中直接使用它。这取决于代码的使用时间。

我的问题是，是否有更有效的方法来执行这些案例？

创建计算列不是一个选项，因为时间来自许多不同的表，其中大多数是我们无法更改的表。

这是示例代码

函数

Create Function dbo.wShift(@wTime datetime) 
Returns varchar(21)
AS
Begin
    Return  case 
            when DATEPART(weekday,@wTime) between 2 and 6 and cast(@wTime as time) between cast('8:30' as time)  and cast('16:30' as time) then 'MON_FRI_8:30-16:30' 
            when DATEPART(weekday,@wTime) = 6 and cast(@wTime as time) >=  cast('16:30' as time)  
              or DATEPART(weekday,@wTime) = 2 and cast(@wTime as time) <=  cast('8:30' as time) then 'FRI-16:30 to MON-8:30'
            when DATEPART(weekday,@wTime) in (1,7) then 'FRI-16:30 to MON-8:30'         
            else 'MON_FRI_16:30-8:30' end;
END 
GO

样本数据和预期结果

Declare @shiftTimes table (MyDate datetime, eShift varchar(23) )
INSERT @shiftTimes values 
 ('2017-01-02 08:31:46.843' ,'MON_FRI_8:30-16:30')
,('2017-01-03 10:35:21.263' ,'MON_FRI_8:30-16:30')
,('2017-04-14 17:24:14.900' ,'FRI-16:30 to MON-8:30')
,('2017-01-06 16:30:51.223' ,'FRI-16:30 to MON-8:30')
,('2017-01-01 00:24:47.450' ,'FRI-16:30 to MON-8:30')
,('2017-01-08 14:22:08.920' ,'FRI-16:30 to MON-8:30')
,('2017-01-02 00:24:11.190' ,'FRI-16:30 to MON-8:30')
,('2017-04-17 07:15:15.650' ,'FRI-16:30 to MON-8:30')
,('2017-01-02 16:38:30.860' ,'MON_FRI_16:30-8:30')
,('2017-01-11 06:27:01.017' ,'MON_FRI_16:30-8:30')

在查询和测试代码中直接实现用例

;WITH Cte as
(
    SELECT 
         MyDate
        ,eShift
        ,dbo.wShift(Mydate) wShiftFunction
        ,case 
            when DATEPART(weekday,MyDate) between 2 and 6 and cast(MyDate as time) between cast('8:30' as time)  and cast('16:30' as time) then 'MON_FRI_8:30-16:30' 
            when DATEPART(weekday,MyDate) = 6 and cast(MyDate as time) >=  cast('16:30' as time)  
            or DATEPART(weekday,MyDate) = 2 and cast(MyDate as time) <=  cast('8:30' as time) then 'FRI-16:30 to MON-8:30'
            when DATEPART(weekday,MyDate) in (1,7) then 'FRI-16:30 to MON-8:30'         
            else 'MON_FRI_16:30-8:30'
        end wShiftCase
    FROM @shiftTimes
)
SELECT
     MyDate
    ,eShift
    ,wShiftFunction
    ,wShiftCase
    ,CASE WHEN eShift = wShiftFunction THEN 'OK' ELSE 'NOK' END Check_wShiftFunction
    ,CASE WHEN eShift = wShiftCase THEN 'OK' ELSE 'NOK' END Check_wShiftCase
FROM
    cte

Answer 1

我怀疑你提出这个问题的原因是使用这个问题的查询很慢。这就是标量函数的性质。将其转换为内联表值函数，并查看性能变化程度。就像这样。

Create Function dbo.wShift(@wTime datetime) 
Returns table 
AS
    Return  select eShift = case 
            when DATEPART(weekday,@wTime) between 2 and 6 and cast(@wTime as time) between cast('8:30' as time)  and cast('16:30' as time) then 'MON_FRI_8:30-16:30' 
            when DATEPART(weekday,@wTime) = 6 and cast(@wTime as time) >=  cast('16:30' as time)  
              or DATEPART(weekday,@wTime) = 2 and cast(@wTime as time) <=  cast('8:30' as time) then 'FRI-16:30 to MON-8:30'
            when DATEPART(weekday,@wTime) in (1,7) then 'FRI-16:30 to MON-8:30'         
            else 'MON_FRI_16:30-8:30' end;
GO