May 2, 2002 at 12:06 pm
I am trying to speed up a query that has to convert a date in the group by section.
Sample query:
SELECT convert(varchar(7), count_date, 120) as cal_month
FROM DateListTable
WHERE convert(varchar(7), count_date, 120) is not null
GROUP BY convert(varchar(7), count_date, 120)
With the convert in the Group By section it take around 10 minutes, if I dont't convert (just use count_date) it takes under a minute. I have also tried to convert the date like this:
(year(count_date)*100 + month(count_date))
But no time change.
I need to be able to group by month. Any suggestions?
Thanks for any help,
Edward
May 2, 2002 at 1:01 pm
Won't say it will help but try
SELECT MONTH(count_date) as cal_month
FROM DateListTable
WHERE count_date is not null
GROUP BY MONTH(count_date)
Or DATEPART(m,count_date). Also is this column indexed? If not might bennefit from one but you would have to test.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 4:14 pm
You might try adding a computed column that is based on the convert and index it.
Andy
May 2, 2002 at 4:33 pm
Index on computed columns is only available in SQL 2000 but it does have some limitations see "Creating Indexes on Computed Columns" in 2000 BOL.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply