Query Performance

Question

Query Performance

sean.alford

Newbie

Points: 3
More actions
November 13, 2014 at 8:56 am

#100571

I believe these two queries produce the same results. (They do with my current test data anyway.) I've simply re-factored the two DateTime statements in Query 1 to the outer most level in Query 2. I'm thinking Query 2 may be more efficient, but wanted to run it by the SQL Gurus to make sure I'm not overlooking anything.
/* Query 1 */
SELECT TBL_1.Station_ID, TBL_1.DateTime, TBL_1.MMDDYY, TBL_1.Input_1_Adjusted, TBL_1.Input_2_Adjusted, TBL_2.Input_1_Daily_Total, TBL_2.Input_2_Daily_Total
FROM
(
SELECT Station_ID, DateTime, convert(date, dateadd(hour,-9,datetime)) as MMDDYY, Input_1_Adjusted, Input_2_Adjusted
FROM tblHistoryHourlyAdjustedNWE
WHERE (
(Station_ID = 7) AND
(DateTime Between '11-01-2014 09:00:00' AND '12-01-2014 08:59:59') AND
(DatePart(hh,DateTime) = 9)
)
) AS TBL_1,
(
SELECT Station_ID, convert(date, dateadd(hour,-9,datetime)) as MMDDYY, sum(Input_1_Delta) as Input_1_Daily_Total, sum(Input_2_Delta) as Input_2_Daily_Total
FROM tblHistoryHourlyAdjustedNWE
WHERE (
(Station_ID = 7) AND
(DateTime Between '11-01-2014 09:00:00' AND '12-01-2014 08:59:59')
)
GROUP BY Station_ID, convert(date, dateadd(hour,-9,datetime))
) AS TBL_2
WHERE TBL_1.MMDDYY = TBL_2.MMDDYY
/* Query 2 */
SELECT TBL_1.Station_ID, TBL_1.DateTime, TBL_1.MMDDYY, TBL_1.Input_1_Adjusted, TBL_1.Input_2_Adjusted, TBL_2.Input_1_Daily_Total, TBL_2.Input_2_Daily_Total
FROM
(
SELECT Station_ID, DateTime, convert(date, dateadd(hour,-9,datetime)) as MMDDYY, Input_1_Adjusted, Input_2_Adjusted
FROM tblHistoryHourlyAdjustedNWE
WHERE (
(Station_ID = 7) AND
(DatePart(hh,DateTime) = 9)
)
) AS TBL_1,
(
SELECT Station_ID, convert(date, dateadd(hour,-9,datetime)) as MMDDYY, sum(Input_1_Delta) as Input_1_Daily_Total, sum(Input_2_Delta) as Input_2_Daily_Total
FROM tblHistoryHourlyAdjustedNWE
WHERE (
(Station_ID = 7)
)
GROUP BY Station_ID, convert(date, dateadd(hour,-9,datetime))
) AS TBL_2
WHERE TBL_1.MMDDYY = TBL_2.MMDDYY AND
(TBL_1.DateTime Between '11-01-2014 09:00:00' AND '12-01-2014 08:59:59')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick thought, in order to provide meaningful answer to your question, some more information is required. Could you post DDL, sample data and preferably the actual execution plans for both queries?

😎

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

I'm almost sure you'll obtain a performance gain if you could put the data of the selects for TBL_1 and TBL_2 in temporary table(s) and re-write the queries. Additionally you can put indexes on the temp table.

Igor Micev,My blog: www.igormicev.com

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

For starters, both queries miss nearly a full second of the day identified in the WHERE clause. Use the following, instead.

(TBL_1.DateTime >= '11-01-2014 09:00:00' AND TBL_1.DateTime < '12-01-2014 09:00:00)

The other thing is that while just looking at code can sometimes reveal which will be faster, such looks are frequently wrong. The best way to find out is to do a test. Provided that the code has no scalar or multi-statement table valued functions in it, a quick check by adding the following code can be of real help especially when analyzed in conjunction with the actual execution plan.

SET STATISTICS TIME, IO ON;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)