Greatest Value - there has to be a better way? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Greatest Value - there has to be a better way?

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-07-13 : 07:35:32

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	@col1	datetime,
	@col2	datetime,
	@col3	datetime,
	@col4	datetime

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
			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 as 'Greatest Value'

Help?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-13 : 07:41:31

I'm not sure if it's the best way to do it, but I've done this before using a udf that populates a table variable with the parameters and uses the MAX function to return the greatest.

Mark

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-07-13 : 07:52:29

so, basically:


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

Hmm - I like it - far more extensible than my horror!

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-13 : 09:33:03

Exactly. Certainly seemed to do the job for my requirement...

Mark

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-13 : 09:53:48

For performance, I would stick your boolean logic construct in the udf.
Instead of creating a table variable in the udf, for each row in the table...

You can do it like this if You want:

SELECT
	id,uid,info,base_schema_ver, --<-------------- Let's get the maximum of these 4 columns
	(
		SELECT MAX(val) FROM
			(	SELECT id AS val
				UNION SELECT uid
				UNION SELECT info
				UNION SELECT base_schema_ver
			) vals
	)  AS maxvalue
FROM
	sysobjects

The green can be used in the udf, iof 4 inserts.
I am not sure whether UNION ALL is better, but I guess it doesn't matter.
No udf at all would be the most efficient I bet.

rockmoose

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-13 : 10:58:10

I like solutions posted but from performance point of view slight modification of your original query may still be the fastest, i'm not quite sure, you have to test.

declare @col1	dateTime,
	@col2	datetime,
	@col3	datetime,
	@col4	datetime

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 and @col1 >= @col3 and @col1 >= @col4 then @col1
           when @col2 > @col1 and @col2 >= @col3 and @col2 >= @col4 then @col2
           when @col3 > @col1 and @col3 > @col2 and @col3 >= @col4 then @col3
	   else @col4
        end

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-13 : 11:17:01

Or this:

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

rockmoose

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-13 : 11:26:36

Nice :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-13 : 11:35:57

Actually,
Wanderers's original case statemant is the fastest.

It always does 3 comparisons flat before determining the highest value.
The other ones do at least 3 every time (3-6 the one I posted) comparisons.

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-13 : 11:46:01

Of course, if applicable (and possible), normalizing this table would help too!

- Jeff

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-13 : 11:46:36

That's right, but I think your last try is the best compromise between performance and style :)

I don't think a few more comparisions make any difference, but your previous solutions included creation of virtual table and that may be measurably slower.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-07-13 : 12:09:15

Hmm - provoked some interest, it seems

Some feedback/comments...

Jeff, re the design:

Regards the mornalizing point ... while I can (and will

) 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:


Application-----------------<ApplicationEvent>----------ApplicationEventType
-----------                  ----------------           --------------------
ID,                          ID,                        No,
ApplicationType              ApplicationEventType_No,   Name,
                             DateTime                   Description

...types values
                                                        Acceptance,  
                                                        Offer,
                                                        Completion,
                                                        Application

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...

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:

The 2 functions created:


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  --@col1,@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

the variations run:


DECLARE @StartTime AS DateTime 
/* ki:NOCOUNT 		*/ 	SET NOCOUNT ON
/* ki:CHECKPOINT 	*/ 	CHECKPOINT
/* ki:CLEANBUFFER 	*/ 	DBCC DROPCLEANBUFFERS
/* ki:PROCCACHE 	*/ 	DBCC FREEPROCCACHE
/* ki:DURATION 		*/ 	SET @StartTime = GetDate()
select 
	--regan
	strDateTimeofApplication,
	strDateTimeofOffer,
	strDateTimeofAcceptance,
	strDateTimeofCompletion, 
	dbo.greatestdate(	strDateTimeofApplication,
				strDateTimeofOffer,
				strDateTimeofAcceptance,
				strDateTimeofCompletion) 
	as Orginial
from	Redbaron.dbo.Application
where	Product_Id is null
/* ki:DURATION 		*/ 	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:PROCCACHE 	*/ 	DBCC FREEPROCCACHE
/* ki:DURATION 		*/ 	SET @StartTime = GetDate()
select 
	--Mark
	strDateTimeofApplication,
	strDateTimeofOffer,
	strDateTimeofAcceptance,
	strDateTimeofCompletion, 
	dbo.maxdate(	strDateTimeofApplication,
			strDateTimeofOffer,
			strDateTimeofAcceptance,
			strDateTimeofCompletion) 
	as Mark
from	Redbaron.dbo.Application
where	Product_Id is null
/* ki:DURATION 		*/ 	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:PROCCACHE 	*/ 	DBCC FREEPROCCACHE
/* ki:DURATION 		*/ 	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
from	Redbaron.dbo.Application
where	Product_Id is null
/* ki:DURATION 		*/ 	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:PROCCACHE 	*/ 	DBCC FREEPROCCACHE
/* ki:DURATION 		*/ 	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
from	Redbaron.dbo.Application
where	Product_Id is null
/* ki:DURATION 		*/ 	PRINT '' PRINT '************************* Overall Duration: 	RockMoose ' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT ''

The results are interesting:

************************* Overall Duration: regan 3436 ms *************************
************************* Overall Duration: Mark 18140 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...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-13 : 12:24:33

I'm surprised. It is true that a few comparisons doesn't make a big difference, but I expected solution RockMoose solution you tested to be slightly slower.

Congratulations RockMoose, the prize is yours.

Ít's the fastest in the most stylish soliotion (at least IMO).

Regan thank you for feedback, I've learnt something again.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-13 : 13:48:27

quote:
Originally posted by mmarovic

I'm surprised. It is true that a few comparisons doesn't make a big difference, but I expected solution RockMoose solution you tested to be slightly slower.

Congratulations RockMoose, the prize is yours. Ít's the fastest in the most stylish soliotion (at least IMO).

Regan thank you for feedback, I've learnt something again.

I ran some tests too...
The results point clearly at straight comparison being much faster than derived table.
I found that UNION ALL is better btw.

Regan, I think You can basically cut and paste this, and try as well if You want.

--set statistics time on
--set statistics io on
-- Application :: 40000 random rows

SELECT TOP 1
(	SELECT MAX(val) FROM
	( 	SELECT strDateTimeofApplication AS val
		UNION ALL SELECT strDateTimeofOffer
		UNION ALL SELECT strDateTimeofAcceptance
		UNION ALL SELECT strDateTimeofCompletion
	) vals
) AS usingUnion
FROM dbo.Application
ORDER BY 1 desc

select top 1
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 as usingComparison
from dbo.Application
order by 1 desc

usingUnion
------------------------------------------------------
2016-11-10 03:46:20.060

Table 'Application'. Scan count 1, logical reads 213, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 317 ms,  elapsed time = 317 ms.

usingComparison
------------------------------------------------------
2016-11-10 03:46:20.060

Table 'Application'. Scan count 1, logical reads 213, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 45 ms,  elapsed time = 45 ms.

rockmoose

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-07-15 : 10:23:25

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

, 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:


*** 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

Results : 100 iterations to temp table, only durations etc. report (excluded Marc's fuction)


*** 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

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.


*** 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

The code I ran for the insert iterations:


DECLARE @StartTime AS DateTime 
DECLARE @LoopMax AS INT, @IterationNumber AS INT, @IterationTime AS DateTime 
DECLARE @Option	varchar(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 (dt1	datetime)
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
	from	Redbaron.dbo.Application
	where	Product_Id is null

	/****************** End Stmnt & Close DURATION Loop **********************/
-- 	PRINT '*** '+@option+' : 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 '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30))
		+' ms ***' 
select '*** '+@option+' : 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 (dt1	datetime)
WHILE @IterationNumber <= @LoopMax 
	BEGIN 
	SET @IterationTime = GetDate()
	insert into #TempResults2 (dt1)
	select 
		--Regan Function
		dbo.greatestdate(	
			strDateTimeofApplication,
			strDateTimeofOffer,
			strDateTimeofAcceptance,
			strDateTimeofCompletion) 
		as Orginial
	from	Redbaron.dbo.Application
	where	Product_Id is null
	/****************** End Stmnt & Close DURATION Loop **********************/
-- 	PRINT '*** '+@option+' : 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 '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30))
		+' ms ***' 
select '*** '+@option+' : 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 (dt1	datetime)
-- WHILE @IterationNumber <= @LoopMax 
-- 	BEGIN 
-- 	SET @IterationTime = GetDate()
-- 	insert into #TempResults3 (dt1)
-- 	select 
-- 		--Mark Function
-- 		dbo.maxdate(	
-- 			strDateTimeofApplication,
-- 			strDateTimeofOffer,
-- 			strDateTimeofAcceptance,
-- 			strDateTimeofCompletion) 
-- 		as Mark
-- 	from	Redbaron.dbo.Application
-- 	where	Product_Id is null
-- 	/****************** End Stmnt & Close DURATION Loop **********************/
-- 	PRINT '*** '+@option+' : 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 '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30))
-- 		+' ms ***' 
-- select '*** '+@option+' : 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 (dt1	datetime)
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
	from	Redbaron.dbo.Application
	where	Product_Id is null
	/****************** End Stmnt & Close DURATION Loop **********************/
-- 	PRINT '*** '+@option+' : 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 '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30))
		+' ms ***' 
select '*** '+@option+' : 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 (dt1	datetime)
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
	from	Redbaron.dbo.Application
	where	Product_Id is null
	/****************** End Stmnt & Close DURATION Loop **********************/
-- 	PRINT '*** '+@option+' : 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 '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30))
		+' ms ***' 
select '*** '+@option+' : 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 (dt1	datetime)
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 '*** '+@option+' : 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 '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30))
		+' ms ***' 
select '*** '+@option+' : 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 ***************************************/

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-15 : 10:30:50

Now that's more like what I expected

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources