0

Is there a way to convert the following into a query without subqueries (or with a lesser number of subqueries) in order to make it faster.

select (   +(select sum(coalesce(quantity,0)) from transaction where 
            buyer_number = 101 and stock_id = 22 and status = \'bought\') 
           -(select sum(coalesce(quantity,0)) from transaction where 
            seller_number = 101 and stock_id = 22 and status = \'sold\') 
       ) as balance_primary, 
       (   +(select sum(coalesce(quantity,0)) from transaction where 
            buyer_number = 101 and stock_id = 22 and status = \'received\') 
           -(select sum(coalesce(quantity,0)) from transaction where 
            seller_number = 101 and stock_id = 22 and status = \'gifted\') 
       ) as balance_secondary
forpas
  • 160,666
  • 10
  • 38
  • 76
Jimski
  • 826
  • 8
  • 23
  • 1
    Why is it slow, the optimized of the database wil take care of it if you have an combined index of Byer number and status – nbk Apr 09 '23 at 08:18
  • 1
    Is it actually `'bougth'` instead of `'bought'`? – forpas Apr 09 '23 at 08:24
  • 1
    You can write the query without subqueries, but it is unlikely to become faster. Your query makes it very easy for the DBMS optimizer to see whether and which indexes to use. So, in case the optimizer is not that good that it gets the same execution plan however you write the query, your query may already get the best plan, while another may not. But well, if there are no indexes available and the optimizer is not that good, then it may really perform four full table scans with your query and only one with a query without subqueries. But I'd recommend providing indexes anyway. – Thorsten Kettner Apr 09 '23 at 09:18
  • 1
    (At last: `sum(coalesce(quantity,0))` may be some nanoseconds slower than `coalesce(sum(quantity),0)`, because it applies `COALESCE` on every row instead of just once on the final result.) – Thorsten Kettner Apr 09 '23 at 09:20

1 Answers1

2

All the WHERE clauses in the subqueries use the condition stock_id = 22, so it can be moved in the WHERE clause of the final query.

You can get the sums with conditional aggregation:

SELECT SUM(CASE WHEN buyer_number = 101 AND status = 'bought' THEN quantity ELSE 0 END) -
       SUM(CASE WHEN seller_number = 101 AND status = 'sold' THEN quantity ELSE 0 END) AS balance_primary,
       SUM(CASE WHEN buyer_number = 101 AND status = 'received' THEN quantity ELSE 0 END) -
       SUM(CASE WHEN seller_number = 101 AND status = 'gifted' THEN quantity ELSE 0 END) AS balance_secondary 
FROM transaction
WHERE stock_id = 22;

You could also write the WHERE clause as:

WHERE stock_id = 22 AND 101 IN (buyer_number, seller_number)
forpas
  • 160,666
  • 10
  • 38
  • 76
  • If buyer_number is already in the CASE WHEN clause then why wuld repeat it again in WHERE stock_id = 22 AND 101 IN (buyer_number, seller_number) – Jimski Apr 09 '23 at 09:46
  • 1
    @Jimski because with `WHERE stock_id = 22 AND 101 IN (buyer_number, seller_number)` the table is filtered even more than just `WHERE stock_id = 22` and the aggregation operates on fewer rows. – forpas Apr 09 '23 at 09:53
  • Thanks, I just learned something new. I upvoted your answer. – Jimski Apr 10 '23 at 10:18