[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query Help - TOO Slow

Posted on 2008-11-10
9
Medium Priority
?
478 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Jason Jones
  • 4
  • 4
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22922248
Are there indexes on your date fields?  Also, you're joining on a date field returned from a subquery...which cannot be fast.
0
 
LVL 1

Author Comment

by:Jason Jones
ID: 22922323
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
 
LVL 1

Author Comment

by:Jason Jones
ID: 22922330
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22922350
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
 
LVL 7

Accepted Solution

by:
Cedric_D earned 1000 total points
ID: 22922402
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
 
LVL 1

Author Comment

by:Jason Jones
ID: 22922408
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1000 total points
ID: 22922469
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
 
LVL 1

Author Comment

by:Jason Jones
ID: 22922536
Gee guys both run 100 times faster...  which one should I use and award points to?

THanks!
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22922568
you can split them
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question