T-SQL Greatest function workarounds - timing tests

Two concerns here. First, the reason the CASE statement isn't in the data generator is because it's supposed to signify different conditions. An example. Let's say this is tracking orders from all sources, and you have a source of data column. You also have columns stating 'first email', 'order from internet', etc. This switch swaps out the correct 'first contact' date with the proper setting.

By moving it to the data generator you've adjusted the column decision to the data before you ever start the process. One of the reasons for this is to actually make that decision during the greatest component. I understand where you're going with it for 'fairness' but I also wanted to keep the complexity that drove the question in the first place.

Also, Table Variables will write or not write to disk just as often/not often as a #table. Check out Wayne's table variable myths article, it's quite thorough. 🙂 I'll see if I can find the link later if some other helpful soul doesn't drop by with it.

Just so, but as discussed above, the decision tree is part of the optimization piece I'm trying to evaluate. Imagine if those values came from different tables instead of the test-harness presentation. (Order Date, Ship Date, CustomerEmailLog, etc)

Now, could/should they be compared side by side? Sure, absolutely. In that case, however, just strip out the offending case code and simply swap in Date4_1, and you'll see equivalent execution plans as to pre-deciding the variable entry.

What I really need to do to level this playing field is to variablize the values in the dates so that the case has a chance to actually short circuit more in line with probable data. I will adjust for that in the near future.

Ah, ok I see (sort of) - I thought you were just switching dates to keep the engine on it's toes 😛

No table variables here, just a plain simple variable to accept the values from the select and dump them - look again :w00t: (sometimes referred to here as a "bitbucket" after the member of the same handle I believe)

mister.magoo (4/29/2013)

D'oh. I misread what you wrote and didn't read the stinkin' code. Not my best moment. That idea I'll definately steal wholeheartedly and without shame. 🙂

However, in the interest of the fairness checks you've mentioned, I've wrapped the different styles into the process as you'll see below:

(waits about 10 minutes for the thing to complete without errors this time please c'mon anyday now... 7:29 later)

SET NOCOUNT ON;

IF OBJECT_ID ('tempdb..#blah') IS NOT NULL

DROP TABLE #blah

IF OBJECT_ID ('tempdb..#VarDates') IS NOT NULL

DROP TABLE #VarDates

IF OBJECT_ID ('tempdb..#results') IS NOT NULL

DROP TABLE #results

CREATE TABLE #blah

( RowNum INT IDENTITY(1,1),

Date1DATETIME,

Date2DATETIME,

Date3DATETIME,

Date4_1DATETIME,

Date4_2 DATETIME,

Date4_3 DATETIME

)

CREATE CLUSTERED INDEX idx_c_Blah ON #Blah

( RowNum)

CREATE TABLE #VarDates

( RowNum INT IDENTITY(1,1),

Date1DATETIME,

Date2DATETIME,

Date3DATETIME,

Date4_1DATETIME,

Date4_2 DATETIME,

Date4_3 DATETIME

)

CREATE CLUSTERED INDEX idx_c_VarDates ON #VarDates

( RowNum)

CREATE TABLE #results

(TestSource VARCHAR(15),

TestType VARCHAR(15),

MillisecondLength INT

)

;WITH cteTally

AS (

SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N

FROM sys.syscolumns sc1,

sys.syscolumns sc2,

sys.syscolumns sc3

)

INSERT INTO #blah

SELECT

-- N,

GETDATE(),

GETDATE() + 1,

GETDATE() - 1,

GETDATE() + 2,

GETDATE() + 3,

GETDATE() + 4

FROM

cteTally

;WITH cteTally

AS (

SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N

FROM sys.syscolumns sc1,

sys.syscolumns sc2,

sys.syscolumns sc3

)

INSERT INTO #VarDates

SELECT

--N,

DATEADD( dd, ABS(CHECKSUM(NEWID())) % 3250 , '20000101'),

DATEADD( dd, ABS(CHECKSUM(NEWID())) % 3250 , '20000101'),

DATEADD( dd, ABS(CHECKSUM(NEWID())) % 3250 , '20000101'),

DATEADD( dd, ABS(CHECKSUM(NEWID())) % 3250 , '20000101'),

DATEADD( dd, ABS(CHECKSUM(NEWID())) % 3250 , '20000101'),

DATEADD( dd, ABS(CHECKSUM(NEWID())) % 3250 , '20000101')

FROM

cteTally

-------------- End Structure Build ------------------

GO

-- Static Dates Build

DECLARE @dt DATETIME, @rn INT

DECLARE @StartTestTime DATETIME

-- Did you know that discard results in options in 2k8R2 actually dumps the statistics in messages, too?

-- Yeah, I didn't either until just now...

--SET STATISTICS TIME, IO ON

SET @StartTestTime = GETDATE()

-- Values GREATEST --

SELECT

@rn = RowNum,

@dt = drv.MaxDt

FROM

#blah

CROSS APPLY

(SELECT MAX( dt) AS MaxDt

FROM

( VALUES ( Date1),

(Date2),

(Date3),

(CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END)

) ValueList (dt)

) AS drv

INSERT INTO #results

VALUES ( 'Static Dates', 'Values Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

-- Cascading CrossApply GREATEST --

SELECT

@rn = RowNum,

@dt = ca3.GrtDt3

FROM

#blah AS b

CROSS APPLY

( SELECT CASE WHEN b.Date1 >= b.Date2 THEN Date1 ELSE b.Date2 END AS GrtDt1) AS ca

CROSS APPLY

( SELECT CASE WHEN ca.GrtDt1 >= b.Date3 THEN ca.GrtDt1 ELSE b.Date3 END AS GrtDt2) AS ca2

CROSS APPLY

( SELECT CASE WHEN ca2.GrtDt2 >= CASE b.RowNum %4

WHEN 1 THEN b.Date4_1

WHEN 2 THEN b.Date4_2

WHEN 3 THEN b.Date4_3

WHEN 0 THEN '19000101'

END

THEN ca2.GrtDt2

ELSE CASE b.RowNum %4

WHEN 1 THEN b.Date4_1

WHEN 2 THEN b.Date4_2

WHEN 3 THEN b.Date4_3

WHEN 0 THEN '19000101'

END

END AS GrtDt3

) AS ca3

INSERT INTO #results

VALUES ( 'Static Dates', 'Cascade Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

-- CASE method GREATEST --

SELECT

@rn = RowNum,

@dt = CASE

WHEN

Date1 >= Date2

THEN

CASE WHEN Date1 >= Date3

THEN CASE

WHEN Date1 >= CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

THEN Date1

ELSE

CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

END

ELSE CASE

WHEN Date3 >= CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

THEN Date3

ELSE

CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

END

END

ELSE

CASE WHEN Date2 >= Date3

THEN CASE

WHEN Date2 >= CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

THEN Date2

ELSE

CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

END

ELSE CASE

WHEN Date3 >= CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

THEN Date3

ELSE

CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

END

END

END

FROM

#blah

INSERT INTO #results

VALUES ( 'Static Dates', 'CASE Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

--SET STATISTICS TIME, IO OFF

GO 50

-- No case Build

DECLARE @dt DATETIME, @rn INT

DECLARE @StartTestTime DATETIME

-- Did you know that discard results in options in 2k8R2 actually dumps the statistics in messages, too?

-- Yeah, I didn't either until just now...

--SET STATISTICS TIME, IO ON

SET @StartTestTime = GETDATE()

-- Values GREATEST --

SELECT

@rn = RowNum,

@dt = drv.MaxDt

FROM

#blah

CROSS APPLY

(SELECT MAX( dt) AS MaxDt

FROM

( VALUES ( Date1),

(Date2),

(Date3),

(Date4_1)

) ValueList (dt)

) AS drv

INSERT INTO #results

VALUES ( 'No Case Check', 'Values Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

-- Cascading CrossApply GREATEST --

SELECT

@rn = RowNum,

@dt = ca3.GrtDt3

FROM

#blah AS b

CROSS APPLY

( SELECT CASE WHEN b.Date1 >= b.Date2 THEN Date1 ELSE b.Date2 END AS GrtDt1) AS ca

CROSS APPLY

( SELECT CASE WHEN ca.GrtDt1 >= b.Date3 THEN ca.GrtDt1 ELSE b.Date3 END AS GrtDt2) AS ca2

CROSS APPLY

( SELECT CASE WHEN ca2.GrtDt2 >= Date4_1

THEN ca2.GrtDt2

ELSE Date4_1

END AS GrtDt3

) AS ca3

INSERT INTO #results

VALUES ( 'No Case Check', 'Cascade Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

-- CASE method GREATEST --

SELECT

@rn = RowNum,

@dt = CASE

WHEN

Date1 >= Date2

THEN

CASE WHEN Date1 >= Date3

THEN CASE

WHEN Date1 >= Date4_1

THEN Date1

ELSE

Date4_1

END

ELSE CASE

WHEN Date3 >= Date4_1

THEN Date3

ELSE

Date4_1

END

END

ELSE

CASE WHEN Date2 >= Date3

THEN CASE

WHEN Date2 >= Date4_1

THEN Date2

ELSE

Date4_1

END

ELSE CASE

WHEN Date3 >= Date4_1

THEN Date3

ELSE

Date4_1

END

END

END

FROM

#blah

INSERT INTO #results

VALUES ( 'No Case Check', 'CASE Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

--SET STATISTICS TIME, IO OFF

GO 50

-- High Variation Date Check

-- Static Dates Build

DECLARE @dt DATETIME, @rn INT

DECLARE @StartTestTime DATETIME

-- Did you know that discard results in options in 2k8R2 actually dumps the statistics in messages, too?

-- Yeah, I didn't either until just now...

--SET STATISTICS TIME, IO ON

SET @StartTestTime = GETDATE()

-- Values GREATEST --

SELECT

@rn = RowNum,

@dt = drv.MaxDt

FROM

#VarDates

CROSS APPLY

(SELECT MAX( dt) AS MaxDt

FROM

( VALUES ( Date1),

(Date2),

(Date3),

(CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END)

) ValueList (dt)

) AS drv

INSERT INTO #results

VALUES ( 'Variable Dates', 'Values Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

-- Cascading CrossApply GREATEST --

SELECT

@rn = RowNum,

@dt = ca3.GrtDt3

FROM

#VarDates AS b

CROSS APPLY

( SELECT CASE WHEN b.Date1 >= b.Date2 THEN Date1 ELSE b.Date2 END AS GrtDt1) AS ca

CROSS APPLY

( SELECT CASE WHEN ca.GrtDt1 >= b.Date3 THEN ca.GrtDt1 ELSE b.Date3 END AS GrtDt2) AS ca2

CROSS APPLY

( SELECT CASE WHEN ca2.GrtDt2 >= CASE b.RowNum %4

WHEN 1 THEN b.Date4_1

WHEN 2 THEN b.Date4_2

WHEN 3 THEN b.Date4_3

WHEN 0 THEN '19000101'

END

THEN ca2.GrtDt2

ELSE CASE b.RowNum %4

WHEN 1 THEN b.Date4_1

WHEN 2 THEN b.Date4_2

WHEN 3 THEN b.Date4_3

WHEN 0 THEN '19000101'

END

END AS GrtDt3

) AS ca3

INSERT INTO #results

VALUES ( 'Variable Dates', 'Cascade Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

-- CASE method GREATEST --

SELECT

@rn = RowNum,

@dt = CASE

WHEN

Date1 >= Date2

THEN

CASE WHEN Date1 >= Date3

THEN CASE

WHEN Date1 >= CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

THEN Date1

ELSE

CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

END

ELSE CASE

WHEN Date3 >= CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

THEN Date3

ELSE

CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

END

END

ELSE

CASE WHEN Date2 >= Date3

THEN CASE

WHEN Date2 >= CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

THEN Date2

ELSE

CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

END

ELSE CASE

WHEN Date3 >= CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

THEN Date3

ELSE

CASE RowNum %4

WHEN 1 THEN Date4_1

WHEN 2 THEN Date4_2

WHEN 3 THEN Date4_3

WHEN 0 THEN '19000101'

END

END

END

END

FROM

#VarDates

INSERT INTO #results

VALUES ( 'Variable Dates', 'CASE Test', DATEDIFF( ms, @StartTestTime, GETDATE()))

SET @StartTestTime = GETDATE()

--SET STATISTICS TIME, IO OFF

GO 50

SELECT

TestSource,

TestType,

AVG( MillisecondLength) AS AvgLen,

MAX( MillisecondLength) AS MaxLen,

MIN( MillisecondLength) AS MinLen,

COUNT(*) AS CountOfEntries

FROM

#results

GROUP BY

TestSource,

TestType

ORDER BY

TestSource,

TestType

GO

SET NOCOUNT OFF;

GO

Along with my current results:

TestSource TestType AvgLen MaxLen MinLen CountOfEntries

--------------- --------------- ----------- ----------- ----------- --------------

No Case Check Cascade Test 671 1023 520 50

No Case Check CASE Test 591 860 463 50

No Case Check Values Test 735 1200 570 50

Static Dates Cascade Test 1348 1926 1090 50

Static Dates CASE Test 1172 1646 966 50

Static Dates Values Test 768 1010 650 50

Variable Dates Cascade Test 1308 1723 1080 50

Variable Dates CASE Test 1235 1756 953 50

Variable Dates Values Test 816 1156 670 50

Which produces some... interesting results.

For the lack of a CASE check during the evaluation, the horrible and evil case statement seems to rule the roost, with Cascade coming in second and Values coming in a horrendous 3rd.

Whenever a decision tree to determine the included value is involved however, Values wins by a landslide. I find that very interesting.

Because of Mr. Magoo's confusion as to my use case, I want to present an idea of the use case. It's not the one in question but that's a different story, it illustrates my business concern that I'm optimizing for. Not that I don't want to find the exceptions either.

Assume you have an CRS that takes orders, ships products, delivers products, and also tracks emails, phone calls, and physical mail to your customer. Sample schema would be something like this:

CREATE TABLE Customers

(CustomerID INT IDENTITY (1, 1) NOT NULL,

CustomerName VARCHAR(50) NOT NULL,

PreferredContactMethod VARCHAR(50)

)

CREATE TABLE Orders

(OrderID INT IDENTITY ( 1,1) NOT NULL,

CustomerIDINT NOT NULL,

OrderDate DATETIME NOT NULL,

ShipDate DATETIME NULL,

DeliverDate DATETIME NULL

)

CREATE TABLE OrderLineItem

(-- Stuff, not important

)

CREATE TABLE EmailContact

( CustomerID INT, EmailTime DATETIME)

CREATE TABLE SnailMail

(CustomerID INT, MailSent DATETIME)

CREATE TABLE PhoneCall

(CustomerID INT, PhoneCall DATETIME)

The idea of the query is to let us know the last time we contacted the client either about product or via their preferred contact method. So, in the end, we want something that does this:

SELECT

GREATEST( OrderDate, ShipDate, DeliverDate,

CASE PreferredContactMethod

WHEN 'Email' THEN EmailTime

WHEN 'Snailmail' THEN MailSent

WHEN 'Phone' THEN PhoneCall

ELSE GREATEST( EmailTime, MailSent, PhoneCall)

END

I hope a clarification of the use case helps to explain what drove the idea behind researching this optimization in the first place.

No worries, and steal away - that's what I did 😛

I remember waiting 40 hours once for a Trillion row test...I did get coffee though 😎

Results from 2012 SP1

Cascade Test131824601220

CASE Test123823501150

Values Test6011016503

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Much clearer 🙂

I like the idea of trying to pin down a "best practice" for this, and will watch with interest to see if you achieve that goal.

+1

Results from local instance, version 10.50.1600.1

Run 1 with actual plan selected

TestSourceTestTypeAvgLenMaxLenMinLenCountOfEntries

No Case CheckCascade Test71687060050

No Case CheckCASE Test 172699358650

No Case CheckCASE Test 270885059050

No Case CheckValues Test50566648350

Static DatesCascade Test 178595063050

Static DatesCascade Test 278298363350

Static DatesCascade Test 378797363350

Static DatesCASE Test 166581652350

Static DatesCASE Test 268391052650

Static DatesValues Test59476655050

Variable DatesCascade Test11211256102650

Variable DatesCASE Test999124089050

Variable DatesValues Test617278655350

Run 2

TestSourceTestTypeAvgLenMaxLenMinLenCountOfEntries

No Case CheckCascade Test31134030650

No Case CheckCASE Test 129932029650

No Case CheckCASE Test 230131329650

No Case CheckValues Test44658042050

Static DatesCascade Test 162066061050

Static DatesCascade Test 261464060650

Static DatesCascade Test 361364360650

Static DatesCASE Test 149059048350

Static DatesCASE Test 249053648650

Static DatesValues Test575261049650

Variable DatesCascade Test60263059650

Variable DatesCASE Test47450647050

Variable DatesValues Test574291649650

Run 3

TestSourceTestTypeAvgLenMaxLenMinLenCountOfEntries

No Case CheckCascade Test31233030650

No Case CheckCASE Test 130332329650

No Case CheckCASE Test 230231629650

No Case CheckValues Test46757643050

Static DatesCascade Test 162583661050

Static DatesCascade Test 262690361350

Static DatesCascade Test 3622100060650

Static DatesCASE Test 150360649050

Static DatesCASE Test 251399049350

Static DatesValues Test629365350650

Variable DatesCascade Test60164659350

Variable DatesCASE Test47451047050

Variable DatesValues Test646421051350

Run 4

TestSourceTestTypeAvgLenMaxLenMinLenCountOfEntries

No Case CheckCascade Test31939031050

No Case CheckCASE Test 130432330050

No Case CheckCASE Test 230733630050

No Case CheckValues Test50280343350

Static DatesCascade Test 162465661650

Static DatesCascade Test 262573061650

Static DatesCascade Test 361864661050

Static DatesCASE Test 151357050650

Static DatesCASE Test 251357350350

Static DatesValues Test637295653650

Variable DatesCascade Test62171061350

Variable DatesCASE Test47452046650

Variable DatesValues Test628292350350

Run 5

TestSourceTestTypeAvgLenMaxLenMinLenCountOfEntries

No Case CheckCascade Test30932630350

No Case CheckCASE Test 130137629650

No Case CheckCASE Test 230038029350

No Case CheckValues Test45053042050

Static DatesCascade Test 161869361050

Static DatesCascade Test 261570060650

Static DatesCascade Test 360972360050

Static DatesCASE Test 150360349350

Static DatesCASE Test 249853349050

Static DatesValues Test575283350050

Variable DatesCascade Test60268059050

Variable DatesCASE Test47453347050

Variable DatesValues Test585291650050

Test 1, Test 2 and Test 3 are slight variations of the base code, since the performance difference isn't particularly significant I'll not post unless someone requests.

Notice how much the results are bent by running with actual plan on.

Appreciate the time Mark. Would you be willing to rerun with the harness in the fourth post? After Mr. Magoo brought up a few things I expanded the harness.

I didn't run most of my tests with Execution Plans, and apparently we have some weaker hardware. Now what's interesting is in your tests CASE is the clear winner almost across the boards, whereas I'm definately finding differently.

I assume the first run was the execution plan run for you?

Hi Craig.......run on my laptop

hope this helps

kind regards

Microsoft SQL Server 2012 - 11.0.2218.0 (Intel X86)

Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

TestSource TestType AvgLen MaxLen MinLen CountOfEntries

--------------- --------------- ----------- ----------- ----------- --------------

No Case Check Cascade Test 392 430 380 50

No Case Check CASE Test 340 363 323 50

No Case Check Values Test 423 460 406 50

Static Dates Cascade Test 700 866 663 50

Static Dates CASE Test 620 776 590 50

Static Dates Values Test 532 1973 470 50

Variable Dates Cascade Test 645 670 636 50

Variable Dates CASE Test 543 586 526 50

Variable Dates Values Test 519 2336 466 50

It does J, thanks. It shows that even though you got huge outliers just like above, the ratios stayed similar to what the rest of us are finding.

Those outliers in the major runs done above seem to be rather significant. I'm going to have to test with MAXDOP 1 and see if it's parallelism that's causing the outliers. Can't hurt to know what a straight line computation would do, either.