what''s a better practice when joining and aggregating...
-
for queries that do an aggregate and lots of joins, what's a better idiom:
1. wrap joined/selected columns in a min()
2. add joined/selected columsn to the group by
3. isloate the aggregation in a subquery and join to that.
EXAMPLE OF 1.:
select
min(S.BizSubBusinessUnitName) as BizSubBusinessUnitName,
min(BU.BizBusinessUnitName) as BizBusinessUnitName,
D.BizStrategyName,
I.SevernTicker,
min(I.SecurityName) as SecurityName,
min(I.CommonTicker) as CommonTicker,
min(IT.InstID) as InstID,
sum(P.Quantity) as SRTradeQuantity,
...
from
(select * from dbo.fnPositionMarkS(null)) P
join tbInstTranche IT WITH (NOLOCK) on IT.InstTrancheID = P.InstTrancheID
join tbBizStrategy D WITH (NOLOCK) on D.BizStrategyID = P.BizStrategyID
...
group by
D.BizStrategyName, I.SevernTicker
-
Do you have a specific case?
This is one of those "it always depends"
. -
no specific case in mind other than the abbreviated code I already posted.
Can you give an example of why you'd choose any one idiom over the other 2?
-
Frankly no... I usually try to find the fasted query for a situation... and often 2 versions can have the same performance. Maybe someone else might think of one.
-
Just be aware that when you do a MIN or MAX on two columns from the same table, you are probably NOT getting the values from the same row.
Sometimes I find that the best technique is to use a subquery for the aggregates, and joining that subquery using the GROUP BY columns to the outer table so I can see the same City, State, and Zip per table row as an example.
SELECT Customer.Name, Customer.City, Customer.State, Customer.Zip, Q.Payments
FROM Customer
INNER JOIN (SELECT SUM(Payment) AS Payments
FROM Customer
GROUP BY SSN) AS Q
ON Customer.SSN = Q.SSN
If you want a better example of your needs, provide a starting place, sample data, and expected output...
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply