Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -2 down vote favorite

I am looking into ways to optimize the following query which uses a lot of subqueries because they tend to deteriorate the speed of the query. Although the following query works fine, it completes in about 6 seconds which is unacceptable. It searches a table of about 500k customers. Any ideas?

 SELECT (
 (SELECT coalesce(SUM(cashout),0)- 
                        ((select coalesce(sum(Buyin),0) from [Transaction] where TYPE='Credit' and CustomerID=132)
                         + (select coalesce(sum(Paid),0) from [Transaction] where TYPE='Credit' and CustomerID=132))


FROM [transaction]
WHERE TYPE='Credit'
AND CustomerID=132
)
-------------------
+
(
(SELECT coalesce(SUM(cashout),0)
                    - (select coalesce(sum(Paid),0) from [Transaction] where TYPE='Debit' AND Cashout>buyin and CustomerID=132) 
                    +  (select coalesce(sum(Cashout),0)- (select coalesce(sum(PAID),0) from [Transaction] where TYPE='Debit' AND Cashout<buyin and CustomerID=132)
                             from [Transaction] where TYPE='Debit' AND Cashout<Buyin and CustomerID=132)
                    +  (select coalesce(sum(Cashout),0)- (select coalesce(sum(PAID),0) from [Transaction] where TYPE='Debit' AND Cashout=buyin and CustomerID=132)
                             from [Transaction] where TYPE='Debit' AND Cashout=Buyin and CustomerID=132)
FROM [Transaction]
WHERE CustomerID=132
AND TYPE='Debit' 
AND Cashout>buyin )
)
--------------
-
(
select coalesce(sum(Paid),0)
from [Transaction] 
where type='Debit Settlement'
AND CustomerID =132
)
--------------
+
(
select coalesce(sum(Paid),0)
from [Transaction] 
where type='Credit Settlement'
AND CustomerID =132
)
);
share|improve this question
1  
You can probably consolidate all of these into one main query and get rid of the subqueries with for example SELECT sum(CASE WHEN type='Credit Settlement' AND CustomerID =132 THEN Paid END) and so on. – Martin Smith 9 hours ago
Thanks! That helped as well. @MartinSmith – nec tso 8 hours ago

2 Answers

up vote 1 down vote accepted

Consider adding cache table where you have precalculated all debit, credit, debit settlement and credit settlement for customer. Im assuming transaction table never gets updated, only inserted so simple trigger performed after insert to do the calculations. 

CREATE TABLE Balance
(
    CustomerID int NOT NULL,
    Debit decimal(18, 2) NOT NULL,
    Credit decimal(18, 2) NOT NULL,
    DebitSettlement decimal(18, 2) NOT NULL,
    CreditSettlement decimal(18, 2) NOT NULL
)

CREATE TRIGGER CalculateBalance
ON [Transaction]
 AFTER INSERT,UPDATE
AS
DECLARE @CustomerID int

SET @CustomerID = (SELECT customerID FROM inserted)

-- Calculate Debit, Credit, DebitSettlement and CreditSettlement for current customer

GO
share|improve this answer
Many thanks. I'm not sure why some people vote down my question though. Is there a particular reason? – nec tso 8 hours ago
No idea why it gets downvoted. Make sure when you implement the trigger that you calculate the balances with the query you used to generate the result, just increment the values to the balance accordingly. Otherwise the inserts will be slow. – Janne Matikainen 7 hours ago
up vote 0 down vote

Try doing a execution plan for the query it also gives suggestions regarding where in the query is lagging, also try to create index on the fields of the tables for which you are using the query.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.