Date range question. 2

[sparkbyte]

Which query would be better??

Select Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
FROM tblTrackingTable

WHERE     (TrackingDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0) AND CURRENT_TIMESTAMP)

AND			FileNumber NOT LIKE '.box.end.'

ORDER BY	TrackingDate DESC

Number of SELECT statements 1 1.0000
Rows returned by SELECT statements 107 107.0000
Number of server roundtrips 1 1.0000
Bytes sent from client 600 600.0000
Bytes received from server 13310 13310.0000
Client processing time 31 31.0000
Total execution time 1546 1546.0000
Wait time on server replies 1515 1515.0000

OR

DECLARE @Today DATETIME
SET @Today = GETDATE()

-- Remove the time element
SET  @Today = DATEDIFF(DAY, 0, @Today)

Select 
	  Tracking_ID
	, EmployeeID
	, MachineName
	, BoxNumber
	, FileNumber
	, TrackingDate
FROM	tblTrackingTable
WHERE	TrackingDate >= @Today
AND		TrackingDate < @Today + 1
AND		FileNumber NOT LIKE '.box.end.'
ORDER BY	TrackingDate

Number of SELECT statements 4 4.0000
Rows returned by SELECT statements 110 110.0000
Number of server roundtrips 3 3.0000
Bytes sent from client 914 914.0000
Bytes received from server 31722 31722.0000
Client processing time 32 32.0000
Total execution time 1578 1578.0000
Wait time on server replies 1546 1546.0000

This table will grow on average of 10k to 20k rows per day.


Thanks

John Fuhrman

 

[Crowley16]

hmm, hard to say really...

I suspect the first one would be better since it's more likely to hit the index, but it depends on the query optimiser which can act in unexpected ways.

If this is a regular run query, and isn't likely to change often, then you can always put in a index hint to always force the usage of the date index. That would pretty much eliminate any non-trivial differences between the 2...

your best bet would be to look at the execution plans of both and see what sort of seeks/scans are used...

--------------------
Procrastinate Now!

 

[markros]

These two queries are not identical. Based on the output, though, I can say, that the first query is better.

Though I don't see that much difference between the two except for the fact that the last one is supposed to return more records that the first (since the first only returns records up to current time and the second all records for the day).

PluralSight Learning Library

 

[sparkbyte]

Thanks Mark.

I missed the fact that I still had current_timestamp in the 1st query.

The second query (said) it also accounted for miliseconds. Not sure if this would make a difference or not.

THAKS

Thanks

John Fuhrman

 

[markros]

With date ranges I always use the second variation of the query. I never use BETWEEN with dates after reading Aaron Bertrand blog.

PluralSight Learning Library

 

[Crowley16]

do you have a link to that blog markros?

--------------------
Procrastinate Now!

 

[r937]

i like the first one because the second one won't work

you're setting @Today to an integer, i.e. the number of days produced by the DATEDIFF

then you use this in your WHERE clause to compare to a datetime

i'm surprised you got any rows back at all



now, even if you were to rewrite the second one to fix this error, it still won't return the same rows as the first query, which only goes up to the current datetime, whereas, as markros pointed out, the second query goes right up until midnight tonight

finally, the second query presents the data in reverse order from the first

if you fix all these differences, is there still a question here?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Crowley16]

r937, sql automatically converts that back to a datetime, hence the query works fine...

--------------------
Procrastinate Now!

 

[r937]

thanks, you're right, i shoulda tested it

pretty obscure behaviour, though

i would prefer to see this --

SET @Today = DATEADD(DAY,DATEDIFF(DAY,0,@Today),0)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Crowley16]

it makes sense when you take into account that sql server stores datetime as a decimal number, the bit before the decimal point is the date, starting from 1900-01-01 and the bit after the decimal is the time, in microseconds, from 00:00:00.000.

hence, passing in a integer is just like passing in a date and no actual conversion of any sort is required. I use this sort of behaviour quite common when wanting just a date or time figure...

also explains why you can do @date - 1 instead of using dateadd...

--------------------
Procrastinate Now!

 

[gmmastros]

DateTime is actually stored as a pair of integers. It often helps to think of it as a decimal number, but it's not actually stored that way.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server

I recall reading somewhere that the "lazy" way of dealing with dates no longer works when you use the date data type in SQL2008. You need to use dateadd & datediff instead of relying on SQL Server to automatically convert numbers to dates and vice versa. I haven't tested this because I don't have a sql2008 instance installed anywhere.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

datetime still works, however the new date datatype doesn't...

--------------------
Procrastinate Now!

 

[sparkbyte]

OK, just to fix query 1 for comparison

Query 1

Select Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
FROM tblTrackingTable

WHERE     (TrackingDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0) AND DateAdd(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1)))

AND			FileNumber NOT LIKE '.box.end.'

ORDER BY	TrackingDate DESC

(1103 row(s) affected)

Query 2

DECLARE @Today DATETIME
SET @Today = GETDATE()

-- Remove the time element
SET  @Today = DATEDIFF(DAY, 0, @Today)

Select 
	  Tracking_ID
	, EmployeeID
	, MachineName
	, BoxNumber
	, FileNumber
	, TrackingDate
FROM	tblTrackingTable
WHERE	TrackingDate >= @Today
AND		TrackingDate < @Today + 1
AND		FileNumber NOT LIKE '.box.end.'
ORDER BY	TrackingDate

