Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Max of 2 dates Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 4:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:44 AM
Points: 1,001, Visits: 3,091
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
Post #1467552
Posted Wednesday, June 26, 2013 5:10 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 652, Visits: 3,017
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1467564
Posted Wednesday, June 26, 2013 6:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:44 AM
Points: 1,001, Visits: 3,091
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
Post #1467605
Posted Wednesday, June 26, 2013 8:47 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 652, Visits: 3,017
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
) 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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1467717
Posted Wednesday, July 3, 2013 1:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:40 AM
Points: 3,443, Visits: 5,404
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!
Post #1469863
Posted Thursday, July 4, 2013 7:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:40 AM
Points: 3,443, Visits: 5,404
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
) 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!
Post #1470545
Posted Monday, July 8, 2013 12:28 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 652, Visits: 3,017
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
) 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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1471317
Posted Monday, July 8, 2013 6:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:40 AM
Points: 3,443, Visits: 5,404
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!
Post #1471399
Posted Monday, July 8, 2013 9:27 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1471426
Posted Monday, July 8, 2013 9:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.
Post #1471428
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse