How can I optimize a SQL query that performs a count nested inside a group-by clause?

I have a charting application that dynamically generates SQL Server queries to compute values for each series on a given chart. This generally works quite well, but I have run into a particular situation in which the generated query is very slow. The query looks like this:

SELECT 
  [dateExpr] AS domainValue,
  (SELECT COUNT(*) FROM table1 WHERE [dateExpr]=[dateExpr(maintable)] AND column2='A') AS series1

FROM table1 maintable
GROUP BY [dateExpr]
ORDER BY domainValue

I have abbreviated [dateExpr] because it's a combination of CAST and DATEPART functions that convert a datetime field to a string in the form of 'yyyy-MM-dd' so that I can easily group by all values in a calendar day. The query above returns both those yyyy-MM-dd values as labels for the x-axis of the chart and the values from the data series "series1" to display on the chart. The data series is supposed to count the number of records that fall into that calendar day that also contain a certain value in [column2]. The "[dateExpr]=[dateExpr(maintable)]" expression looks like this:

CAST(DATEPART(YEAR,dateCol) AS VARCHAR)+'-'+CAST(DATEPART(MONTH,dateCol) AS VARCHAR) = 
CAST(DATEPART(YEAR,maintable.dateCol) AS VARCHAR)+'-'+CAST(DATEPART(MONTH,maintable.dateCol) AS VARCHAR)

with an additional term for the day (ommitted above for the sake of space). That is the source of the slowness of the query, but I don't know how to rewrite the query so that it returns the same result more efficiently. I have complete control over the generatio开发者_Go百科n of the query, so if I could find more efficient SQL that returned the same results, I could modify the query generator appropriately. Any pointers would be greatly appreciated.

I havent tested but i think it can be done by:

SELECT 
  [dateExpr] AS domainValue,
  SUM (CASE WHEN  column2='A' THEN 1 ELSE 0 END) AS series1

FROM table1 maintable
GROUP BY [dateExpr]
ORDER BY domainValue

The fastest way to do this would be to use calendar tables. Create a sql table with an entry for every month for next who knows how many years. Then select from that calendar table, joining in the entries from table1 that have dates between the start and end date for the month. Then, if your clustered index is on the dateCol in table1, the query will run very quickly.

EDIT: Example Query. This assumes a months table exists with two columns, StartDate and EndDate where EndDate is the midnight on the first day of the next month. The clustered index on the months table should be on StartDate

SELECT
    months.StartDate,
    COUNT(*) AS [Count]
FROM months
INNER JOIN table1
    ON table1.dateCol >= months.StartDate AND table1.dateCol < months.EndDate
GROUP BY months.StartDate;

With Calendar As
    (
    Select DateAdd(d, DateDiff(d, 0, Min( dateCol ) ), 0) As [date]
    From Table1
    Union All
    Select DateAdd(d, 1, [date])
    From Calendar
    Where [date] <= (
                    Select Max( DateAdd(d, DateDiff(d, 0, dateCol) + 1, 0) )
                    From Table1
                    )
    )
Select C.date, Count(Table1.PK) As Total
From Calendar As C
        Left Join Table1
            On Table1.dateCol >= C.date
                And Table1.dateCol < DateAdd(d, 1, C.date )
                And Table1.column2 = 'A'
Group By C.date
Option (Maxrecursion 0);

Rather than try to force the display format in SQL, you should do that in your report or chart generator. However, what you can do in the SQL is to strip the time portion from the datetime values as I've done in my solution.