Better query to accomplish this?
-
I've run into something that's a bit more advanced than I am, and the only way I know how to accomplish the desired result is through a sort of "brute force" query. Can someone help me figure out how to do this more elegantly, and more importantly, with better performance?
Here's the format of my table.
Here's the format that I'd like to return for a report.
The query I'm using to accomplish this is below. It's ridiculously slow.
SELECT r1.record_date as [Date], r1.hour as [Hour], r1.kwh as [R1], r2.kwh as [R2], r3.kwh as [R3],
w1.kwh as [W1], w2.kwh as [W2], w3.kwh as [W3],
v1.kwh as [V1], v2.kwh as [V2], v3.kwh as [V3]
FROM totals r1
INNER JOIN totals r2
ON r1.record_date = r2.record_date and r1.hour = r2.hour
INNER JOIN totals r3
ON r1.record_date = r3.record_date and r1.hour = r3.hour
INNER JOIN totals w1
ON r1.record_date = w1.record_date and r1.hour = w1.hour
INNER JOIN totals w2
ON r1.record_date = w2.record_date and r1.hour = w2.hour
INNER JOIN totals w3
ON r1.record_date = w3.record_date and r1.hour = w3.hour
INNER JOIN totals v1
ON r1.record_date = v1.record_date and r1.hour = v1.hour
INNER JOIN totals v2
ON r1.record_date = v2.record_date and r1.hour = v2.hour
INNER JOIN totals v3
ON r1.record_date = v3.record_date and r1.hour = v3.hour
WHERE r1.feeder='r1' and r2.feeder='r2' and r3.feeder='r3'
and w1.feeder='w1' and w2.feeder='w2' and w3.feeder='w3'
and v1.feeder='v1' and v2.feeder='v2' and v3.feeder='v3'
ORDER BY r1.record_date, r1.hour
There must be a better way. Any thoughts?
Thanks!
-
Yes. Try the methods in the following article.
http://www.sqlservercentral.com/articles/Crosstab/65048/ (Dynamic CrossTabs).
--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) -
I appreciate the help, but I don't understand how to implement that example. I guess I'll just live with a slow query.
-
I spent some more time with your tutorial and finally figured out a solution. Thanks very much for your help. Much more readable and much better performance.
-
Jeff Teel (9/17/2016)
I spent some more time with your tutorial and finally figured out a solution. Thanks very much for your help. Much more readable and much better performance.Very cool. Can you post the code you ended up with? I'd be happy to "peer review" it for you to see if there are any addition improvements that might be able to be made.
--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) -
Ok, here's what I used. I didn't use your "pre-aggregation" recommendation because I still don't really understand that part, and because the performance from doing just this alone made such a drastic improvement. I'm certainly open to other ideas though.
SELECT CASE WHEN GROUPING(hour) = 1 THEN 'Totals' ELSE FORMAT(hour * 100,'00:00') END AS
[Hour],
SUM(CASE WHEN feeder = 'r1' THEN kwh ELSE 0 END) AS R1,
SUM(CASE WHEN feeder = 'r2' THEN kwh ELSE 0 END) AS R2,
SUM(CASE WHEN feeder = 'r3' THEN kwh ELSE 0 END) AS R3,
SUM(CASE WHEN feeder = 'w1' THEN kwh ELSE 0 END) AS W1,
SUM(CASE WHEN feeder = 'w2' THEN kwh ELSE 0 END) AS W2,
SUM(CASE WHEN feeder = 'w3' THEN kwh ELSE 0 END) AS W3,
SUM(CASE WHEN feeder = 'v1' THEN kwh ELSE 0 END) AS V1,
SUM(CASE WHEN feeder = 'v2' THEN kwh ELSE 0 END) AS V2,
SUM(CASE WHEN feeder = 'v3' THEN kwh ELSE 0 END) AS V3,
SUM(kwh) AS Totals
FROM totals
WHERE record_date = '{Desired Report Date}'
GROUP BY hour WITH ROLLUP
ORDER BY hour
-
That looks just fine, Jeff. The "pre-aggregation" method would add nothing here because each feeder only has one entry per hour (according to the data you've shown in your original post).
As a bit of a sidebar, thanks for taking the time to dig into those two articles. A lot of people would rather have a solution provided on a silver platter rather than taking the time to teach themselves something new and then have great success at it. Well done and thank you for the feedback.
EDIT: I missed the fact that you used FORMAT instead of CONVERT. It won't make much of a difference here because of the extremely low row count of how it's being used here BUT, be advised that FORMAT is 44 times slower than CONVERT. I'd recommend just staying away from FORMAT altogether. It's not worth getting into the bad habit of using it in it's current manifestation (or, infestation, if you prefer :-D)
--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)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply