Solved

SQL: Union

Posted on 2014-10-21
16
348 Views
Last Modified: 2014-10-21
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).
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

Open in new window

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?
0
Comment
Question by:pzozulka
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
ID: 40394775
Not sure about 'better', although I'd recommend using UNION ALL instead of UNION, as UNION eliminates duplicates, and just to make sure that if there's not a duplicate row between the two ( negative payment / two adjustments of the same amount?) that the duplicate isn't eliminated.

>Together, they are my derived table (JOIN) to get total customer payments (payments - adjustments).
Show me what you mean  by this, as I don't see a JOIN here.   A subquery would work though, and depending on whate you're trying to do you can remove the SUM / GROUP BY in the main query above, and add it in the parent query below...

SELECT a.PartyID, a.Amount
FROM (the above query goes here) a

Open in new window

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
ID: 40394777
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
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394798
I didn't include the join in the above, but just wanted to make it known that the above code was part of my derived table. But here it is below, using your suggestion of UNION ALL. And just to make sure we're not removing dups, I added the transactionID for uniqueness.

Is there anything wrong with UNION as far as performance goes? My discomfort with it mostly because I don't use it often.

SELECT a.PartyId, SUM(a.amount)
FROM 
(
	SELECT T.TransactionId, T.PartyId, T.Amount
	FROM [Transaction] T
	WHERE T.PaymentTypeId = 1 

	UNION ALL

	SELECT Tr.TransactionId, Tr.PartyId, Tr.Amount
	FROM [Transaction] Tr JOIN [Transaction] T ON Tr.AdjustsTransactionId = T.TransactionId
	WHERE Tr.PaymentTypeId = 9 AND 
	T.PaymentTypeId = 1
) a
GROUP BY a.PartyId

Open in new window

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
ID: 40394804
>> Is there anything wrong with UNION as far as performance goes? <<

You're scanning the [Transaction] table at least one extra time, then you have additional I/O to combine the results from the separate scans.  If possible, it's best to do it in a single query, as I posted above.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
ID: 40394817
>Is there anything wrong with UNION as far as performance goes?
UNION eliminates duplicates, which takes some extra time.  UNION ALL does not eliminate duplicates, so no extra time.

Having said that, if you can get to a JOIN solution such as Scott's above, as his statement is correct in that it would eliminate the second SEEK/SCAN on Transaction.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394852
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%.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394857
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:
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

Open in new window


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

Open in new window

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 450 total points
ID: 40394893
The "% of batch" value is useless.  Instead, issue these statements:

SET STATISTICS IO ON
SET STATISTICS TIME ON

and then run both query.  Look at the logical I/Os only, not the physical; in fact, for a more accurate comparison, confirming that there are no physical I/Os in either query, only logical.  That will provide the best comparison.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Author Comment

by:pzozulka
ID: 40394926
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?
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394940
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
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394959
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?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40395021
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.
0
 
LVL 31

Expert Comment

by:awking00
ID: 40395078
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;
0
 
LVL 31

Expert Comment

by:awking00
ID: 40395097
>>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;
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40395136
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40395275
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
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now