January 30, 2014 at 8:00 am
Hmm, just to come back on this. I tested both solutions after running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE each time (I'm using a test instance obviously!) and i got the following stats:
My solution:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 24 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8192 row(s) affected)
Table '#DateTest________000000000003'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 223 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Jeff's solution:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 11 ms, elapsed time = 11 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8192 row(s) affected)
Table '#DateTest________000000000003'. Scan count 2, logical reads 72, physical reads 0, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 261 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
So in fact my solution is marginally faster and involves less i/o, but this is for a relatively small table. There will come a point, however, at which the table/index scan will become more expensive than the index seek and the other operators in the plan produced by Jeff's solution.
Regards
Lempster
January 31, 2014 at 7:37 am
Lempster (1/30/2014)
Hmm, just to come back on this. I tested both solutions after running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE each time (I'm using a test instance obviously!) and i got the following stats:My solution:
SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 24 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8192 row(s) affected)
Table '#DateTest________000000000003'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 223 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Jeff's solution:
SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 11 ms, elapsed time = 11 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8192 row(s) affected)
Table '#DateTest________000000000003'. Scan count 2, logical reads 72, physical reads 0, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 261 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
So in fact my solution is marginally faster and involves less i/o, but this is for a relatively small table. There will come a point, however, at which the table/index scan will become more expensive than the index seek and the other operators in the plan produced by Jeff's solution.
Regards
Lempster
Exactly right. Even the optimizer will sometimes do Scans (table or Index scan) on smaller things even when an index is available because it determines that a Scan would actually faster.
Here's a test of the code with substantially more. I ran this on my 4 core laptop. I agree that the difference in duration is not "substantial" in this case and that's because duration includes returns to the screen which probably would not be done in most cases. Returning data to the screen is sometimes called the "Great Equalizer" because no matter how fast your code actually is, it takes roughly the same amount of time to display the same amount of data no matter what. If you were to drive the output to variables to take the display out of the picture, you'd see a whole 'nuther story.
Note that I did add an "=" sign to your code to pick up when "today" was the end date.
Also notice that I do 4 runs each. The first run is right after the DBCC stuff and simulates what would happen if, indeed, things weren't already cached. In real life, though, it's likely that all of this would be in cache.
Because scan for lower numbers of rows are almost as fast or sometimes faster than using indexes, people are frequently fooled that their code is good enough without realizing that execution plans do change when scale of the problem changes. I also never justify non-SARGable code even when low numbers of rows are supposedly "guaranteed" because 1) that can change in a heartbeat and 2) someone else my pick up my code for use on something much larger especially if they're in a hurry for a solution.
--=============================================================================
-- Create a larger test table with the appropriate index
-- Only adding the appropriate index is a part of the solution
--=============================================================================
--DROP TABLE dbo.#DateTest
GO
--===== Create the table, as before
CREATE TABLE dbo.#DateTest
(
FromDate varchar(9)
,ToDate varchar(9)
)
;
WITH
cteFromDate AS
(
SELECT TOP 5000000
FromDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'2011','2015'),'2011')
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
)
INSERT INTO #DateTest
(FromDate,ToDate)
SELECT FromDate = CONVERT(CHAR(8),fd.FromDate,112)
+ CONVERT(CHAR(1),ABS(CHECKSUM(NEWID()))%2+1)
,ToDate = CONVERT(CHAR(8),DATEADD(dd,ABS(CHECKSUM(NEWID()))%90,fd.FromDate),112)
+ CONVERT(CHAR(1),ABS(CHECKSUM(NEWID()))%2+1)
FROM cteFromDate fd
;
--===== Add the expected index
CREATE CLUSTERED INDEX IX_#DateTest
ON #DateTest (FromDate,ToDate)
;
--=============================================================================
-- Demonstrate that the current solution will NOT do an Index Seek
-- and a method that will. The Index Seek is followed by a nice
-- high performance range scan
--=============================================================================
GO
PRINT '========== Lempster =================================================='
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Setup the variable for @Today to make testing easy
DECLARE @Today INT;
SELECT @Today = CAST(CONVERT(varchar,getdate(),112) AS int) --Or whatever
;
--===== This CANNOT do an Index Seek because of the formulas
-- on the FromDate and ToDate columns (non-SARGable).
SET STATISTICS TIME,IO ON;
SELECT ISNULL(CAST(SUBSTRING(FromDate,1,8) AS int),19000101) AS FromDate
,ISNULL(CAST(SUBSTRING(ToDate,1,8) as int),99991231) AS ToDate
FROM dbo.#DateTest
WHERE ISNULL(SUBSTRING(FromDate,1,8),19000101) <= @Today
AND ISNULL(SUBSTRING(ToDate,1,8),99991231) >= @Today;
SET STATISTICS TIME,IO OFF;
GO 4
PRINT '========== SARGable =================================================='
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Setup the variable for @Today to make testing easy
DECLARE @Today DATETIME;
SELECT @Today = GETDATE() --Or whatever
;
--===== This DOES do an Index Seek because there are no forumulas
-- on the table columns in the WHERE clause (SARGable).
SET STATISTICS TIME,IO ON;
SELECT FromDate = ISNULL(LEFT(FromDate,8),'19000101')
,ToDate = ISNULL(LEFT(ToDate ,8),'99991231')
FROM dbo.#DateTest
WHERE (FromDate <= CONVERT(CHAR(8),@Today ,112)+'9' OR FromDate IS NULL)
AND (ToDate >= CONVERT(CHAR(8),@Today ,112)+'0' OR ToDate IS NULL);
SET STATISTICS TIME,IO OFF;
GO 4
Here are the run results.
========== Lempster ==================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
(156116 row(s) affected)
Table '#DateTest___________________________________________________________________________________________________________000000000088'.
Scan count 3, logical reads 24029, physical reads 241, read-ahead reads 23902, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2200 ms, elapsed time = 2353 ms.
(156116 row(s) affected)
Table '#DateTest___________________________________________________________________________________________________________000000000088'.
Scan count 3, logical reads 24029, 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 = 2043 ms, elapsed time = 1734 ms.
(156116 row(s) affected)
Table '#DateTest___________________________________________________________________________________________________________000000000088'.
Scan count 3, logical reads 24029, 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 = 2013 ms, elapsed time = 1677 ms.
(156116 row(s) affected)
Table '#DateTest___________________________________________________________________________________________________________000000000088'.
Scan count 3, logical reads 24029, 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 = 2059 ms, elapsed time = 1731 ms.
Batch execution completed 4 times.
========== SARGable ==================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
(156116 row(s) affected)
Table '#DateTest___________________________________________________________________________________________________________000000000088'.
Scan count 2, logical reads 18529, physical reads 279, read-ahead reads 18427, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 858 ms, elapsed time = 3594 ms.
(156116 row(s) affected)
Table '#DateTest___________________________________________________________________________________________________________000000000088'.
Scan count 2, logical reads 18529, 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 = 687 ms, elapsed time = 1614 ms.
(156116 row(s) affected)
Table '#DateTest___________________________________________________________________________________________________________000000000088'.
Scan count 2, logical reads 18529, 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 = 639 ms, elapsed time = 1569 ms.
(156116 row(s) affected)
Table '#DateTest___________________________________________________________________________________________________________000000000088'.
Scan count 2, logical reads 18529, 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 = 718 ms, elapsed time = 1596 ms.
Batch execution completed 4 times.
The truth be told, they're both not that great because of the mixed character data in denormalized columns instead of using the correct DATETIME datatype.
Still, the SARGable code is almost 3 times faster overall. Imagine if all the code on your database were a mere 3 times faster. With the possible exception of SSDs (which I don't believe would help here), you can't buy a machine that's 3 times faster.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2014 at 8:23 am
Jeff Moden (1/31/2014)
The truth be told, they're both not that great because of the mixed character data in denormalized columns instead of using the correct DATETIME datatype.
Yep, and I think that's the main thing to take away from this whole thread...as was pointed out multiple times. It's always good fun and educational to get deeper into these topics though. Cheers Jeff!
Regards
Lempster
January 31, 2014 at 9:09 am
Lempster (1/31/2014)
Jeff Moden (1/31/2014)
The truth be told, they're both not that great because of the mixed character data in denormalized columns instead of using the correct DATETIME datatype.Yep, and I think that's the main thing to take away from this whole thread...as was pointed out multiple times. It's always good fun and educational to get deeper into these topics though. Cheers Jeff!
Regards
Lempster
Absolutely. Thanks for being a part of that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply