All geek questions in one place

Best Min / Max Feature for Dates in T-SQL

I created the following function to determine the MAX date between two dates. It takes approximately 00.030 to 00.050 seconds to run one of the SELECT statements in a function comment.

Is there a more efficient and cleaner way?

/* Returns the greater of two dates. SELECT dbo.fnMaxDate(NULL , NULL) SELECT dbo.fnMaxDate('1/1/2011', NULL) SELECT dbo.fnMaxDate(NULL , '1/1/2011') SELECT dbo.fnMaxDate('1/1/2011', '1/1/2011') SELECT dbo.fnMaxDate('1/2/2011', '1/1/2011') SELECT dbo.fnMaxDate('1/1/2011', '1/2/2011') */ ALTER FUNCTION dbo.fnMaxDate ( @Date1 DATETIME, @Date2 DATETIME ) RETURNS datetime AS BEGIN DECLARE @Result DATETIME IF @Date1 IS NULL AND @Date2 IS NULL SET @Result = NULL; ELSE IF @Date1 IS NULL SET @Result = @Date2 ELSE IF @Date2 IS NULL SET @Result = @Date1 ELSE IF @Date1 >= @Date2 SET @Result = @Date1 ELSE SET @Result = @Date2 RETURN @Result END
+4
source share
5 answers
 ALTER FUNCTION dbo.fnMaxDate ( @Date1 DATETIME, @Date2 DATETIME ) RETURNS datetime AS BEGIN RETURN CASE WHEN ISNULL(@Date1, @Date2) > ISNULL(@Date2, @Date1) THEN ISNULL(@Date1, @Date2) ELSE ISNULL(@Date2, @Date1) END END
+4
source

I found that the case structure is about three times faster than calling a function.

 declare @d table(d1 date, d2 date); insert into @d values(null,null) , ('2/19/2012',null) , (null,'2/19/2012') , ('2/1/2012','2/15/2012') , ('2/1/2012','1/15/2012'); declare @md date , @i int=1 , @ts datetime2 , @ms1 int , @ms2 int; -- function select @ts=GETDATE(); while @i<10000 begin select @md=dbo.fnMaxDate(d1,d2) from @d; set @i+=1; end select @ms1=DATEDIFF(ms,@ts,GETDATE()); -- recommended case structure set @i=1; select @ts=GETDATE(); while @i<10000 begin select @md=case when ISNULL(d1,d2)<ISNULL(d2,d1) then ISNULL(d2,d1) else ISNULL(d1,d2) end from @d; set @i+=1; end select @ms2=DATEDIFF(ms,@ts,GETDATE()); select [Function Run Time (ms)] =@ms1 , [Case Run Time (ms)] =@ms2 go

Result:

 Function Run Time (ms) Case Run Time (ms) ---------------------- ------------------ 940 296
+2
source 
  /* Returns the greater of two dates. SELECT dbo.fnMaxDate(NULL , NULL) SELECT dbo.fnMaxDate('1/1/2011', NULL) SELECT dbo.fnMaxDate(NULL , '1/1/2011') SELECT dbo.fnMaxDate('1/1/2011', '1/1/2011') SELECT dbo.fnMaxDate('1/2/2011', '1/1/2011') SELECT dbo.fnMaxDate('1/1/2011', '1/2/2011') */ ALTER FUNCTION dbo.fnMaxDate ( @Date1 DATETIME, @Date2 DATETIME ) RETURNS datetime AS BEGIN DECLARE @Result datetime SELECT TOP 1 @Result = T.DateValue FROM ( SELECT @Date1 DateValue UNION ALL SELECT @Date2 DateValue ) T ORDER BY T.DateValue DESC RETURN @Result END
+1
source

I know this is an old question and already answered, but when I saw that the script test had already passed, I could not resist the path :)

I created a scalar function that surpasses all other functional tests here and a function that matters even faster than scalar UDF.

Comparable result (for AMissico test)

 TVF = 0.022253313291 SVF = 0.04627526226

Functions

 /* Returns the greater of two dates. SELECT dbo.fnMaxDate(NULL , NULL) SELECT dbo.fnMaxDate('1/1/2011', NULL) SELECT dbo.fnMaxDate(NULL , '1/1/2011') SELECT dbo.fnMaxDate('1/1/2011', '1/1/2011') SELECT dbo.fnMaxDate('1/2/2011', '1/1/2011') SELECT dbo.fnMaxDate('1/1/2011', '1/2/2011') */ CREATE FUNCTION dbo.svfMaxDate ( @Date1 DATETIME, @Date2 DATETIME ) RETURNS datetime AS BEGIN RETURN coalesce(dateadd(dd, ((datediff(dd, 0, @Date1) + datediff(dd, 0, @Date2)) + abs(datediff(dd, 0, @Date1) - datediff(dd, 0, @Date2))) / 2, 0), @Date1, @Date2) END GO CREATE FUNCTION dbo.tvfMaxDate ( @Date1 DATETIME, @Date2 DATETIME ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT [MaxDate] = coalesce(dateadd(dd, ((datediff(dd, 0, @Date1) + datediff(dd, 0, @Date2)) + abs(datediff(dd, 0, @Date1) - datediff(dd, 0, @Date2))) / 2, 0), @Date1, @Date2) ; GO

I also used a simple AMissico script test to verify this, but only bothered to check my version of functions and the CASE statement, which will be used as a control point / baseline:

Test results

 Case(ms) svfMaxDate tvfMaxDate 0.01343000 0.03907000 0.01837000

Normalize test results to AMissico baseline

 Baseline Case(ms) / This Test Case(ms) = Normalisation Factor 0.01616 / 0.01334000 = 1.21139430 svfMaxDate * Normalisation Factor= Comparable Result 0.03820000 * 1.21139430 = 0.04627526226 tvfMaxDate * Normalisation Factor= Comparable Result 0.01837 * 1.21139430 = 0.022253313291

Test script

 declare @d table(d1 date, d2 date); insert into @d values(null,null) , ('2/19/2012',null) , (null,'2/19/2012') , ('2/1/2012','2/15/2012') , ('2/1/2012','1/15/2012'); declare @md date , @i int=1 , @ts datetime2 , @ms0 int , @ms1 int , @ms2 int ; -- case structure set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md=case when ISNULL(d1,d2)<ISNULL(d2,d1) then ISNULL(d2,d1) else ISNULL(d1,d2) end from @d; set @i+=1; end select @ms0=DATEDIFF(ms,@ts,GETDATE()); -- svfMaxDate, Arithmetic set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md=dbo.svfMaxDate(d1,d2) from @d; set @i+=1; end select @ms1=DATEDIFF(ms,@ts,GETDATE()); -- tvfMaxDate, Arithmetic in TVF with CROSS APPLY set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md = tf.MaxDate from @d cross apply dbo.tvfMaxDate(d1,d2) as tf; set @i+=1; end select @ms2=DATEDIFF(ms,@ts,GETDATE()); select [Case(ms)] =@ms0 /100000.0, fnMaxDate=@ms1 /100000.0, tvfMaxDate=@ms2 /100000.0 go

The TVF loan belongs to Jeff Moden, the following link was used as a link to create it: How to make scalar UDF faster (SQL Spackle)

+1
source

Based on my simple performance testing, I'm going to slightly change the version of my original function (see below).

  IS NULL CASE IS NOT NULL UNION Case(ms) Empty Func fnMaxDate1 fnMaxDate2 fnMaxDate3 fnMaxDate4 0.01616 0.0446 0.0518 0.04934 0.05036 0.06177

The fastest function method is the CASE statement, but only approx. 0.003ms The slowest function is a function using the SELECT UNION "Pittsburgh DBA". I reordered my function to first check the most common result, which will check both arguments as IS NOT NULL. This change in logic leads to performance along with the CASE function.

Therefore, I refuse performance of 0.001ms for clarity of the IS NOT NULL function (see below).

Using the following script:

 declare @d table(d1 date, d2 date); insert into @d values(null,null) , ('2/19/2012',null) , (null,'2/19/2012') , ('2/1/2012','2/15/2012') , ('2/1/2012','1/15/2012'); declare @md date , @i int=1 , @ts datetime2 , @ms0 int , @ms1 int , @ms3 int , @ms2 int , @ms4 int , @ms5 int ; -- case structure set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md=case when ISNULL(d1,d2)<ISNULL(d2,d1) then ISNULL(d2,d1) else ISNULL(d1,d2) end from @d; set @i+=1; end select @ms0=DATEDIFF(ms,@ts,GETDATE()); -- fnMaxDate1, IF IS NULL set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md=dbo.fnMaxDate1(d1,d2) from @d; set @i+=1; end select @ms1=DATEDIFF(ms,@ts,GETDATE()); -- fnMaxDate2, CASE set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md=dbo.fnMaxDate2(d1,d2) from @d; set @i+=1; end select @ms2=DATEDIFF(ms,@ts,GETDATE()); -- fnMaxDate3, IF IS NOT NULL set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md=dbo.fnMaxDate3(d1,d2) from @d; set @i+=1; end select @ms3=DATEDIFF(ms,@ts,GETDATE()); -- fnMaxDate4, SELECT UNION set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md=dbo.fnMaxDate4(d1,d2) from @d; set @i+=1; end select @ms4=DATEDIFF(ms,@ts,GETDATE()); -- fnMaxDate5, empty function call set @i=1; select @ts=GETDATE(); while @i<100000 begin select @md=dbo.fnMaxDate5(d1,d2) from @d; set @i+=1; end select @ms5=DATEDIFF(ms,@ts,GETDATE()); select [Case(ms)] =@ms0 /100000.0, [fnMaxDate5 (empty function call)] =@ms5 /100000.0, fnMaxDate1=@ms1 /100000.0, fnMaxDate2=@ms2 /100000.0, fnMaxDate3 = @ms3/100000.0, fnMaxDate4=@ms4 /100000.0 go

Finally, here is the final version of the function:

 /* Returns the greater of two dates. SELECT dbo.fnMaxDate(NULL , NULL) SELECT dbo.fnMaxDate('1/1/2011', NULL) SELECT dbo.fnMaxDate(NULL , '1/1/2011') SELECT dbo.fnMaxDate('1/1/2011', '1/1/2011') SELECT dbo.fnMaxDate('1/2/2011', '1/1/2011') SELECT dbo.fnMaxDate('1/1/2011', '1/2/2011') */ ALTER FUNCTION [dbo].[fnMaxDate] ( @Date1 DATETIME, @Date2 DATETIME ) RETURNS DATETIME AS BEGIN DECLARE @Result DATETIME IF @Date1 IS NOT NULL AND @Date2 IS NOT NULL IF @Date1 >= @Date2 SET @Result = @Date1 ELSE SET @Result = @Date2 ELSE IF @Date1 IS NULL SET @Result = @Date2 ELSE IF @Date2 IS NULL SET @Result = @Date1 RETURN @Result END
0
source

Source: https://habr.com/ru/post/1397151/

More articles:


All Articles