user3544117 user3544117 - 3 months ago 24
SQL Question

Select SUM using SQL subquery is too slow

I have a "Transaction" table.

A Transaction can have multiple "TransactionProduct".

A Transaction product can have multiple "PriceModifiers".

I have a query that return every transaction products with the total of price modifiers (reduction amount).

The query :

SELECT
TRANSACTION.DESCRIPTION,
TRANSACTIONPRODUCT.PRODUCTNAME,
TRANSACTIONPRODUCT.EFFECTIVEPRICE,
(
SELECT SUM(AMOUNT)
FROM PRICEMODIFIER
WHERE TRANSACTIONPRODUCTID = TRANSACTIONPRODUCT.TRANSACTIONPRODUCTID
)
AS REDUCTIONAMOUNT
FROM
TRANSACTION,
TRANSACTIONPRODUCT
WHERE
TRANSACTION.TRANSACTIONID = TRANSACTIONPRODUCT.TRANSACTIONID


If I remove the subquery with the Sum, the query is very fast to execute.

With the subquery, it takes several seconds...

Any idea how I can improve the query ?

Answer

Try this:

SELECT
  T.DESCRIPTION,
  TP.PRODUCTNAME,
  TP.EFFECTIVEPRICE,
  SUM(PM.AMOUNT)
FROM 
    TRANSACTION T
JOIN TRANSACTIONPRODUCT TP 
    ON T.TRANSACTIONID = TP.TRANSACTIONID
JOIN PRICEMODIFIER PM
    ON PM.TRANSACTIONPRODUCTID = TP.TRANSACTIONPRODUCTID
GROUP BY T.DESCRIPTION, TP.PRODUCTNAME, TP.EFFECTIVEPRICE
Comments