Problem with date variables

Question

Post reply

Problem with date variables

brickpack

SSCrazy

Points: 2702
More actions
February 28, 2014 at 6:58 pm

#396353

The table in question has ~100 million rows and an index on the Date column. For some reason...my date variables don't seem to work in the following query.
This query spins and never returns a result set...
DECLARE @yesterday DATETIME, @today DATETIME
SET @yesterday = (SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1)
SET @today = (SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0))
SELECT @yesterday as Date, Username, SUBSTRING(username,PATINDEX('%@%', username) + 1,LEN(username)) AS Domain,
MAX(called_station_id) AS Number, SUM(acct_session_time)/3600.00 AS Hours
FROM
WHERE Date between @yesterday AND @today
GROUP BY username
But if I add the actual date string the result set returns in ~40 seconds...
SELECT '2014-02-27' as Date, Username, SUBSTRING(username,PATINDEX('%@%', username) + 1,LEN(username)) AS Domain,
MAX(called_station_id) AS Number, SUM(time)/3600.00 AS Hours
FROM
WHERE Date between '2014-02-27' AND '2014-02-28'
GROUP BY username
What am I missing?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

What's the execution plan look like? It'd be great if you could post that and your index script.

My guess is you have an index scan on the date index and a key lookup against the clustered index on

...or have an implicit conversion from the date to datetime going on.

100 million rows is a sizeable table. Without seeing your execution plan or knowing exactly what your index looks like, I'd recommend:

1. Because you said it's quick when you type in the actual date (notice you are not including the '00:00:00:000' - try Changing your @yesterday and @today variables to date to match the data type that's stored in the

... Ex: DECLARE @yesterday DATE, @today DATE

SELECT @yesterday = GETDATE()-1, @today = GETDATE()

2. Create a filtered index for the current year, past x months, or past year (this should significantly reduce your index size and number of logical reads) - Ex: CREATE NONCLUSTERED INDEX fidx_Dates ON dbo.[Table]

[Date] ASC

INCLUDE (Username, call_station_id, acct_session_time)

WHERE [Date] >= '2014-01-01'

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

What is the datatype of the DATE column?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

brickpack SSCrazy Points: 2702 More actions · Answer 3

Turns out...all I had to do was add "Username" to the included columns on the date index. Using the variables works now...but it's still slow. It takes ~2.2 mins to return the results that the other query returns in 40 secs. Oh well...I can live with that.

Thanks for the input guys!

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

So the 2nd point I mentioned worked for you (great!). If you post your execution plan, we can probably get that thing to run in seconds...I'm still thinking there's a keylookup in there somewhere due to the date/datetime conversion or something similar.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience