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 ««12

Comparing string with date Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 9:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
ChrisM@Work (1/29/2014)
Jeff Moden (1/29/2014)
Post deleted. I made a mistake. I'll be back.


A little history Jeff. Might save you some time.


You're right. That's insane and now I know what you meant in your original post. The OP changes perfectly good data to denormalized data and then tries to do a search on the denormalized data. I just don't get that and your original suggestion on this post is correct.

Too bad because I finally learned how to copy'n'paste my SARGable solution correctly.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536004
Posted Wednesday, January 29, 2014 10:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 2,036, Visits: 1,378
Jeff Moden (1/29/2014)

Too bad because I finally learned how to copy'n'paste my SARGable solution correctly.


Could you post your solution anyway Jeff?

Regards
Lempster
Post #1536009
Posted Thursday, January 30, 2014 2:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:21 AM
Points: 7, Visits: 46
..
Post #1536218
Posted Thursday, January 30, 2014 7:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
Lempster (1/29/2014)
Jeff Moden (1/29/2014)

Too bad because I finally learned how to copy'n'paste my SARGable solution correctly.


Could you post your solution anyway Jeff?

Regards
Lempster


Sure. With the understanding that such data should never be stored in a table and that it should be normalized as a real DATETIME column and a separate column for the "time of day slot" indicator, here's how to solve this problem in a SARGable fashion. For those that don't know, "SARGable" has come to basically mean "can do an Index Seek if the correct supporting index is available".

--=============================================================================
-- 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)
)
;
--===== Insert the original 4 rows in the test data
INSERT INTO dbo.#DateTest
SELECT '201401221','201401292' UNION ALL
SELECT '201401092','201401161' UNION ALL
SELECT NULL ,'201402282' UNION ALL
SELECT '201401152',NULL
;
GO
--===== Insert another 16380 similar rows
INSERT INTO #DateTest
SELECT * FROM #DateTest
GO 12
--===== Add the expected index
CREATE INDEX IX_#DateTest
ON #DateTest (FromDate,ToDate)
;
--=============================================================================
-- Demonstrate the 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
--=============================================================================
--===== Setup the variable for @Today to make testing easy
DECLARE @Today DATETIME
SELECT @Today = GETDATE() --Or whatever
;
--===== This CANNOT do an Index Seek because of the formulas
-- on the FromDate and ToDate columns (non-SARGable).
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
;
--===== This DOES do an Index Seek because there are no forumulas
-- on the table columns in the WHERE clause (SARGable).
SELECT FromDate = FromDate -- ISNULL(FromDate,'19000101')
,ToDate = ToDate -- ISNULL(ToDate ,'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)
;


Notice the neither FromDate or ToDate is contained in a formula.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536338
Posted Thursday, January 30, 2014 7:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 2,036, Visits: 1,378
Thanks Jeff and I totally agree with you about doing this the proper way using DATETIME data type which is what I prefaced my first reply to the OP with.
Post #1536344
Posted Thursday, January 30, 2014 8:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 2,036, Visits: 1,378
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
Post #1536362
Posted Friday, January 31, 2014 7:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536779
Posted Friday, January 31, 2014 8:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 2,036, Visits: 1,378
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
Post #1536804
Posted Friday, January 31, 2014 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536831
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse