Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Max of 2 dates


Max of 2 dates

Author
Message
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
Alan.B (6/25/2013)
Which is why I included two solutions: My original solution which I believe is correct and one which was cleaner and faster than the one Michael posted.


Do you not test before making performance claims?

Michael Valentine Jones
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 117 ms.

Alan.B
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 269 ms.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7423
Sean Pearce (6/26/2013)
Alan.B (6/25/2013)
Which is why I included two solutions: My original solution which I believe is correct and one which was cleaner and faster than the one Michael posted.


Do you not test before making performance claims?

Michael Valentine Jones
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 117 ms.

Alan.B
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 269 ms.


First, good morning to you too. I am not trying to win a contest here and would like to think we are all on the same team. If I was mistaken it would not be the first time on this forum. That said, perhaps a friendlier tone is in order?

Regarding your question... I would not make performance claims without testing. Can you post the ddl you used to come up with those numbers? What are you comparing to what? I don't have a server handy at the moment but Id like to see how you came up with these results; they were different then mine. I will re-test this later this morning and post my results.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
Alan.B (6/26/2013)
Sean Pearce (6/26/2013)
Alan.B (6/25/2013)
Which is why I included two solutions: My original solution which I believe is correct and one which was cleaner and faster than the one Michael posted.


Do you not test before making performance claims?

Michael Valentine Jones
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 117 ms.

Alan.B
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 269 ms.


First, good morning to you too. I am not trying to win a contest here and would like to think we are all on the same team. If I was mistaken it would not be the first time on this forum. That said, perhaps a friendlier tone is in order?

Regarding your question... I would not make performance claims without testing. Can you post the ddl you used to come up with those numbers? What are you comparing to what? I don't have a server handy at the moment but Id like to see how you came up with these results; they were different then mine. I will re-test this later this morning and post my results.

Good afternoon.

I think the onus on supplying DDL for a performance test is on the person who claimed their code is faster.

Mine was simply creating two tables with a date column and loading 500,000 random dates.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7423
Sean Pearce (6/26/2013)
Alan.B (6/26/2013)
Sean Pearce (6/26/2013)
Alan.B (6/25/2013)
Which is why I included two solutions: My original solution which I believe is correct and one which was cleaner and faster than the one Michael posted.


Do you not test before making performance claims?

Michael Valentine Jones
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 117 ms.

Alan.B
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 269 ms.


First, good morning to you too. I am not trying to win a contest here and would like to think we are all on the same team. If I was mistaken it would not be the first time on this forum. That said, perhaps a friendlier tone is in order?

Regarding your question... I would not make performance claims without testing. Can you post the ddl you used to come up with those numbers? What are you comparing to what? I don't have a server handy at the moment but Id like to see how you came up with these results; they were different then mine. I will re-test this later this morning and post my results.

Good afternoon.

I think the onus on supplying DDL for a performance test is on the person who claimed their code is faster.

Mine was simply creating two tables with a date column and loading 500,000 random dates.



Fair enough, and please pardon me if I was rude (it was 5AM-ish [2 hours before Coffee] in Chicago when I replied to your post)...

You are correct Michael's Solution is a little quicker....

Code I used to test (The way I am testing here is not optimal... but I'm in a hurry):

/**************************************************
(1) Sample Data
**************************************************/

IF OBJECT_ID('tempdb..table1') IS NOT NULL
   DROP TABLE table1;
IF OBJECT_ID('tempdb..table2') IS NOT NULL
   DROP TABLE table2;
IF OBJECT_ID('tempdb..tally') IS NOT NULL
   DROP TABLE tally;

CREATE TABLE table1 (id int primary key, [date] date not null);
CREATE TABLE table2 (id int primary key, [date] date not null);
CREATE TABLE tally (n int primary key);

;WITH
   L0(c)   AS(SELECT 1 UNION ALL SELECT 1 AS O), -- 2 rows
   L1(c)   AS(SELECT 1 FROM L0 [A] CROSS JOIN L0 [B]), -- 4 rows
   L2(c)   AS(SELECT 1 FROM L1 [A] CROSS JOIN L1 [B]) , -- 16 rows
   L3(c)   AS(SELECT 1 FROM L2 [A] CROSS JOIN L2 [B]), -- 256 rows
   L4(c)   AS(SELECT 1 FROM L3 [A] CROSS JOIN L3 [B]), -- 65,536 rows
   L5(c)   AS(SELECT 1 FROM L4 [A] CROSS JOIN L4 [B]), -- 4,294,967,296 rows
   Tally_cte AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM L5)
