pzozulka
asked on
SQL: Union
Is there a better way of doing the below? The first SELECT gets all payments, so all values are positive. The next SELECT statement gets all adjustments, so all values are negative. Together, they are my derived table (JOIN) to get total customer payments (payments - adjustments).
PartyId Amount
130528 4063.26
130528 -200.00
So I would probably need to drop the above code down a level into a derived table -- call it A. Then, SELECT a SUM of Amount FROM table A, and then group by PartyId. This will be derived table B.
And then from the main select clause, select the amount from table B.
Is there a better strategy?
SELECT T.PartyId, SUM(T.Amount) as 'Amount'
FROM [Transaction] T
WHERE T.PaymentTypeId = 1
GROUP BY T.PartyId
UNION
SELECT Tr.PartyId, SUM(Tr.Amount) as 'Amount'
FROM [Transaction] Tr JOIN [Transaction] T ON Tr.AdjustsTransactionId = T.TransactionId
WHERE Tr.PaymentTypeId = 9 AND
T.PaymentTypeId = 1
GROUP BY Tr.PartyId
Results might look like this:PartyId Amount
130528 4063.26
130528 -200.00
So I would probably need to drop the above code down a level into a derived table -- call it A. Then, SELECT a SUM of Amount FROM table A, and then group by PartyId. This will be derived table B.
And then from the main select clause, select the amount from table B.
Is there a better strategy?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Interesting...when I ran both queries with "Include Actual Execution Plan", Scott's solution took 59% of the batch, while the UNION took 41%.
If you view Estimated Execution plan, then they say both 50%.
If you view Estimated Execution plan, then they say both 50%.
ASKER
What's more interesting is that I have to do this twice (two derived tables, in other words, two left joins) because the report requires two different columns, that have slightly different criteria.
This code, take 61% of batch as compared to same criteria using UNION ALL:
While this code, takes 50% of batch:
This code, take 61% of batch as compared to same criteria using UNION ALL:
SELECT T.PartyId, SUM(T.Amount + ISNULL(T9.Amount, 0)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
T9.PaymentTypeId = 9 AND
T9.AdjustsTransactionId = T.TransactionId
WHERE
T.PaymentTypeId = 1
GROUP BY T.PartyId
While this code, takes 50% of batch:
SELECT T.PartyId, SUM(T.Amount + ISNULL(T9.Amount, 0)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
T9.PaymentTypeId = 9 AND
T9.AdjustsTransactionId = T.TransactionId
WHERE
T.PaymentTypeId = 3 AND T.PaymentMethodId = 1
GROUP BY T.PartyId
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Scott, do I have to SET these values to OFF after I run the comparison or do they automatically turn off for future queries?
ASKER
OK, so as suggested the stats are below:
UNION query: CPU time = 156 ms, elapsed time = 239 ms, logical reads = 13651, physical reads = 0
JOIN query: CPU time = 187 ms, elapsed time = 269 ms, logical reads = 10938, physical reads = 0
UNION query: CPU time = 156 ms, elapsed time = 239 ms, logical reads = 13651, physical reads = 0
JOIN query: CPU time = 187 ms, elapsed time = 269 ms, logical reads = 10938, physical reads = 0
ASKER
So based on what you said, "look at logical I/Os only", it would seem that the JOIN query has better performance, even though the elapsed time is longer?
These stats don't make sense to me. You would think that the less logical reads, the smaller the elapsed time?
These stats don't make sense to me. You would think that the less logical reads, the smaller the elapsed time?
You would indeed think that. But remember, we're dealing with 30ms or less, very likely across hyper-threaded CPUs, so that is negligible.
I'm surprised the I/O is that close, though, only 25% more. Be sure you're not comparing a "double lookup" in the JOIN:
WHERE
T.PaymentTypeId = 3 AND T.PaymentMethodId = 1
to a single lookup in the UNION ALL.
I'm surprised the I/O is that close, though, only 25% more. Be sure you're not comparing a "double lookup" in the JOIN:
WHERE
T.PaymentTypeId = 3 AND T.PaymentMethodId = 1
to a single lookup in the UNION ALL.
select PartyId,
sum(case when PaymentTypeId = 1 then Amount else 0 end) -
sum(case when PaymentTypeId = 9 then Amount else 0 end) as Amount
from Transaction
group by PartyId;
sum(case when PaymentTypeId = 1 then Amount else 0 end) -
sum(case when PaymentTypeId = 9 then Amount else 0 end) as Amount
from Transaction
group by PartyId;
>>Results might look like this:
PartyId Amount
130528 4063.26
130528 -200.00<<
In my above query, the results would look like this:
PartyId Amount
130528 3863.26
To get your results -
select PartyId,
sum(case when PaymentTypeId = 1 then Amount else 0 end) Amount
from Transaction
group by PartyId
union all
select PartyId,
sum(case when PaymentTypeId = 9 then Amount else 0 end) * -1 as Amount
from Transaction
group by PartyId;
PartyId Amount
130528 4063.26
130528 -200.00<<
In my above query, the results would look like this:
PartyId Amount
130528 3863.26
To get your results -
select PartyId,
sum(case when PaymentTypeId = 1 then Amount else 0 end) Amount
from Transaction
group by PartyId
union all
select PartyId,
sum(case when PaymentTypeId = 9 then Amount else 0 end) * -1 as Amount
from Transaction
group by PartyId;
ASKER
awking00: I don't think this would work for me. Notice the below line of code from Scott's query:
T9.AdjustsTransactionId = T.TransactionId
This ensures that all PaymentTypeId that equal 9 (Adjustments) are related to the original transactionId.
I can't just say ALL transactions of PaymentTypeId = 9 because then it will find unrelated transactions as well.
Hope that makes sense.
T9.AdjustsTransactionId = T.TransactionId
This ensures that all PaymentTypeId that equal 9 (Adjustments) are related to the original transactionId.
I can't just say ALL transactions of PaymentTypeId = 9 because then it will find unrelated transactions as well.
Hope that makes sense.
Also, awking is subtracting a negative number, which will add it instead; i.e.:
If the base data is this:
PartyId Amount
130528 4063.26
130528 -200.00<<
The query above will yield this, which is incorrect:
PartyId Amount
130528 4263.26
If the base data is this:
PartyId Amount
130528 4063.26
130528 -200.00<<
The query above will yield this, which is incorrect:
PartyId Amount
130528 4263.26
ASKER
Is there anything wrong with UNION as far as performance goes? My discomfort with it mostly because I don't use it often.
Open in new window