(1103 row(s) affected)

[red]The question was which query would be better.[/red]

The 1st compares the 2 dates using a BETWEEN.

The 2nd converts the date to an int (DateDiff) the uses
>= < to capture everything between the 2 dates.

Query 1 - Execution Plan
Table Scan 93% - Parallelism (gather streams) 7% - Sort 0% - Select 0%

Query 2 - Execution Plan (3 selects)

Select 1
Assign 0%

Select 2
Assign 0%

Select 3
Table Scan 71% - Sort 23% - Parallelism (gather streams) 6% - Select 0%


OK here is the curiosity part.

Trial 1 Average
Client Execution Time 11:04:08
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements 0 0.0000
Rows affected by INSERT, DELETE, or UPDATE statem... 0 0.0000
Number of SELECT statements 2 2.0000
[highlight]Rows returned by SELECT statements 1104 1104.0000[/highlight]
Number of transactions 0 0.0000
Network Statistics
Number of server roundtrips 3 3.0000
TDS packets sent from client 3 3.0000
TDS packets received from server 40 40.0000
Bytes sent from client 842 842.0000
Bytes received from server 154196 154196.0000
Time Statistics
Client processing time 610 610.0000
Total execution time 2125 2125.0000
Wait time on server replies 1515 1515.0000

Trial 1 Average
Client Execution Time 11:03:59
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements 0 0.0000
Rows affected by INSERT, DELETE, or UPDATE statem... 0 0.0000
Number of SELECT statements 4 4.0000
[highlight]Rows returned by SELECT statements 1106 1106.0000[/highlight]

Number of transactions 0 0.0000
Network Statistics
Number of server roundtrips 3 3.0000
TDS packets sent from client 3 3.0000
TDS packets received from server 40 40.0000
Bytes sent from client 918 918.0000
Bytes received from server 153506 153506.0000
Time Statistics
Client processing time 16 16.0000
Total execution time 1531 1531.0000
Wait time on server replies 1515 1515.0000

As you can see the selected rows are 2 higher in Query 2 but the Rows affected are the same for both queries.

Hmmm....

Thanks

John Fuhrman

 

[gmmastros]

The difference between setting an @Today variable vs. using it directly is going to be negligible. More disturbing to me.... why do you have a table scan in your execution plan? Table scan only occur if you do not have a primary key on the table. With a primary key on the table, you may see a "clustered index scan", but not a table scan.

What I am saying is.... indexes are more important than the code in this situation.

To see what indexes you have on this table:

[tt][blue]
sp_helpindex 'tblTrackingTable'
[/blue][/tt]

In this case, an index that has TrackingDate as the first column will give you a performance increase. There may be other ways to improve this with indexes too. Specifically, a covering index may give you the best performance. With an index on TrackingDate, your execution plan will likely show an index seek and a bookmark lookup. This will be many times faster than not having an index with TrackingDate as the first column.

If the bookmark lookup is taking the majority of the time, and you still want to make the performance better, then you could try creating an covering index. In this case, the covering index would like have TrackingDate and FileNumber as part of the index with Tracking_ID, EmployeeID, MachineName, BoxNumber as included column. If you are using sql2000, you cannot use included columns so all of the columns would need to be part of the index.

If you're not sure about all of this, post the output to the sp_helpindex that I showed earlier.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah, one more thing...

As you can see the selected rows are 2 higher in Query 2 but the Rows affected are the same for both queries.

That happens because you are selecting 2 additional rows, but not returning the output of the selects. Here:

DECLARE @Today DATETIME
SET @Today = GETDATE()

-- Remove the time element
SET  @Today = DATEDIFF(DAY, 0, @Today)

Each set adds a row to the 'selects' line. You can reduce this by one select with this:

DECLARE @Today DATETIME
SET  @Today = DATEDIFF(DAY, 0, GetDate())

I doubt this will have much (if any) performance implications.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Thanks George!!

index_name index_description index_keys
1 aaaaaTrackingTable_PK nonclustered, unique, primary key located on PRIMARY Tracking_ID
2 Key nonclustered located on PRIMARY Tracking_ID
3 NetworkLogonID nonclustered located on PRIMARY EmployeeID

Thanks

John Fuhrman

 

[gmmastros]

Just for fun, try this....

1. Run the code to see how long it takes.
2. Add the following index.
3. run the code again to see how long it takes.

Create Index idx_tblTrackingTable_TrackingDate 
On tblTrackingTable(TrackingDate)

If the index does not seem to help, then you should remove it with this:

Drop Index tblTrackingTable.idx_tblTrackingTable_TrackingDate

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

That was noticably faster.

Time Statistics
Client processing time 31 31.0000
Total execution time 46 46.0000
Wait time on server replies 15 15.0000

vs.

Time Statistics
Client processing time 16 16.0000
Total execution time 1531 1531.0000
Wait time on server replies 1515 1515.0000


Thanks

John Fuhrman

 

[markros]

chrowley, sorry, didn't check this forum till now.

Take a look at

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/series-of-bad-habits-to-kick-by-aaron-be

it lists all the blogs in that series by Aaron. I honestly read just a few and the first one is my favorite.

PluralSight Learning Library

 

[markros]

George. I know where did you read about lazy way - on your favorite website. Search by "date clash".

BTW, what do you think about this date arithmetics?

http://forums.asp.net/p/1554442/3818880.aspx#3818880

PluralSight Learning Library