INSERT INTO tally
   SELECT n FROM Tally_cte WHERE n<=500000;

INSERT INTO table1
   SELECT n,
         CAST(
            DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)
   FROM tally

INSERT INTO table2
   SELECT n,
         CAST(
            DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)
   FROM tally

--SELECT * FROM table1;
--SELECT * FROM table2;

/**************************************************
(2) Queries
**************************************************/
SET NOCOUNT ON

dbcc freeproccache

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
-- Michaels Solution
   select top 1
      a.[Date]
   from
      (
      select top 1 b.[Date] from table1 b order by b.[Date] desc
      union
      select top 1 c.[Date] from table2 c order by c.[Date] desc
      Wink a
   order by
      a.[Date] desc;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

DBCC freeproccache

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
   SELECT MAX([date]) [date] FROM
   (   SELECT [date] FROM table1
      UNION
      SELECT [date] FROM table2) AS a
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO



Results:


Michael Valentine Jones
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 181 ms.

Alan.B
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 218 ms, elapsed time = 227 ms.


Honestly, I made the assumption that it was faster based on the estimated query plan. I know that is not always accurate but, in this case, it made sense.



I will stand by the statement that my code is cleaner and add that it is easier to read. Just showing different ways of doing this... ;-)

Edit: Image did not post correctly.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
Gentlemen,

We are all gentlemen here right? Is it too late to join the party, or skirmish as the case may be?

How's this one stack up in your test harness Alan?


DBCC freeproccache;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
WITH T1 (d1) AS (SELECT MAX([date]) FROM table1)
,T2 (d2) AS (SELECT MAX([date]) FROM table2)
SELECT [Date]=CASE WHEN d1 > d2 THEN d1 ELSE d2 END
FROM (SELECT d1=(SELECT d1 FROM T1), d2=(SELECT d2 FROM T2)) a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO




Looked pretty good when I tried it.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
What, no takers? OK then I'll go for it.

Test harness (now 1M rows):


/**************************************************
(1) Sample Data
**************************************************/

IF OBJECT_ID('tempdb..table1') IS NOT NULL
   DROP TABLE table1;
IF OBJECT_ID('tempdb..table2') IS NOT NULL
   DROP TABLE table2;
IF OBJECT_ID('tempdb..tally') IS NOT NULL
   DROP TABLE tally;

CREATE TABLE table1 (id int primary key, [date] date not null);
CREATE TABLE table2 (id int primary key, [date] date not null);
CREATE TABLE tally (n int primary key);

;WITH
   L0(c)   AS(SELECT 1 UNION ALL SELECT 1 AS O), -- 2 rows
   L1(c)   AS(SELECT 1 FROM L0 [A] CROSS JOIN L0 [B]), -- 4 rows
   L2(c)   AS(SELECT 1 FROM L1 [A] CROSS JOIN L1 [B]) , -- 16 rows
   L3(c)   AS(SELECT 1 FROM L2 [A] CROSS JOIN L2 [B]), -- 256 rows
   L4(c)   AS(SELECT 1 FROM L3 [A] CROSS JOIN L3 [B]), -- 65,536 rows
   L5(c)   AS(SELECT 1 FROM L4 [A] CROSS JOIN L4 [B]), -- 4,294,967,296 rows
   Tally_cte AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM L5)
INSERT INTO tally
   SELECT n FROM Tally_cte WHERE n<=1000000;

INSERT INTO table1
   SELECT n,
         CAST(
            DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)
   FROM tally

INSERT INTO table2
   SELECT n,
         CAST(
            DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)
   FROM tally

--SELECT * FROM table1;
--SELECT * FROM table2;

/**************************************************
(2) Queries
**************************************************/
SET NOCOUNT ON

