Multiple calls to MAX(datecolumn) in select

Question

Multiple calls to MAX(datecolumn) in select

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

andis59 (11/21/2016)
Is there any difference in performance?
BTW, does the size of the database table has any impact on the Tuning Advisor and the time it takes to finish?

There's no significant difference in performance with these 2. However, you might find set-theorists that would want to avoid variables. I wouldn't correct either of those, but I thought it was a nice example on how it could be done in a different and more concise way.

In fact, I'm finding no difference in performance among the three options when there's an index in place. If there's no index, only the original version is slower. I tested this with 10 million rows.

CREATE TABLE serialno(

rowcreateddt datetime);

CREATE CLUSTERED INDEX CI_MyDatetime ON dbo.serialno(rowcreateddt);

INSERT INTO serialno

SELECT TOP 10000000 DATEADD( DD, RAND(CHECKSUM(NEWID()))*10000, '2010')

FROM sys.all_columns a, sys.all_columns b;

;

GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DECLARE @RunDateTime datetime = '20161124',

@CheckIfInFuture bit = 1,

@DaysIntoFuture int = 3;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DECLARE @TimeStamp datetime2 = sysdatetime();

IF(SELECT Max(rowcreateddt)

FROM serialno) IS NOT NULL

AND ( (SELECT Max(rowcreateddt) FROM serialno) > @RunDateTime

OR ( @CheckIfInFuture = '1' AND (SELECT Max(rowcreateddt) FROM serialno) < @RunDateTime - @DaysIntoFuture ) )

SELECT 1, DATEDIFF(McS, @TimeStamp, sysdatetime());

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET @TimeStamp = sysdatetime();

IF EXISTS( SELECT 1

FROM serialno

HAVING Max(rowcreateddt) > @RunDateTime

OR ( @CheckIfInFuture = 1 AND Max(rowcreateddt) < @RunDateTime - @DaysIntoFuture))

SELECT 2, DATEDIFF(MCS, @TimeStamp, sysdatetime());

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET @TimeStamp = sysdatetime();

DECLARE @MaxRowCreatedDT DATETIME;

SELECT @MaxRowCreatedDT = Max(rowcreateddt)

FROM serialno;

IF (@MaxRowCreatedDT IS NOT NULL)

AND (

(@MaxRowCreatedDT > @RunDateTime)

OR (

@CheckIfInFuture = '1'

AND (@MaxRowCreatedDT < @RunDateTime - @DaysIntoFuture)

)

SELECT 3, DATEDIFF(MCS, @TimeStamp, sysdatetime());

GO 5

DROP TABLE serialno;

Also, the size of the database has an impact on the Tuning Advisor. It'll take longer with more data, but it will also be more accurate with the right volume instead of having a small db to test.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

andis59@gmail.com SSCommitted Points: 1884 More actions · Answer 2

Thank you Luis.

I realize that I have a long way to go in the world of SQL.

// Anders

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

andis59 (11/22/2016)
Thank you Luis.
I realize that I have a long way to go in the world of SQL.
// Anders

The good part is that you're moving forward. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

andis59 (11/21/2016)
BTW, does the size of the database table has any impact on the Tuning Advisor and the time it takes to finish?

Quick advice, very carefully assess the output of the DTA and NEVER blindly apply any suggestions it makes.

😎

andis59@gmail.com SSCommitted Points: 1884 More actions · Answer 5

Eirikur Eiriksson (11/22/2016)
andis59 (11/21/2016)
BTW, does the size of the database table has any impact on the Tuning Advisor and the time it takes to finish?
Quick advice, very carefully assess the output of the DTA and NEVER blindly apply any suggestions it makes.
😎

Combined with the General Disclaimer from Luis

Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

They make a really good advise!

Thank you very much!