SQL Server Performance

GReatest Value - there has to be a better way?

Discussion in 'T-SQL Performance Tuning for Developers' started by SQL_Guess, Jul 13, 2005.

  1. SQL_Guess New Member

    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.



    declare@col1datetime,
    @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
  2. Madhivanan Moderator

    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
  3. FrankKalis Moderator

    <pre id="code"><font face="courier" size="2" id="code"><br />SELECT CASE<br /> WHEN MAX(@col1) &gt;= MAX(@col2) AND MAX(@col1) &gt;= MAX(@col3) AND MAX(@col1) &gt;= MAX(@col4) THEN MAX(@col1) <br /> WHEN MAX(@col2) &gt;= MAX(@col1) AND MAX(@col2) &gt;= MAX(@col3) AND MAX(@col2) &gt;= MAX(@col4) THEN MAX(@col2) <br /> WHEN MAX(@col3) &gt;= MAX(@col1) AND MAX(@col3) &gt;= MAX(@col2) AND MAX(@col3) &gt;= MAX(@col4) THEN MAX(@col3) <br /> ELSE MAX(@Col4) END AS Greatest_value<br /><br />SELECT CASE<br /> WHEN Max1 &gt;= Max2 AND Max1 &gt;= Max3 AND Max1 &gt;= Max4 THEN Max1<br /> WHEN Max2 &gt;= Max1 AND Max2 &gt;= Max3 AND Max2 &gt;= Max4 THEN Max2<br /> WHEN Max3 &gt;= Max1 AND Max3 &gt;= Max2 AND Max3 &gt;= Max4 THEN Max3<br /> ELSE Max4 END<br /> FROM<br /> (SELECT MAX(@col1) Max1, MAX(@col2) Max2, MAX(@col3) Max3, MAX(@col4) Max4)x <br /></font id="code"></pre id="code"><br /><br />I leave it to you to decide, if they are "better" than yours. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  4. SQL_Guess New Member

    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
  5. Madhivanan Moderator

    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
  6. SQL_Guess New Member

    Frank,<br /><br />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=':p' />]<br /><br />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:<br /><br />name,surname,maxdate(DateOfApplication,DateOfQuote,..)<br />for all clients...<br /><br />Of coourse, I'm a bit worried about performance at the moment...<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  7. FrankKalis Moderator

    You know that a scalar UDF evaluates row-by-row? Not really nice. <br />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=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  8. SQL_Guess New Member

    Frank,<br /><br />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...<br /><br />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.<br /><br />I'm not certain that going from:<br /><br />Application<br />-----------<br />ID,<br />ApplicationType,<br />ApplicationDateTime,<br />OfferDateTime,<br />AcceptanceDateTime,<br />CompletionDateTime<br /><br />to something like:<br /><pre id="code"><font face="courier" size="2" id="code"><br />Application-----------------&lt;ApplicationEvent&gt;----------ApplicationEventType<br />----------- ---------------- --------------------<br />ID, ID, No,<br />ApplicationType ApplicationEventType_No, Name,<br /> DateTime Description<br /><br />...<br /> Acceptance, <br /> Offer,<br /> Completion,<br /> Application<br /><br /></font id="code"></pre id="code"><br />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...<br /><br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  9. SQL_Guess New Member

    Hmm - provoked some interest, it seems [:0]<br /><br />Some feedback/comments...<br /><br />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:<br /><br />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...<br /><br />The 2 functions created:<br /><pre id="code"><font face="courier" size="2" id="code"><br />drop function dbo.GreatestDate<br />go<br />CREATE FUNCTION dbo.GreatestDate (<br /> @col1 datetime,<br /> @col2 datetime,<br /> @col3 datetime,<br /> @col4 datetime)<br />returns Datetime<br />as <br />begin<br />declare @GreatestDate Datetime<br />select --@col1,@col2,@col3,@Col4,<br /> @GreatestDate =<br /> case<br /> when @col1 &gt; @col2<br /> then<br /> case when @col1 &gt; @col3<br /> then<br /> case when @col1 &gt; @Col4<br /> then @col1<br /> else @col4<br /> end<br /> else case when @col3 &gt; @col4<br /> then @col3<br /> else @col4<br /> end<br /> end<br /> else case when @col2 &gt; @col3<br /> then<br /> case when @col2 &gt; @col4<br /> then @col2<br /> else @col4<br /> end<br /> else case when @col3 &gt; @col4<br /> then @col3<br /> else @col4<br /> end<br /> end<br /> end <br />return @GreatestDate <br />end <br />go <br /><br />drop function dbo.MaxDate<br />go<br />create function dbo.MaxDate (@col1 datetime,@col2 datetime,@col3 datetime,@col4 datetime)<br />returns datetime<br />as<br />begin<br />declare @Maxdate datetime<br />declare @SortTable table (datecol datetime)<br />insert into @SortTable (datecol) values (@col1)<br />insert into @SortTable (datecol) values (@col2)<br />insert into @SortTable (datecol) values (@col3)<br />insert into @SortTable (datecol) values (@col4)<br /><br />select @Maxdate = max (datecol) from @SortTable<br />return @MaxDate<br />end <br />go <br /><br /></font id="code"></pre id="code"><br /><br />the variations run:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @StartTime AS DateTime <br />/* ki:NOCOUNT */ SET NOCOUNT ON<br />/* ki:CHECKPOINT */ CHECKPOINT<br />/* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS<br />/* ki<img src='/community/emoticons/emotion-4.gif' alt=':p' />ROCCACHE */ DBCC FREEPROCCACHE<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt=':D' />URATION */ SET @StartTime = GetDate()<br />select <br />--regan<br />strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion, <br />dbo.greatestdate(strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion) <br />as Orginial<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt=':D' />URATION */ PRINT '' PRINT '************************* Overall Duration: regan' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT '' <br />/* ki:NOCOUNT */ SET NOCOUNT ON<br />/* ki:CHECKPOINT */ CHECKPOINT<br />/* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS<br />/* ki<img src='/community/emoticons/emotion-4.gif' alt=':p' />ROCCACHE */ DBCC FREEPROCCACHE<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt=':D' />URATION */ SET @StartTime = GetDate()<br />select <br />--Mark<br />strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion, <br />dbo.maxdate(strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion) <br />as Mark<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt=':D' />URATION */ PRINT '' PRINT '************************* Overall Duration: Mark' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT '' <br />/* ki:NOCOUNT */ SET NOCOUNT ON<br />/* ki:CHECKPOINT */ CHECKPOINT<br />/* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS<br />/* ki<img src='/community/emoticons/emotion-4.gif' alt=':p' />ROCCACHE */ DBCC FREEPROCCACHE<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt=':D' />URATION */ SET @StartTime = GetDate()<br />select <br />--MMarovic<br />strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion,<br /> case<br /> when strDateTimeofApplication &gt;= strDateTimeofOffer <br />and strDateTimeofApplication &gt;= strDateTimeofAcceptance <br />and strDateTimeofApplication &gt;= strDateTimeofCompletion <br />then strDateTimeofApplication<br /> when strDateTimeofOffer &gt; strDateTimeofApplication <br />and strDateTimeofOffer &gt;= strDateTimeofAcceptance <br />and strDateTimeofOffer &gt;= strDateTimeofCompletion <br />then strDateTimeofOffer<br /> when strDateTimeofAcceptance &gt; strDateTimeofApplication <br />and strDateTimeofAcceptance &gt; strDateTimeofOffer <br />and strDateTimeofAcceptance &gt;= strDateTimeofCompletion <br />then strDateTimeofAcceptance<br /> else strDateTimeofCompletion<br /> end as MMarovic<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt=':D' />URATION */ PRINT '' PRINT '************************* Overall Duration: MMarovic ' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT '' <br />/* ki:NOCOUNT */ SET NOCOUNT ON<br />/* ki:CHECKPOINT */ CHECKPOINT<br />/* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS<br />/* ki<img src='/community/emoticons/emotion-4.gif' alt=':p' />ROCCACHE */ DBCC FREEPROCCACHE<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt=':D' />URATION */ SET @StartTime = GetDate()<br />SELECT<br />--RockMoose<br /> strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion, --&lt;-------------- Let's get the maximum of these 4 columns<br /> (<br /> SELECT MAX(val) FROM<br /> ( SELECT strDateTimeofApplication AS val<br /> UNION SELECT strDateTimeofOffer<br /> UNION SELECT strDateTimeofAcceptance<br /> UNION SELECT strDateTimeofCompletion<br /> ) vals<br /> ) AS RockMoose<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt=':D' />URATION */ PRINT '' PRINT '************************* Overall Duration: RockMoose ' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT '' <br /></font id="code"></pre id="code"><br /><br /><br /><br /><br />The results are interesting:<br /><br />************************* Overall Duration: regan3436 ms *************************<br />************************* Overall Duration: Mark18140 ms *************************<br />************************* Overall Duration: MMarovic 3453 ms *************************<br />************************* Overall Duration: RockMoose 3403 ms *************************<br /><br />This was in returning resultsets of 12049 rows...<br /><br />Access Paths for 3 are exactly the same - the long one has a far different one...<br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  10. FrankKalis Moderator

    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=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  11. mmarovic Active Member

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

    Right, it's not all about performance. And his solution is very elegant, too. <br />Btw, Regan. <s>Interesting name of a DB. </s><br />Interesting name for a server. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  13. SQL_Guess New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Btw, Regan. <s>Interesting name of a DB. </s><br />Interesting name for a server. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Frank, you were right first time - it's the db name [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />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=':D' />] ) - I hope that isn't a serious breach of forums-iquette [?]<br /><br />I'd be interested in your (and everyone else's) point of view on the normalization comments...<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />You know that a scalar UDF evaluates row-by-row? Not really nice. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />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?<br /><br />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.<br /><br /><br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  14. mmarovic Active Member

    Please check execution plan also, I'm curious to see how exec plan of RockMoose looks like.
  15. SQL_Guess New Member

    Will do ... had a hectic day, so that's on the list for tomorrow now [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  16. FrankKalis Moderator

    Now, that I realize that you want this max value for each row, can you test how this will perform?<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE tableA<br />(<br /> id INT IDENTITY PRIMARY KEY<br /> , date1 DATETIME<br /> , date2 DATETIME<br /> , date3 DATETIME<br /> , date4 DATETIME<br />)<br />INSERT INTO tableA SELECT GETDATE(),GETDATE()-1,GETDATE()-1,GETDATE()-1<br />INSERT INTO tableA SELECT GETDATE()+5,GETDATE()-1,GETDATE()-1,GETDATE()+3<br /><br />SELECT<br /> id,<br /> MAX(iDate) AS maxdate<br />FROM (<br /> SELECT<br /> id,<br /> CASE i<br /> WHEN 1 THEN date1<br /> WHEN 2 THEN date2<br /> WHEN 3 THEN date3<br /> WHEN 4 THEN date4<br /> END AS iDate<br /> FROM tableA CROSS JOIN (<br /> SELECT 1 AS i UNION ALL <br /> SELECT 2 UNION ALL <br /> SELECT 3 UNION ALL <br /> SELECT 4<br /> ) T<br />) T<br />GROUP BY id<br />DROP TABLE tableA<br /><br />id maxdate <br />----------- ------------------------------------------------------ <br />1 2005-07-15 07:57:22.323<br />2 2005-07-20 07:57:22.323<br /><br />(2 row(s) affected)<br /></font id="code"></pre id="code"><br />Just a play, but I am curious how it performs against the other solutions. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  17. SQL_Guess New Member

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

    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
  19. FrankKalis Moderator

    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=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  20. SQL_Guess New Member

    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
  21. mmarovic Active Member

    Go ahead and let us know when it's done. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  22. FrankKalis Moderator

    Thanks! I'll revive this thread when I need information and/or help. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  23. SQL_Guess New Member

    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
  24. RGKN New Member

    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?
  25. FrankKalis Moderator

    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=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  26. mmarovic Active Member

    It's not done yet. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />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.<br /><br />RockMouse variation has the structure:<br /><br />case<br /> when col1 &gt; col2 and col1 &gt; col3 and col1 &gt; col4 then col1<br /> when col2 &gt; col3 and col2 &gt; col4 then col2<br /> when col3 &gt; col4 then col3<br /> else col4<br />end<br /><br />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).<br /><br />Off course that will be true only in case my theory is correct.
  27. FrankKalis Moderator

    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)
  28. mmarovic Active Member

    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.
  29. SQL_Guess New Member

    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
  30. FrankKalis Moderator

    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. webmaster@sql-server-performance.com<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Share This Page