dbcc freeproccache
PRINT 'Michael''s Solution';
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
   select top 1
      a.[Date]
   from
      (
      select top 1 b.[Date] from table1 b order by b.[Date] desc
      union
      select top 1 c.[Date] from table2 c order by c.[Date] desc
      Wink a
   order by
      a.[Date] desc;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

DBCC freeproccache
PRINT 'Alan''s Solution';
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
   SELECT MAX([date]) [date] FROM
   (   SELECT [date] FROM table1
      UNION
      SELECT [date] FROM table2) AS a
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

DBCC freeproccache;
PRINT 'Dwain''s Solution';
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
WITH T1 (d1) AS (SELECT MAX([date]) FROM table1)
,T2 (d2) AS (SELECT MAX([date]) FROM table2)
SELECT [Date]=CASE WHEN d1 > d2 THEN d1 ELSE d2 END
FROM (SELECT d1=(SELECT d1 FROM T1), d2=(SELECT d2 FROM T2)) a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO





Test results (IO counts and DBCC display omitted):


Michael's Solution
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

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

Alan's Solution
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

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

Dwain's Solution
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1357 ms, elapsed time = 440 ms.



Note that at the original 500K rows, Michael's solution has a bit of an edge in elapsed time over mine.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7423
dwain.c (7/3/2013)
Gentlemen,

We are all gentlemen here right? Is it too late to join the party, or skirmish as the case may be?

How's this one stack up in your test harness Alan?


DBCC freeproccache;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
WITH T1 (d1) AS (SELECT MAX([date]) FROM table1)
,T2 (d2) AS (SELECT MAX([date]) FROM table2)
SELECT [Date]=CASE WHEN d1 > d2 THEN d1 ELSE d2 END
FROM (SELECT d1=(SELECT d1 FROM T1), d2=(SELECT d2 FROM T2)) a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO




Looked pretty good when I tried it.


Hey Dwain! Thanks for chiming in. Forgive the late response (returning and recovering from 4th of July weekend here).

On my machine (desktop 4GB, 8 CPUs, 3.4GHz) It appears that your query is the fastest at 500K rows and 1M.


SET NOCOUNT ON

dbcc freeproccache

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
   PRINT 'Michael''s Solution';
   select top 1
      a.[Date]
   from
      (
      select top 1 b.[Date] from table1 b order by b.[Date] desc
      union
      select top 1 c.[Date] from table2 c order by c.[Date] desc
      Wink a
   order by
      a.[Date] desc;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

DBCC freeproccache

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
   PRINT 'Alan''s Solution';
   SELECT MAX([date]) [date] FROM
   (   SELECT [date] FROM table1
      UNION
      SELECT [date] FROM table2) AS a
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

DBCC freeproccache;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
   PRINT 'Dwain''s Solution';
   WITH T1 (d1) AS (SELECT MAX([date]) FROM table1)
      ,T2 (d2) AS (SELECT MAX([date]) FROM table2)
   SELECT [Date]=CASE WHEN d1 > d2 THEN d1 ELSE d2 END
   FROM (SELECT d1=(SELECT d1 FROM T1), d2=(SELECT d2 FROM T2)) a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO



Results:


500,000 Rows:
--------------------------------------------------
Michael's Solution
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 177 ms.

Alan's Solution
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 224 ms.

Dwain's Solution
SQL Server Execution Times:
CPU time = 124 ms, elapsed time = 126 ms.

1,000,000 Rows:
--------------------------------------------------
Michael's Solution
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 350 ms.

Alan's Solution
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 437 ms.

Dwain's Solution
SQL Server Execution Times:
CPU time = 249 ms, elapsed time = 250 ms.


Nice work Dwain (as always).

Edit: Put results into a blank [Code] box.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
Hoo-uh! And here I thought everyone had forgotten this thread.

I'm not surprised by the results. I've had to clock down the machine I did this timing test on because of overheating problems.

Note that this technique is effective because of the indexing on the tables.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 1721
More fun with stats, with timed durations thrown in...and 4 alternate solutions. All four of the new ones are a few milliseconds faster but the results vary from run to run. I also threw in am itvf that measures the total duration of each.



IF OBJECT_ID('dbo.table1') IS NOT NULL
DROP TABLE dbo.table1;
IF OBJECT_ID('dbo.table2') IS NOT NULL
DROP TABLE dbo.table2;
IF OBJECT_ID('dbo.tallytest') IS NOT NULL
DROP TABLE dbo.tallytest;

CREATE TABLE dbo.table1 (id int primary key, [date] date not null);
CREATE TABLE dbo.table2 (id int primary key, [date] date not null);
CREATE TABLE dbo.tallytest (n int primary key);

;WITH
L0(c) AS(SELECT 1 UNION ALL SELECT 1 AS O), -- 2 rows
L1(c) AS(SELECT 1 FROM L0 [A] CROSS JOIN L0 [B]), -- 4 rows
L2(c) AS(SELECT 1 FROM L1 [A] CROSS JOIN L1 [B]) , -- 16 rows
L3(c) AS(SELECT 1 FROM L2 [A] CROSS JOIN L2 [B]), -- 256 rows
L4(c) AS(SELECT 1 FROM L3 [A] CROSS JOIN L3 [B]), -- 65,536 rows
L5(c) AS(SELECT 1 FROM L4 [A] CROSS JOIN L4 [B]), -- 4,294,967,296 rows
tallytest_cte AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM L5)
INSERT INTO tallytest
SELECT n FROM tallytest_cte WHERE n<=1000000;

INSERT INTO table1
SELECT n,
CAST(
DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)
FROM tallytest

INSERT INTO table2
SELECT n,
CAST(
DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)
FROM tallytest

--SELECT * FROM table1;
--SELECT * FROM table2;

IF OBJECT_ID('dbo.itvfCalculateDurationInMilliseconds') IS NOT NULL
DROP FUNCTION dbo.itvfCalculateDurationInMilliseconds
GO

CREATE FUNCTION dbo.itvfCalculateDurationInMilliseconds
(
@StartDate DATETIME2
,@EndDate DATETIME2
)
RETURNS TABLE
AS
RETURN
(
/* Original script by Tab Alleman, MSDN Forums 6/23/2011 */

SELECT
(
--DATE Portion in millseconds
CAST(
DATEDIFF(dd
, DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) --@StartDate Date portion
, DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0) --@EndDate Date portion
)
AS decimal(19,0))
* 24 --hrs in day
* 60 --minutes in hour
* 60 --seconds in minute
* 1000 --ms in second
) --difference in milleseconds of Date Portions
+
(
--TIME portion in millseconds
CAST(
DATEDIFF(ms
, DATEADD(dd, -DATEDIFF(dd, 0, @StartDate), @StartDate) --@StartDate Time portion
, DATEADD(dd, -DATEDIFF(dd, 0, @EndDate), @EndDate) --@EndDate Time portion
)
AS decimal(19,0))
) AS Duration
)
GO



