SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Comparing string with date


Comparing string with date

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216778 Visits: 41986
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. Blush

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lempster
Lempster
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5115 Visits: 1657
Jeff Moden (1/29/2014)

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


Could you post your solution anyway Jeff?

Regards
Lempster
andrew.edgar
andrew.edgar
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 46
..
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216778 Visits: 41986
Lempster (1/29/2014)
Jeff Moden (1/29/2014)

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


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lempster
Lempster
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5115 Visits: 1657
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.
Lempster
Lempster
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5115 Visits: 1657
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216778 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lempster
Lempster
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5115 Visits: 1657
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216778 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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