SQL Query Help - TOO Slow

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
LVL 1
Jason JonesGIS/APP DevAsked:
Who is Participating?
 
Cedric_DConnect With a Mentor Commented:
This should help:

execute:

create index ix1 on GWLMCD (DATE, UNID)

and rewrite query as:

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], DATE
                            FROM          GWLMCD) AS S ON S.UNID LIKE 'BUT00014' AND S.DATE = CONVERT(datetime, MakeDates_2.thedate)
GROUP BY MakeDates_2.thedate
ORDER BY MakeDates_2.thedate


on syntax error, you might need to enclode date column into brackets:  [date].

0
 
chapmandewCommented:
Are there indexes on your date fields?  Also, you're joining on a date field returned from a subquery...which cannot be fast.
0
 
Jason JonesGIS/APP DevAuthor Commented:
There is an index for the UNID and another for Date...  This same query runs on another database with about 30K records and is very fast,  I just modified it to pull the same data type from another database...  I am open to any suggestions as I gotta get this down to a few seconds at most.

Thanks
Jason
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jason JonesGIS/APP DevAuthor Commented:
Here is the Table info

CREATE TABLE [dbo].[GWLMCD](
      [UNID] [nvarchar](20) NULL,
      [WELL ID] [nvarchar](255) NULL,
      [DATE] [smalldatetime] NULL,
      [Depth Below Measuring Point] [float] NULL,
      [GroundWater Elevation] [float] NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [TIME] [nvarchar](20) NULL,
      [Depth Below Ground Surface] [float] NULL,
      [Reference Elevation] [nvarchar](50) NULL,
      [TEMP] [float] NULL
0
 
chapmandewCommented:
GWLMCD Is the table w/ the million records?

it is this statement that is absolutely killing you:

LEFT OUTER JOIN
                          (SELECT     UNID, TEMP, [Depth Below Measuring Point], CAST(CONVERT(varchar(10), DATE, 120) AS datetime) AS DATETIME2
                            FROM          GWLMCD)
0
 
Jason JonesGIS/APP DevAuthor Commented:
Ye it is...

I am not a SQL Guru, I just took a query that I knew was working and changed it for this table..  if you could just copy and paste a solution that would really help me I have no idea where to start with this....

Thanks
Jason
0
 
chapmandewConnect With a Mentor Commented:
try this:

SELECT     UNID, TEMP, [Depth Below Measuring Point], DATE INTO #temp
FROM          GWLMCD
WHERE UNID = 'BUT00014'

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     * FROM #temp ) AS S ON S.DATE = CONVERT(datetime, MakeDates_2.thedate)
GROUP BY MakeDates_2.thedate
ORDER BY MakeDates_2.thedate
0
 
Jason JonesGIS/APP DevAuthor Commented:
Gee guys both run 100 times faster...  which one should I use and award points to?

THanks!
0
 
chapmandewCommented:
you can split them
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.