October 12, 2011 at 2:10 am
using this query, is there an alternative approach to achieve my goal? or im doing the right approach by using dateadd to get the data 10 days before?
CREATE TABLE #temptable
(
ddate datetime
)
INSERT INTO #temptable(ddate) VALUES ('2011-10-12 15:57:29.873')
INSERT INTO #temptable(ddate) VALUES ('2011-10-01 15:57:29.873')
INSERT INTO #temptable(ddate) VALUES ('2011-10-02 15:57:29.873')
INSERT INTO #temptable(ddate) VALUES ('2011-10-03 15:57:29.873')
INSERT INTO #temptable(ddate) VALUES ('2011-10-04 15:57:29.873')
SELECT * FROM #temptable
WHERE CONVERT(varchar(10),ddate,101) = DATEADD(dd,-10,CONVERT(varchar(10),getdate(),101))
October 12, 2011 at 2:19 am
This was removed by the editor as SPAM
October 12, 2011 at 2:29 am
The theory of the post above is correct if you're using SQL 2008, but bear in mind that it's not functionally equivelent (add another row for "2011-10-03 00:00:00.000" to show this).
The functionally equivalent version would be:
SELECT * FROM #temptable
WHERE ddate >= CONVERT(DATE,DATEADD(dd,-10,getdate()) ) AND ddate< CONVERT(DATE,DATEADD(dd,-9,getdate()) )
Or alternatively, this is also SARGable:
SELECT * FROM #temptable
WHERE CONVERT(DATE,ddate) = DATEADD(dd,-10,CONVERT(DATE,GETDATE()))
October 12, 2011 at 2:57 am
i noticed you guys used a different condition statement by using BETWEEN condition and >= <
Is there a difference in performance? given the table has the right indexes.
October 12, 2011 at 3:14 am
Teemo (10/12/2011)
i noticed you guys used a different condition statement by using BETWEEN condition and >= <Is there a difference in performance? given the table has the right indexes.
Best way to check is to test 🙂
Here's a test environment for you to run: -
IF object_id('tempdb..#dateTest') IS NOT NULL
BEGIN
DROP TABLE #dateTest
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS OurID,
RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME) AS OurDate
INTO #dateTest
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT OurDate
FROM #dateTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== BETWEEN =========='
SET STATISTICS TIME ON
SELECT OurDate
FROM #dateTest WITH (NOLOCK)
WHERE OurDate BETWEEN CONVERT(DATE,DATEADD(dd,-10,getdate()) ) AND CONVERT(DATE,DATEADD(dd,-9,getdate()) )
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== >= < =========='
SET STATISTICS TIME ON
SELECT OurDate
FROM #dateTest WITH (NOLOCK)
WHERE OurDate >= CONVERT(DATE,DATEADD(dd,-10,getdate()) ) AND OurDate < CONVERT(DATE,DATEADD(dd,-9,getdate()) )
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== OtherSARG =========='
SET STATISTICS TIME ON
SELECT OurDate
FROM #dateTest WITH (NOLOCK)
WHERE CONVERT(DATE,OurDate) = DATEADD(dd,-10,CONVERT(DATE,GETDATE()))
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== Non-SARG =========='
SET STATISTICS TIME ON
SELECT OurDate
FROM #dateTest WITH (NOLOCK)
WHERE CONVERT(varchar(10),OurDate,101) = DATEADD(dd,-10,CONVERT(varchar(10),getdate(),101))
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
Here's the results on my box: -
========== BASELINE ==========
(1000000 row(s) affected)
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 3628 ms.
================================================================================
========== BETWEEN ==========
(127 row(s) affected)
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 144 ms.
================================================================================
========== >= < ==========
(127 row(s) affected)
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 143 ms.
================================================================================
========== OtherSARG ==========
(127 row(s) affected)
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 97 ms.
================================================================================
========== Non-SARG ==========
(127 row(s) affected)
SQL Server Execution Times:
CPU time = 797 ms, elapsed time = 818 ms.
================================================================================
October 12, 2011 at 4:14 am
that's great, thank you very much, thanks guys for all that information!
October 12, 2011 at 9:05 am
Speaking of huge data entity where it needs for more specific indexes of 2008 technologies , it is muhc better to use the query below:
SELECT * FROM #temptable
WHERE ddate >= CONVERT(DATE,DATEADD(dd,-10,getdate()) )
AND ddate< CONVERT(DATE,DATEADD(dd,-9,getdate()) )
Where < and > are applicable for that 2008 technology "index filter" meanwhile between is not applicable.
therefore, you have to change your codes accordingly to let query optimizer select that filtered index for its query exaction plan where significant performance revenue could be there
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 12, 2011 at 9:15 am
Performace Guard (Shehap) (10/12/2011)
Where < and > are applicable for that 2008 technology "index filter" meanwhile between is not applicable.therefore, you have to change your codes accordingly to let query optimizer select that filtered index for its query exaction plan where significant performance revenue could be there
Sorry, you've lost me. What have filtered indexes got to do with this?
October 12, 2011 at 9:19 am
Howardw, This is as generic concept for between and < / > for all queries regardless that query specifically
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 12, 2011 at 9:26 am
Performace Guard (Shehap) (10/12/2011)
Howardw, This is as generic concept for between and < / > for all queries regardless that query specifically
Ok, so I'm still not understanding what it is you're saying about the difference between > and < and BETWEEN (other than that between is equivalent to >= and <= rather than > and <)? Both are perfectly SARGable and I'm not aware of a difference in relation to Filtered Indexes?
October 12, 2011 at 10:08 am
Performace Guard (Shehap) (10/12/2011)
Speaking of huge data entity where it needs for more specific indexes of 2008 technologies , it is muhc better to use the query below:SELECT * FROM #temptable
WHERE ddate >= CONVERT(DATE,DATEADD(dd,-10,getdate()) )
AND ddate< CONVERT(DATE,DATEADD(dd,-9,getdate()) )
Where < and > are applicable for that 2008 technology "index filter" meanwhile between is not applicable.
therefore, you have to change your codes accordingly to let query optimizer select that filtered index for its query exaction plan where significant performance revenue could be there
My above test showed that "BETWEEN" (when written with the correct consideration) and ">= <" were interchangeable performance-wise. Granted I was only looking at 1 million rows, so maybe you've talking about a much larger data-set but somehow I think that the trend would be the same (I'm willing to set-up some tests on increasingly larger sets of dates if you wish though 🙂 ).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply