May 17, 2005 at 5:00 am
Hi, I have query that takes most of the time in my Stored Procedure, I am wondering if there is any better way of writing it to get the desired results much faster, its basically intended at retreiving the number of transactions group by year, month and day. from a single table. I have non clustered index on my date field.
SELECT COUNT(pos) as number,
YEAR(po_date ) AS y,
MONTH(po_date ) AS m,
DAY(po_date ) AS d
FROM
my_pos
WHERE
DATEADD(ss,-14400,PO_DATE) >= '01/01/2005'
AND
DATEADD(ss,-14400,PO_DATE) <= '01/02/2005'
GROUP BY
YEAR(po_date),
MONTH(po_date),
DAY(po_date)
ORDER BY
y,
m,
d
Thanks
Prasad Bhogadi
www.inforaise.com
May 17, 2005 at 7:40 am
WHERE
DATEADD(ss,-14400,PO_DATE) >= '01/01/2005'
AND
DATEADD(ss,-14400,PO_DATE) <= '01/02/2005'
GROUP BY
With what i understand, the conditions above won't filter any dates. You can just get rid of the conditions above.
May 17, 2005 at 8:24 am
The dates mentioned in the example are just for example and the period is dynamically picked and it as has the time concatenated to it.
Prasad Bhogadi
www.inforaise.com
May 23, 2005 at 1:40 pm
You should remove the DATEADD statements from the column side the statement. The server can't use the indexes properly if you run a calculation on the indexed value. Try something like this:
WHERE
PO_DATE >= DATEADD(ss,14400,'01/01/2005')
AND
PO_DATE <= DATEADD(ss,14400,'01/02/2005')
GROUP BY
-Dan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply