I have the following SQL statement that takes nearly 1 minute to execute on a Windows 2000 SQL server. The database have over a million records, but this query only pulls about 630 of them, but takes way too long. IT basically takes returns a set of daily records temperature measurements and a depth of a particular UNID. One day could have several readings hence the AVG and some days may be NULL where a measurement wasn't done.. so it makes the dates, joins them and displays the records..
SELECT CONVERT(varchar(6), MakeDates_2.thedate, 101) + CONVERT(varchar(4), MakeDates_2.thedate, 120) AS DateTime, 1.8 * CAST(AVG(S.TEMP) + 32 AS int) AS TEMP,
ROUND(AVG(S.[Depth Below Measuring Point]), 3) AS [Depth Below Measuring Point]
FROM dbo.MakeDates('2/2/2007', '10/19/2008') AS MakeDates_2 LEFT OUTER JOIN
(SELECT UNID, TEMP, [Depth Below Measuring Point], CAST(CONVERT(varchar(10), DATE, 120) AS datetime) AS DATETIME2
FROM GWLMCD) AS S ON S.UNID LIKE 'BUT00014' AND S.DATETIME2 = MakeDates_2.thedate
GROUP BY MakeDates_2.thedate
ORDER BY MakeDates_2.thedate