/***************************************************************/

SET NOCOUNT ON

DECLARE
@StartDate DATETIME2(7),
@EndDate DATETIME2(7),
@MaxDate DATE

dbcc freeproccache

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

PRINT 'Michael''s Solution';
SET @StartDate = GETDATE()

select top 1
@MaxDate = a.[Date]
from
(
select top 1 b.[Date] from table1 b order by b.[Date] desc
union
select top 1 c.[Date] from table2 c order by c.[Date] desc
) a
order by
a.[Date] desc;

SET @EndDate = GETDATE()
PRINT '=================================================================';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT 'Michael''s Solution' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)
GO

DECLARE
@StartDate DATETIME2(7),
@EndDate DATETIME2(7),
@MaxDate DATE

DBCC freeproccache

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

PRINT 'Alan''s Solution';
SET @StartDate = GETDATE()

SELECT
@MaxDate = MAX([date])
FROM
(
SELECT [date] FROM table1
UNION
SELECT [date] FROM table2
) r

SET @EndDate = GETDATE()
PRINT '=================================================================';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT 'Alan''s Solution' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)
GO

DECLARE
@StartDate DATETIME2(7),
@EndDate DATETIME2(7),
@MaxDate DATE


DBCC freeproccache;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

PRINT 'Dwain''s Solution';
SET @StartDate = GETDATE()

;
WITH T1(d1)
AS (
SELECT
MAX([date])
FROM
table1
),
T2(d2)
AS (
SELECT
MAX([date])
FROM
table2
)
SELECT
@MaxDate = CASE WHEN d1 > d2 THEN d1
ELSE d2
END
FROM
(
SELECT d1= (SELECT d1 FROM T1),d2= (SELECT d2 FROM T2)
) a ;

SET @EndDate = GETDATE()
PRINT '=================================================================';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT 'Dwain''s Solution' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)
GO

