SQL Server Performance Forum – Threads Archive

GReatest Value – there has to be a better way?

Hi all, I’ve just gotten a new request, and I can’t see a better way (unless there is a BIGGEST, GREATEST function I don’t know of) to select the colun that has the largest value, from 4 columns in a row, other than what I’ve just hacked together below. The thinking is to then make a function out of this ugliness, and have that available. Naturally, I am hoping someone can either smack me upside the head with a BOL reference I can’t find, or provide the village idiot with a better way of doing it. [email protected],
@col2datetime,
@col3datetime,
@col4datetime set @col1 = getdate()
set @col2 = dateadd(day,-1,getdate())
set @col3 = dateadd(day,1,getdate())
set @col4 = dateadd(day,-3,getdate()) select @col1,@col2,@col3,@Col4,
case
when @col1 > @col2
then
casewhen @col1 > @col3
then
casewhen @col1 > @Col4
then @col1
else @col4
end
else casewhen @col3 > @col4
then @col3
else @col4
end
end
elsecasewhen @col2 > @col3
then
casewhen @col2 > @col4
then @col2
else @col4
end
elsecasewhen @col3 > @col4
then @col3
else @col4
end
end
end as ‘Greatest Value’
Help?[:0] Panic, Chaos, Disorder … my work here is done –unknown

declare @t table(date1 datetime,date2 datetime,date3 datetime,date4 datetime)
insert into @t values(getdate(),getdate()-1,getdate()-2,getdate()-3)
select case when date1>date2 and date1>date3 and date1>date4 then date1
when date2>date3 and date2>date4 then date2
when date3>date4 then date3 else date4 end from @t Madhivanan Failing to plan is Planning to fail

<pre id="code"> SELECT CASE WHEN MAX(@col1) >= MAX(@col2) AND MAX(@col1) >= MAX(@col3) AND MAX(@col1) >= MAX(@col4) THEN MAX(@col1) WHEN MAX(@col2) >= MAX(@col1) AND MAX(@col2) >= MAX(@col3) AND MAX(@col2) >= MAX(@col4) THEN MAX(@col2) WHEN MAX(@col3) >= MAX(@col1) AND MAX(@col3) >= MAX(@col2) AND MAX(@col3) >= MAX(@col4) THEN MAX(@col3) ELSE MAX(@Col4) END AS Greatest_value SELECT CASE WHEN Max1 >= Max2 AND Max1 >= Max3 AND Max1 >= Max4 THEN Max1 WHEN Max2 >= Max1 AND Max2 >= Max3 AND Max2 >= Max4 THEN Max2 WHEN Max3 >= Max1 AND Max3 >= Max2 AND Max3 >= Max4 THEN Max3 ELSE Max4 END FROM (SELECT MAX(@col1) Max1, MAX(@col2) Max2, MAX(@col3) Max3, MAX(@col4) Max4)x </pre id="code"> I leave it to you to decide, if they are "better" than yours. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

I’ve now got this one – seems about the nicest (i think – what do you think?), since it is the easiest to extend: create function dbo.MaxDate (@col1 datetime,@col2 datetime,@col3 datetime,@col4 datetime)
returns datetime
as
begin
declare @Maxdate datetime
declare @SortTable table (datecol datetime)
insert into @SortTable (datecol) values (@col1)
insert into @SortTable (datecol) values (@col2)
insert into @SortTable (datecol) values (@col3)
insert into @SortTable (datecol) values (@col4) select @Maxdate = max (datecol) from @SortTable
return @MaxDate
end
But your’s is certainly nicer than my original one, thanks. Panic, Chaos, Disorder … my work here is done –unknown

If you have multiple values for each column,then do you think this will give the expected result?
Madhivanan Failing to plan is Planning to fail

Frank, As I read it, your’s doesn’t actually meet my unspecified requirement … upspecified requirement – I guess we are all used to those [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] I need the maximum date per row, and not for the entire table – I will actually be returning a resultset along the lines (pseduo code) of: name,surname,maxdate(DateOfApplication,DateOfQuote,..) for all clients… Of coourse, I’m a bit worried about performance at the moment… Panic, Chaos, Disorder … my work here is done –unknown

You know that a scalar UDF evaluates row-by-row? Not really nice. Anyway, another thought is, that if your requirement goes beyond say, 3-4 columns, one might be inclined to think this is a violation of 1NF and repeated groups. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

Frank, I do agree – the row by row is not nice. Fortunately, we’ve made a business call to decide that the DateOfApplication is the most relevant, so I no longer need to worry about this… Regards the 1nf point … while I can (and will [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ) plead the "legacy database" arguement, what we were looking at, before we convinced business, was a requirement to display the most recent datetime of a row for an application which has: ApplicationDateTime, OfferDateTime, AcceptanceDateTime, CompletionDateTime. I’m not certain that going from: Application ———– ID, ApplicationType, ApplicationDateTime, OfferDateTime, AcceptanceDateTime, CompletionDateTime to something like: <pre id="code"> Application—————–<ApplicationEvent>———-ApplicationEventType ———– —————- ——————– ID, ID, No, ApplicationType ApplicationEventType_No, Name, DateTime Description … Acceptance, Offer, Completion, Application </pre id="code"> Is really the best way? Do you remove DateOFBirth and MarraigeDate from a party row just because they could be handled more generically in the 2nd model… I wouldn’t… Panic, Chaos, Disorder … my work here is done –unknown

Hmm – provoked some interest, it seems [:0] Some feedback/comments… Now, another interesting bit. Due to all the responses, I decided to go and test this all in my test environment anyway. The results where somewhat interesting: NOTE – Frank, I had some trouble converting your’s to work in line, but since I was going quickly, and largely Find&Replacing, that may be on my side… The 2 functions created: <pre id="code"> drop function dbo.GreatestDate go CREATE FUNCTION dbo.GreatestDate ( @col1 datetime, @col2 datetime, @col3 datetime, @col4 datetime) returns Datetime as begin declare @GreatestDate Datetime select [email protected],@col2,@col3,@Col4, @GreatestDate = case when @col1 > @col2 then case when @col1 > @col3 then case when @col1 > @Col4 then @col1 else @col4 end else case when @col3 > @col4 then @col3 else @col4 end end else case when @col2 > @col3 then case when @col2 > @col4 then @col2 else @col4 end else case when @col3 > @col4 then @col3 else @col4 end end end return @GreatestDate end go drop function dbo.MaxDate go create function dbo.MaxDate (@col1 datetime,@col2 datetime,@col3 datetime,@col4 datetime) returns datetime as begin declare @Maxdate datetime declare @SortTable table (datecol datetime) insert into @SortTable (datecol) values (@col1) insert into @SortTable (datecol) values (@col2) insert into @SortTable (datecol) values (@col3) insert into @SortTable (datecol) values (@col4) select @Maxdate = max (datecol) from @SortTable return @MaxDate end go </pre id="code"> the variations run: <pre id="code"> DECLARE @StartTime AS DateTime /* ki:NOCOUNT */ SET NOCOUNT ON /* ki:CHECKPOINT */ CHECKPOINT /* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS /* ki<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ROCCACHE */ DBCC FREEPROCCACHE /* ki<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />URATION */ SET @StartTime = GetDate() select –regan strDateTimeofApplication, strDateTimeofOffer, strDateTimeofAcceptance, strDateTimeofCompletion, dbo.greatestdate(strDateTimeofApplication, strDateTimeofOffer, strDateTimeofAcceptance, strDateTimeofCompletion) as Orginial fromRedbaron.dbo.Application whereProduct_Id is null /* ki<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />URATION */ PRINT ” PRINT ‘************************* Overall Duration: regan’ + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ‘ ms *************************’ PRINT ” /* ki:NOCOUNT */ SET NOCOUNT ON /* ki:CHECKPOINT */ CHECKPOINT /* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS /* ki<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ROCCACHE */ DBCC FREEPROCCACHE /* ki<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />URATION */ SET @StartTime = GetDate() select –Mark strDateTimeofApplication, strDateTimeofOffer, strDateTimeofAcceptance, strDateTimeofCompletion, dbo.maxdate(strDateTimeofApplication, strDateTimeofOffer, strDateTimeofAcceptance, strDateTimeofCompletion) as Mark fromRedbaron.dbo.Application whereProduct_Id is null /* ki<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />URATION */ PRINT ” PRINT ‘************************* Overall Duration: Mark’ + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ‘ ms *************************’ PRINT ” /* ki:NOCOUNT */ SET NOCOUNT ON /* ki:CHECKPOINT */ CHECKPOINT /* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS /* ki<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ROCCACHE */ DBCC FREEPROCCACHE /* ki<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />URATION */ SET @StartTime = GetDate() select –MMarovic strDateTimeofApplication, strDateTimeofOffer, strDateTimeofAcceptance, strDateTimeofCompletion, case when strDateTimeofApplication >= strDateTimeofOffer and strDateTimeofApplication >= strDateTimeofAcceptance and strDateTimeofApplication >= strDateTimeofCompletion then strDateTimeofApplication when strDateTimeofOffer > strDateTimeofApplication and strDateTimeofOffer >= strDateTimeofAcceptance and strDateTimeofOffer >= strDateTimeofCompletion then strDateTimeofOffer when strDateTimeofAcceptance > strDateTimeofApplication and strDateTimeofAcceptance > strDateTimeofOffer and strDateTimeofAcceptance >= strDateTimeofCompletion then strDateTimeofAcceptance else strDateTimeofCompletion end as MMarovic fromRedbaron.dbo.Application whereProduct_Id is null /* ki<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />URATION */ PRINT ” PRINT ‘************************* Overall Duration: MMarovic ‘ + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ‘ ms *************************’ PRINT ” /* ki:NOCOUNT */ SET NOCOUNT ON /* ki:CHECKPOINT */ CHECKPOINT /* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS /* ki<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ROCCACHE */ DBCC FREEPROCCACHE /* ki<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />URATION */ SET @StartTime = GetDate() SELECT –RockMoose strDateTimeofApplication, strDateTimeofOffer, strDateTimeofAcceptance, strDateTimeofCompletion, –<————– Let’s get the maximum of these 4 columns ( SELECT MAX(val) FROM ( SELECT strDateTimeofApplication AS val UNION SELECT strDateTimeofOffer UNION SELECT strDateTimeofAcceptance UNION SELECT strDateTimeofCompletion ) vals ) AS RockMoose fromRedbaron.dbo.Application whereProduct_Id is null /* ki<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />URATION */ PRINT ” PRINT ‘************************* Overall Duration: RockMoose ‘ + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ‘ ms *************************’ PRINT ” </pre id="code"> The results are interesting: ************************* Overall Duration: regan3436 ms ************************* ************************* Overall Duration: Mark18140 ms ************************* ************************* Overall Duration: MMarovic 3453 ms ************************* ************************* Overall Duration: RockMoose 3403 ms ************************* This was in returning resultsets of 12049 rows… Access Paths for 3 are exactly the same – the long one has a far different one… Panic, Chaos, Disorder … my work here is done –unknown

I’ve seen you’ve posted this also on SQLTeam.com. What shall I say in addition to those comments? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

I have to add that I like RockMoose solution not just from performance point of view.

Right, it’s not all about performance. And his solution is very elegant, too. Btw, Regan. <s>Interesting name of a DB. </s> Interesting name for a server. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Btw, Regan. <s>Interesting name of a DB. </s> Interesting name for a server. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> Frank, you were right first time – it’s the db name [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Yes, also posted @SQLTeam – I generally do that if I think it might be a little challenging – I know there are several people that are on both forums (yourself, Derrick, and several others I’ve just discovered are on both [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] ) – I hope that isn’t a serious breach of forums-iquette [?] I’d be interested in your (and everyone else’s) point of view on the normalization comments… <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis You know that a scalar UDF evaluates row-by-row? Not really nice. <hr height="1" noshade id="quote"></blockquote id="quote"> I’m a bit surprised by my results. If you look, you’ll notice that basically my 2 functions perform near-as-damnit to the same as RockMooses straight SQL. What happenened to the row-by-row, or is Rockmooses also doing row-by-row? Just to add a last wrinkle, I’ll take my initial monster out of the functions, and throw it in as straight SQL, and then do my last set of testing – namely multi-iteration (I think I’ll do 1000 executions for each statement) – and I’ll post the results, fyi. Panic, Chaos, Disorder … my work here is done –unknown

Please check execution plan also, I’m curious to see how exec plan of RockMoose looks like.

Will do … had a hectic day, so that’s on the list for tomorrow now [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Panic, Chaos, Disorder … my work here is done –unknown

Now, that I realize that you want this max value for each row, can you test how this will perform? <pre id="code"> CREATE TABLE tableA ( id INT IDENTITY PRIMARY KEY , date1 DATETIME , date2 DATETIME , date3 DATETIME , date4 DATETIME ) INSERT INTO tableA SELECT GETDATE(),GETDATE()-1,GETDATE()-1,GETDATE()-1 INSERT INTO tableA SELECT GETDATE()+5,GETDATE()-1,GETDATE()-1,GETDATE()+3 SELECT id, MAX(iDate) AS maxdate FROM ( SELECT id, CASE i WHEN 1 THEN date1 WHEN 2 THEN date2 WHEN 3 THEN date3 WHEN 4 THEN date4 END AS iDate FROM tableA CROSS JOIN ( SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) T ) T GROUP BY id DROP TABLE tableA id maxdate ———– —————————————————— 1 2005-07-15 07:57:22.323 2 2005-07-20 07:57:22.323 (2 row(s) affected) </pre id="code"> Just a play, but I am curious how it performs against the other solutions. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

This got quite interesting (is that the geek in me coming out?). I ran this several times, with up to 10 iterations, bring the data back to the client. Because of the fact that in my environment, I get 12K rows per query, that became a bit excessive (PC REALLY started grinding when I had a 1.2 M row report [}<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />], for testing over 10 times, I dumped data into temp tables, and pushed iterations up to 100, and for the 100 runs, I remove Mark’s function, which consistently performs 6.5 to 10 times WORSE than the other variations… MARK – if performance is an issue for you, read this – you may want to review and see if these results are true for you. The only fixed conclusion’s I can draw are: My code runs better as SQL than as a function (marignal on 1 execution, but getting better and better as # iterations goes up) Marc’s function really doesn’t look like it performs near any of the other options; Strangely, Frank’s solution, which I thought would end up being the best, seems to scale slightly worse than some of the others. Anyone who is interested, and cares, maybe you can go through my testing, and results, and point out errors in my methodology, or conclusions… Results : 10 iterations, returned to client: <pre id="code"> *** Regan SQL : Iteration: 10 Duration: 720 ms *** *** Regan SQL : Overall Duration: 9906 ms *** *** Regan SQL : Average Duration: 990.60000000000 *** Regan Function : Iteration: 10 Duration: 733 ms *** *** Regan Function : Overall Duration: 10080 ms *** *** Regan Function : Average Duration: 1008.00000000000 *** Marc Function : Iteration: 10 Duration: 6250 ms *** *** Marc Function : Overall Duration: 65220 ms *** *** Marc Function : Average Duration: 6522.00000000000 *** MMarovic SQL : Iteration: 10 Duration: 750 ms *** *** MMarovic SQL : Overall Duration: 10063 ms *** *** MMarovic SQL : Average Duration: 1006.30000000000 *** Rockmoose SQL : Iteration: 10 Duration: 763 ms *** *** Rockmoose SQL : Overall Duration: 10030 ms *** *** Rockmoose SQL : Average Duration: 1003.00000000000 *** Frank SQL : Iteration: 10 Duration: 560 ms *** *** Frank SQL : Overall Duration: 8513 ms *** *** Frank SQL : Average Duration: 851.30000000000 </pre id="code"> Results : 100 iterations to temp table, only durations etc. report (excluded Marc’s fuction) <pre id="code"> *** Regan SQL : Overall Duration: 11016 ms *** *** Regan SQL : Average Duration: 110.16000000000 *** Regan Function : Overall Duration: 22733 ms *** *** Regan Function : Average Duration: 227.33000000000 *** MMarovic SQL : Overall Duration: 10936 ms *** *** MMarovic SQL : Average Duration: 109.36000000000 *** Rockmoose SQL : Overall Duration: 24173 ms *** *** Rockmoose SQL : Average Duration: 241.73000000000 *** Frank SQL : Overall Duration: 18733 ms *** *** Frank SQL : Average Duration: 187.33000000000 </pre id="code"> Intersting that in the return to client, Franks seemed to be winning, but not in the temp table version – then I realized that Franks was only returning 1 date and an ID column, whereas my previous tests had all others 5 dates … is that the difference ? I’m not sure… Just for the heck of it, I ran a set of 1000 iterations. It seems the results differ a bit. Having said that, given that this would have create 5 temp tables, each populated with 12 Million rows, maybe tempdb usage impacted. <pre id="code"> *** Regan SQL : Overall Duration: 96280 ms *** *** Regan SQL : Average Duration: 96.28000000000 *** Regan Function : Overall Duration: 198490 ms *** *** Regan Function : Average Duration: 198.49000000000 *** MMarovic SQL : Overall Duration: 81673 ms *** *** MMarovic SQL : Average Duration: 81.67300000000 *** Rockmoose SQL : Overall Duration: 213113 ms *** *** Rockmoose SQL : Average Duration: 213.11300000000 *** Frank SQL : Overall Duration: 159953 ms *** *** Frank SQL : Average Duration: 159.95300000000 </pre id="code"> The code I ran for the insert iterations: <pre id="code"> DECLARE @StartTime AS DateTime DECLARE @LoopMax AS INT, @IterationNumber AS INT, @IterationTime AS DateTime DECLARE @Optionvarchar(25) SET NOCOUNT ON SET @LoopMax = 100 CHECKPOINT /* No unnecessary IO’s when writing to log */ DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */ DBCC FREEPROCCACHE /* Clean out execution plans in cache */ /********************** Initialise DURATION Loop ***********************/ SET @StartTime = GetDate() SET @IterationNumber = 1 SET @Option = ‘Regan SQL' create table #TempResults1 (dt1datetime) WHILE @IterationNumber <= @LoopMax BEGIN SET @IterationTime = GetDate() insert into #TempResults1 (dt1) select case when strDateTimeofApplication > strDateTimeofOffer then case when strDateTimeofApplication > strDateTimeofAcceptance then case when strDateTimeofApplication > strDateTimeofCompletion then strDateTimeofApplication else strDateTimeofCompletion end else case when strDateTimeofAcceptance > strDateTimeofCompletion then strDateTimeofAcceptance else strDateTimeofCompletion end end else case when strDateTimeofOffer > strDateTimeofAcceptance then case when strDateTimeofOffer > strDateTimeofCompletion then strDateTimeofOffer else strDateTimeofCompletion end else case when strDateTimeofAcceptance > strDateTimeofCompletion then strDateTimeofAcceptance else strDateTimeofCompletion end end end as GreatestDate fromRedbaron.dbo.Application whereProduct_Id is null /****************** End Stmnt & Close DURATION Loop **********************/ – PRINT ‘*** ‘[email protected]+’ : Iteration: ‘+Cast(@IterationNumber AS varchar(10))+’ Duration: ‘ – +Cast(DateDiff(ms, @IterationTime, GetDate()) AS varchar(30)) + ‘ ms ***’ SET @IterationNumber = @IterationNumber + 1 END drop table #TempResults1 /************************** End Statement ********************************/ PRINT ‘*** ‘[email protected]+’ : Overall Duration: ‘+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) +’ ms ***’ select ‘*** ‘[email protected]+’ : Average Duration: ‘,Cast(DateDiff(ms, @StartTime, GetDate()) AS decimal)/@Loopmax CHECKPOINT /* No unnecessary IO’s when writing to log */ DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */ DBCC FREEPROCCACHE /* Clean out execution plans in cache */ /********************** Initialise DURATION Loop ***********************/ SET @StartTime = GetDate() SET @IterationNumber = 1 SET @Option = ‘Regan Function' create table #TempResults2 (dt1datetime) WHILE @IterationNumber <= @LoopMax BEGIN SET @IterationTime = GetDate() insert into #TempResults2 (dt1) select –Regan Function dbo.greatestdate( strDateTimeofApplication, strDateTimeofOffer, strDateTimeofAcceptance, strDateTimeofCompletion) as Orginial fromRedbaron.dbo.Application whereProduct_Id is null /****************** End Stmnt & Close DURATION Loop **********************/ – PRINT ‘*** ‘[email protected]+’ : Iteration: ‘+Cast(@IterationNumber AS varchar(10))+’ Duration: ‘ – +Cast(DateDiff(ms, @IterationTime, GetDate()) AS varchar(30)) + ‘ ms ***’ SET @IterationNumber = @IterationNumber + 1 END drop table #TempResults2 /************************** End Statement ********************************/ PRINT ‘*** ‘[email protected]+’ : Overall Duration: ‘+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) +’ ms ***’ select ‘*** ‘[email protected]+’ : Average Duration: ‘,Cast(DateDiff(ms, @StartTime, GetDate()) AS decimal)/@Loopmax – CHECKPOINT /* No unnecessary IO’s when writing to log */ – DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */ – DBCC FREEPROCCACHE /* Clean out execution plans in cache */ – – /********************** Initialise DURATION Loop ***********************/ – SET @StartTime = GetDate() – SET @IterationNumber = 1 – SET @Option = ‘Marc Function' – create table #TempResults3 (dt1datetime) – WHILE @IterationNumber <= @LoopMax – BEGIN – SET @IterationTime = GetDate() – insert into #TempResults3 (dt1) – select – –Mark Function – dbo.maxdate( – strDateTimeofApplication, – strDateTimeofOffer, – strDateTimeofAcceptance, – strDateTimeofCompletion) – as Mark – fromRedbaron.dbo.Application – whereProduct_Id is null – /****************** End Stmnt & Close DURATION Loop **********************/ – PRINT ‘*** ‘[email protected]+’ : Iteration: ‘+Cast(@IterationNumber AS varchar(10))+’ Duration: ‘ – +Cast(DateDiff(ms, @IterationTime, GetDate()) AS varchar(30)) + ‘ ms ***’ – SET @IterationNumber = @IterationNumber + 1 – END – drop table #TempResults3 – /************************** End Statement ********************************/ – PRINT ‘*** ‘[email protected]+’ : Overall Duration: ‘+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) – +’ ms ***’ – select ‘*** ‘[email protected]+’ : Average Duration: ‘,Cast(DateDiff(ms, @StartTime, GetDate()) AS decimal)/@Loopmax CHECKPOINT /* No unnecessary IO’s when writing to log */ DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */ DBCC FREEPROCCACHE /* Clean out execution plans in cache */ /********************** Initialise DURATION Loop ***********************/ SET @StartTime = GetDate() SET @IterationNumber = 1 SET @Option = ‘MMarovic SQL' create table #TempResults4 (dt1datetime) WHILE @IterationNumber <= @LoopMax BEGIN SET @IterationTime = GetDate() insert into #TempResults4 (dt1) select –MMarovic SQL case when strDateTimeofApplication >= strDateTimeofOffer and strDateTimeofApplication >= strDateTimeofAcceptance and strDateTimeofApplication >= strDateTimeofCompletion then strDateTimeofApplication when strDateTimeofOffer > strDateTimeofApplication and strDateTimeofOffer >= strDateTimeofAcceptance and strDateTimeofOffer >= strDateTimeofCompletion then strDateTimeofOffer when strDateTimeofAcceptance > strDateTimeofApplication and strDateTimeofAcceptance > strDateTimeofOffer and strDateTimeofAcceptance >= strDateTimeofCompletion then strDateTimeofAcceptance else strDateTimeofCompletion end as MMarovic fromRedbaron.dbo.Application whereProduct_Id is null /****************** End Stmnt & Close DURATION Loop **********************/ – PRINT ‘*** ‘[email protected]+’ : Iteration: ‘+Cast(@IterationNumber AS varchar(10))+’ Duration: ‘ – +Cast(DateDiff(ms, @IterationTime, GetDate()) AS varchar(30)) + ‘ ms ***’ SET @IterationNumber = @IterationNumber + 1 END drop table #TempResults4 /************************** End Statement ********************************/ PRINT ‘*** ‘[email protected]+’ : Overall Duration: ‘+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) +’ ms ***’ select ‘*** ‘[email protected]+’ : Average Duration: ‘,Cast(DateDiff(ms, @StartTime, GetDate()) AS decimal)/@Loopmax CHECKPOINT /* No unnecessary IO’s when writing to log */ DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */ DBCC FREEPROCCACHE /* Clean out execution plans in cache */ /********************** Initialise DURATION Loop ***********************/ SET @StartTime = GetDate() SET @IterationNumber = 1 SET @Option = ‘Rockmoose SQL' create table #TempResults5 (dt1datetime) WHILE @IterationNumber <= @LoopMax BEGIN SET @IterationTime = GetDate() insert into #TempResults5 (dt1) SELECT –RockMoose (SELECT MAX(val) FROM ( SELECT strDateTimeofApplication AS val UNION SELECT strDateTimeofOffer UNION SELECT strDateTimeofAcceptance UNION SELECT strDateTimeofCompletion ) vals ) AS RockMoose fromRedbaron.dbo.Application whereProduct_Id is null /****************** End Stmnt & Close DURATION Loop **********************/ – PRINT ‘*** ‘[email protected]+’ : Iteration: ‘+Cast(@IterationNumber AS varchar(10))+’ Duration: ‘ – +Cast(DateDiff(ms, @IterationTime, GetDate()) AS varchar(30)) + ‘ ms ***’ SET @IterationNumber = @IterationNumber + 1 END drop table #TempResults5 /************************** End Statement ********************************/ PRINT ‘*** ‘[email protected]+’ : Overall Duration: ‘+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) +’ ms ***’ select ‘*** ‘[email protected]+’ : Average Duration: ‘,Cast(DateDiff(ms, @StartTime, GetDate()) AS decimal)/@Loopmax CHECKPOINT /* No unnecessary IO’s when writing to log */ DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */ DBCC FREEPROCCACHE /* Clean out execution plans in cache */ /********************** Initialise DURATION Loop ***********************/ SET @StartTime = GetDate() SET @IterationNumber = 1 SET @Option = ‘Frank SQL' create table #TempResults6 (dt1datetime) WHILE @IterationNumber <= @LoopMax BEGIN SET @IterationTime = GetDate() insert into #TempResults6 (dt1) SELECT –Frank SQL MAX(iDate) AS maxdate FROM (SELECT id, CASE i WHEN 1 THEN StrDatetimeOfApplication WHEN 2 THEN strDateTimeofOffer WHEN 3 THEN strDateTimeofAcceptance WHEN 4 THEN strDateTimeofCompletion END AS IDate FROM Redbaron..application CROSS JOIN (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) NumberColumnsTable WHERE Product_ID is null ) XJoinResultTable GROUP BY id /****************** End Stmnt & Close DURATION Loop **********************/ – PRINT ‘*** ‘[email protected]+’ : Iteration: ‘+Cast(@IterationNumber AS varchar(10))+’ Duration: ‘ – +Cast(DateDiff(ms, @IterationTime, GetDate()) AS varchar(30)) + ‘ ms ***’ SET @IterationNumber = @IterationNumber + 1 END drop table #TempResults6 /************************** End Statement ********************************/ PRINT ‘*** ‘[email protected]+’ : Overall Duration: ‘+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) +’ ms ***’ select ‘*** ‘[email protected]+’ : Average Duration: ‘,Cast(DateDiff(ms, @StartTime, GetDate()) AS decimal)/@Loopmax /* NOCOUNT */ SET NOCOUNT OFF /* IO */ SET STATISTICS IO OFF /* CPU & TIME */ SET STATISTICS TIME OFF /* PROFILE */ SET STATISTICS PROFILE OFF /****************************** DONE ***************************************/ </pre id="code"> Panic, Chaos, Disorder … my work here is done –unknown

Unfotunatelly right now I don’t have time for testing (project deadline next Thursday), so I hope someone will have time to test rockMoose modification of my solution:
case
when strDateTimeofApplication > strDateTimeofOffer and
strDateTimeofApplication > strDateTimeofAcceptance and
strDateTimeofApplication > strDateTimeofCompletion
then strDateTimeofApplication
when strDateTimeofOffer > strDateTimeofAcceptance and
strDateTimeofOffer > strDateTimeofCompletion
then strDateTimeofOffer
when strDateTimeofAcceptance > strDateTimeofCompletion
then strDateTimeofAcceptance
else strDateTimeofCompletion
end

Guys, whatever here is the outcome. This is getting really interesting. If none of you doesn’t mind, I will make a contribution to this site analyzing this thread, most likely a FAQ. Okay for everybody? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

Quite happy with that Frank… I’ll get you some testing done on the last option, and if you want, I’ll give you info regards environments run, etc. Panic, Chaos, Disorder … my work here is done –unknown

Go ahead and let us know when it’s done. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Thanks! I’ll revive this thread when I need information and/or help. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Collated and completed results for all version (Marc’s function removed from 100 and 1000 runtimes due to slow speed. Is would seem that mmarovic #2 variations wins out over the long term:
10- iterations
*** mmarovic V2 (rockmoose variation) SQL : Overall Duration: 7406 ms ***
*** mmarovic V2 (rockmoose variation) SQL : Average Duration: 740.60000000000 *** Frank SQL : Overall Duration: 8513 ms ***
*** Frank SQL : Average Duration: 851.30000000000 *** Regan SQL : Overall Duration: 9906 ms ***
*** Regan SQL : Average Duration: 990.60000000000 *** Rockmoose SQL : Overall Duration: 10030 ms ***
*** Rockmoose SQL : Average Duration: 1003.00000000000 *** MMarovic SQL : Overall Duration: 10063 ms ***
*** MMarovic SQL : Average Duration: 1006.30000000000 *** Regan Function : Overall Duration: 10080 ms ***
*** Regan Function : Average Duration: 1008.00000000000 *** Marc Function : Overall Duration: 65220 ms ***
*** Marc Function : Average Duration: 6522.00000000000
100- iterations
*** mmarovic V2 (rockmoose variation) SQL : Overall Duration: 10533 ms ***
*** mmarovic V2 (rockmoose variation) SQL : Average Duration: 105.33000000000 *** MMarovic SQL : Overall Duration: 10936 ms ***
*** MMarovic SQL : Average Duration: 109.36000000000 *** Regan SQL : Overall Duration: 11016 ms ***
*** Regan SQL : Average Duration: 110.16000000000 *** Frank SQL : Overall Duration: 18733 ms ***
*** Frank SQL : Average Duration: 187.33000000000 *** Regan Function : Overall Duration: 22733 ms ***
*** Regan Function : Average Duration: 227.33000000000 *** Rockmoose SQL : Overall Duration: 24173 ms ***
*** Rockmoose SQL : Average Duration: 241.73000000000 1000- iterations *** mmarovic V2 (rockmoose variation) SQL : Overall Duration: 75640 ms ***
*** mmarovic V2 (rockmoose variation) SQL : Average Duration: 75.64000000000 *** MMarovic SQL : Overall Duration: 81673 ms ***
*** MMarovic SQL : Average Duration: 81.67300000000 *** Regan SQL : Overall Duration: 96280 ms ***
*** Regan SQL : Average Duration: 96.28000000000 *** Frank SQL : Overall Duration: 159953 ms ***
*** Frank SQL : Average Duration: 159.95300000000 *** Regan Function : Overall Duration: 198490 ms ***
*** Regan Function : Average Duration: 198.49000000000 *** Rockmoose SQL : Overall Duration: 213113 ms ***
*** Rockmoose SQL : Average Duration: 213.11300000000
Panic, Chaos, Disorder … my work here is done –unknown

Out of interest do you get a difference with Rockmoose’s if you use UNION ALL instead of UNION. For a very basic test here I get a different execution plan for UNION ALL. Does it actually make any difference? Regards, Robert. PS Also how does UDF using the same method compare to Regan’s function?

Regan, now you only need some words around this results and you have an article yourself. If brad is willing to publish it, you can make some extra money. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

It’s not done yet. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] It looks like that droping down to the next when clause of case statement is more costly then testing one more condition inside when clause. RockMouse variation has the structure: case when col1 > col2 and col1 > col3 and col1 > col4 then col1 when col2 > col3 and col2 > col4 then col2 when col3 > col4 then col3 else col4 end Now if we choose col1 to be one that in most cases contains the biggest value, col2 next one based on the same criteria and so on, we can make even the faster query (not too much unless distibution is extremely unbalanced and optimal combination is different then one we tested). Off course that will be true only in case my theory is correct.

Yes, it’s a basic lesson when designing efficient algorithms to put the most likely condition at first. However, I am really curious how much one will gain from that repositioning. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

You are right, we will not gain anything if distribution is fairly balanced and even if it is not, we will just slightly improve performance. However I would like it to be tested to confirm or prove wrong the theory mentioned.

Actually, the distribution is not even. Basically, the ApplicationDateTime will always be populated, and will always be the oldest UNLESS the others are default ‘1900-01-01’, since the design way back when decided to default ‘1900-01-01’ rather than use NULLs. If all have valid values (i.e. not = ‘1900-01-01’) then Completed will be greater than Accepted will be greater than Offered will be greater than Application. In terms of spread, for Applications that have NULL Product_ID (i.e no product has yet bee created), I will check Thursday (away for 2 days on a process/methodology brainstorm and workshop) what the distribution is. As I mentioned somewhere along the thread, it was discovered that we didn’t actually have to do this calculation, so since then, it has been an intellectual exercise. I’ll get those distribution numbers so that we can try and generate these best query. Frank, I’ve never really written an article before. If you think it is worthwehile, I’ll try and do one, and then bounce it off you, if your willing… CiaO 4 NoW Panic, Chaos, Disorder … my work here is done –unknown

Writing an article is certainly a worthwhile experience. It happens very often to me that when I think about how to write a problem down I realize some other way to solve it. It seems that one sees things suddenly from another point of view. Sometimes solutions that I first thought were close to brilliant turn out to be crap and vice versa. Anyway, to cut a long story short, just go for it, but contact Brad first and ask him what he thinks about this stuff. [email protected] [<img src=’/community/emoticons/emotion-1.gif’ alt=’

]]>