July 4, 2013 at 7:38 pm
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 ), -- 4 rows
L2(c)AS(SELECT 1 FROM L1 [A] CROSS JOIN L1 ) , -- 16 rows
L3(c)AS(SELECT 1 FROM L2 [A] CROSS JOIN L2 ), -- 256 rows
L4(c)AS(SELECT 1 FROM L3 [A] CROSS JOIN L3 ), -- 65,536 rows
L5(c)AS(SELECT 1 FROM L4 [A] CROSS JOIN L4 ), -- 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 8, 2013 at 12:28 pm
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.
-- Itzik Ben-Gan 2001
July 8, 2013 at 6:29 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 8, 2013 at 9:27 pm
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 ), -- 4 rows
L2(c) AS(SELECT 1 FROM L1 [A] CROSS JOIN L1 ) , -- 16 rows
L3(c) AS(SELECT 1 FROM L2 [A] CROSS JOIN L2 ), -- 256 rows
L4(c) AS(SELECT 1 FROM L3 [A] CROSS JOIN L3 ), -- 65,536 rows
L5(c) AS(SELECT 1 FROM L4 [A] CROSS JOIN L4 ), -- 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
July 8, 2013 at 9:41 pm
Test results:
IDTitleMaxDateDuration
1Michael's Solution2013-07-071890
2Alan's Solution2013-07-072447
3Dwain's Solution2013-07-071280
4Steven's Solution12013-07-072154
5Steven's Solution22013-07-07710
6Steven's Solution32013-07-07473
7Steven's Solution42013-07-07490
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.
July 8, 2013 at 10:07 pm
Hi
Another one to add to the mix. Not any quicker, but slightly less in the IO stats
DECLARE
@StartDate DATETIME2(7),
@EndDate DATETIME2(7),
@MaxDate DATE
DBCC freeproccache;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
PRINT 'MickyT''s Solution';
SET @StartDate = GETDATE()
SELECT
@MaxDate =
COALESCE(b.MaxDate, a.MaxDate)
FROM
(
SELECT MAX([Date]) AS MaxDate FROM table1 AS t1
) a
CROSS APPLY (
SELECT MAX([Date]) AS MaxDate FROM table2 AS t2
WHERE [Date] > (a.MaxDate)
) b
SET @EndDate = GETDATE()
PRINT '=================================================================';
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT 'MickyT''s Solution' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)
GO
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply