Post reply

Problem with date variables

  • 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!

  • February 28, 2014 at 11:05 pm

    #1693310

    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

  • March 1, 2014 at 7:39 pm

    #1693352

    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)
    Intro to Tally Tables and Functions

  • March 3, 2014 at 9:46 am

    #1693625

    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!

  • March 3, 2014 at 10:05 am

    #1693634

    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

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

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