DECLARE
@StartDate DATETIME2(7),
@EndDate DATETIME2(7),
@MaxDate DATE

DBCC freeproccache;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

PRINT 'Steven''s Solution1';
SET @StartDate = GETDATE()

SELECT TOP(1)
@MaxDate = MaxDate
FROM
(
SELECT TOP(1) MAX([Date]) AS MaxDate FROM table1 AS t1
WHERE ID > 0
UNION
SELECT TOP(1) MAX([Date]) AS MaxDate FROM table2 AS t2
WHERE ID > 0
) r

SET @EndDate = GETDATE()
PRINT '=================================================================';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT 'Steven''s Solution1' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)
GO

DECLARE
@StartDate DATETIME2(7),
@EndDate DATETIME2(7),
@MaxDate DATE


DBCC freeproccache;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

PRINT 'Steven''s Solution2';
SET @StartDate = GETDATE()

SELECT
@MaxDate = MaxDate
FROM
(
SELECT MAX([Date]) AS MaxDate FROM table1 AS t1
WHERE ID > 0
INTERSECT
SELECT MAX([Date]) AS MaxDate FROM table2 AS t2
WHERE ID > 0
) r

SET @EndDate = GETDATE()
PRINT '=================================================================';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT 'Steven''s Solution2' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)
GO

DECLARE
@StartDate DATETIME2(7),
@EndDate DATETIME2(7),
@MaxDate DATE


DBCC freeproccache;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

PRINT 'Steven''s Solution3';
SET @StartDate = GETDATE()

SELECT
@MaxDate = MaxDate
FROM
(
SELECT TOP(1)
MaxDate,
DateRank = RANK() OVER (ORDER BY MaxDate)
FROM
(
SELECT MAX([Date]) AS MaxDate FROM table1 AS t1
WHERE ID > 0
INTERSECT
SELECT MAX([Date]) AS MaxDate FROM table2 AS t2
WHERE ID > 0
) r
) r1;

SET @EndDate = GETDATE()
PRINT '=================================================================';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT 'Steven''s Solution3' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)
GO

DECLARE
@StartDate DATETIME2(7),
@EndDate DATETIME2(7),
@MaxDate DATE

DBCC freeproccache;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

PRINT 'Steven''s Solution4';
SET @StartDate = GETDATE()

SELECT
@MaxDate = MaxDate
FROM
(
SELECT MAX([Date]) AS MaxDate FROM table1 AS t1
WHERE ID > 0
INTERSECT
SELECT MAX([Date]) AS MaxDate FROM table2 AS t2
WHERE ID > 0
) r

SET @EndDate = GETDATE()
PRINT '=================================================================';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT 'Steven''s Solution4' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)
GO



Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 1721
Test results:


ID   Title   MaxDate   Duration
1   Michael's Solution   2013-07-07   1890
2   Alan's Solution      2013-07-07   2447
3   Dwain's Solution   2013-07-07   1280
4   Steven's Solution1   2013-07-07   2154
5   Steven's Solution2   2013-07-07   710
6   Steven's Solution3   2013-07-07   473
7   Steven's Solution4   2013-07-07   490


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Michael's Solution

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'table1'. Scan count 3, logical reads 2012, physical reads 24, read-ahead reads 1640, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 3, logical reads 2012, physical reads 76, read-ahead reads 1253, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Alan's Solution

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 3, logical reads 2012, physical reads 23, read-ahead reads 797, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table1'. Scan count 3, logical reads 2012, physical reads 38, read-ahead reads 356, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Dwain's Solution

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'table1'. Scan count 3, logical reads 2012, physical reads 78, read-ahead reads 922, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 3, logical reads 2008, physical reads 10, read-ahead reads 272, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Steven's Solution1

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'table1'. Scan count 3, logical reads 2180, physical reads 26, read-ahead reads 1866, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 3, logical reads 2012, physical reads 140, read-ahead reads 1060, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Steven's Solution2

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'table1'. Scan count 3, logical reads 2180, physical reads 1, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 3, logical reads 2012, physical reads 1, read-ahead reads 34, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Steven's Solution3

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'table1'. Scan count 3, logical reads 2180, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 3, logical reads 2012, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Steven's Solution4

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'table1'. Scan count 3, logical reads 2180, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 3, logical reads 2012, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

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

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search