Hello Experts,
I have couple of queries which i need your advise on, if it can be rewritten/optimized to better the performance.
Optimization suggestions based on the execution plan (index changes) and query logic.
Thanks for sharing your inputs and analysis.
Query 1:
SELECT DT.classid, SUM(IT.expectedamount) AS expAmt, SUM(IT.debitamount) AS debAmtFROM deptrans DT inner join itemtotal IT ON DT.id = IT.deposittransid left outer join depreceipt DR ON DR.deposittransid = DT.id WHERE DT.bankaccountid IN ( ? ) AND (( ( DT.statecode IN ( ?, ?, ?, ?, ?, ?, ?, ? ) OR ( DT.statecode IN ( ? ) AND IT.statecode = ? ) ) AND DR.requesttime >= ? AND DR.requesttime <= ? )) GROUP BY DT.classid ORDER BY DT.classid
Execution Plan:
===============
Query 2:
SELECT Count(DISTINCT DT.id) FROM deptrans DT inner join itemtotal IT ON DT.id = IT.deposittransid left outer join depreceipt DR ON DR.deposittransid = DT.id WHERE DT.bankaccountid IN ( ? ) AND (( ( DT.statecode IN ( ?, ?, ?, ?, ?, ?, ?, ? ) OR ( DT.statecode IN ( ? ) AND IT.statecode = ? ) ) AND DR.requesttime >= ? AND DR.requesttime <= ? ))
Execution Plan:
===============
