November 12, 2002 at 7:01 am
Is there a way to speed up this query
SELECT Convert(char,Ai_InputDate,101) AS iDate, Count(tblAvisosInternet.Ai_Id) AS iAvisos
FROM tblAvisosInternet
GROUP BY Convert(char,Ai_InputDate,101)
What I need is the ability to count per input date (Ai_InputDate, smalldatetime) the classified ads I got in a table. So far the table is pretty small, about 150'000 rows, I have an index on the date but I reckon it doesn't help that much.
Jean-Luc
jeanluc@corobori.com
www.corobori.com
Jean-Luc
www.corobori.com
November 12, 2002 at 8:08 am
Have you checked the execution plan for this query.
November 12, 2002 at 8:30 am
quote:
Have you checked the execution plan for this query.
Yes but I don't understand what it means.
I was thinking if there was a way to have an index based on the actual date, not including the time as perhaps the convert consumes a lot time in the process.
Jean-Luc
Jean-Luc
www.corobori.com
November 12, 2002 at 10:12 am
create a computed column and then index that
i.e.
ALTER TABLE tblAvisosInternet ADD iDate AS Convert(char,Ai_InputDate,101)
CREATE INDEX I_tblAvisosInternet_iDate ON tblAvisosInternet (iDate)
Then USE
SELECT iDate, count(1) AS iAvisos
FROM tblAvisosInternet
GROUP BY iDate
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply