June 16, 2011 at 10:10 am
Really basic question here, more about preferences and reasons why.
I have a proc with several different queries in it. One of them is basically:
SELECT a whole buncha columns
FROM StagingTable st
...a few LEFT OUTER JOINs to different tables...
LEFT OUTER JOIN (Select ID, Sum(PCount) AS PCount
FROM RealTable
Where BillMonth < @BillMonth /*this bill month, FYI*/ AND PCount = 1
GROUP BY ID) rt1
ON st.ID = rt1.ID
LEFT OUTER JOIN (Select ID, Sum(CCount) AS CCount
FROM RealTable
Where BillMonth < @BillMonth /*this bill month, FYI*/ AND CCount = 1
GROUP BY ID) rt2
ON st.ID = rt2.ID
So I got cute and decided to test the last two tables as a CTE.
WITH MyCTE AS
(Select ID, Sum(CCount) AS CCount, Sum(PCount) AS PCount
FROM RealTable
Where BillMonth < @BillMonth /*this bill month, FYI*/ AND
(CCount = 1 OR PCount = 1)
GROUP BY ID)
SELECT a whole buncha columns
FROM StagingTable st
...a few LEFT OUTER JOINs to different tables...
LEFT OUTER JOIN MyCTE mc
ON st.ID = mc.ID
The time it takes for both queries to run is exactly 12 seconds. Not a heart breaker. The execution plans are different, but I don't see enough of a difference to worry about at the moment. The staging recordset is only going to get larger over time, possibly up into the millions in the next few years, but right now we're dealing with records in the thousands.
Since I'm cleaning, I'm trying to debate if I should change this one query or leave it as is. The things in the Execution Plans which are "bad" are Index Scans that I can't really fix (small, narrow tables) and a Table Scan of the StageTable because it's a heap.
So, generic thoughts? Should I or should I not use the CTE approach?
June 16, 2011 at 10:20 am
It's a style issue at that point.
If you do both sums in a single derived table in the From clause, instead of as a CTE or two derived tables, you'll get the same result as the CTE. If you do two CTEs, one for each sum, you'll get the same result as your current query.
Just two different ways to write the same thing.
You could also do both sums as inline sub-queries in the Select clause. SQL Server will probably treat that the same way, too. Usually does, anyway.
So, since they're all pretty much the same where it counts, in the query optimizer, pick whichever is most readable to you.
Personally, I like CTEs for this. I use them in other places where they're really the only option, so I use them where optional for consistency.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply