May 17, 2005 at 5:14 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
I posted this in Suggestions Forum by misktake Please ignore the same at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=182884
Thanks
Prasad Bhogadi
www.inforaise.com
May 17, 2005 at 5:23 am
You could try count(*) instead of count(pos) if that column doesn't allow nulls.. This will allow the server to simply do an index seek on the po_date.
Also I would use the date add functions on the constant part of the between operation (DATEADD(ss,14400,'01/01/2005')) so that the index seek can be used instead of the index scan.
May 17, 2005 at 10:10 am
Paste your query into Query Analyzer and hit CTRL + I
This will launch the indexing wizzard. Follow the steps and let it add indexes to your table
This should increase the speed in which your query will execute.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply