Optimize Query with Left Joins

Question

The following query is executing for ~30seconds.I couldn't find the bottleneck. Please help me to increase the performance.

SELECT 'Beginning Balance' AS TYPE, 
       COUNT(DISTINCT lmt.lmt_corp_id) ,
       COUNT(DISTINCT ldt.ldt_clnt_id) ,
       COUNT(DISTINCT ldt.ldt_guar_id) ,
       COUNT(DISTINCT ldt.ldt_loan_ref_id),
       COUNT(ldt.ldt_line_item_id) ,
       SUM(ldt.ldt_princpal_bal) , 
       SUM(ldt.ldt_fund_amt), 
       SUM(ldt.ldt_princpal_bal) - SUM(ldt.ldt_fund_amt), 
       SUM(pay.PaidPrinc), 
       SUM(pay.PaidInt), 
       SUM(pay.PaidOther), 
       SUM(pay.TotalPmt), 
       SUM(fn_get_line_pb_by_date(ldt.ldt_line_item_id, '2014-01-16', 'BOD')), 
       sum(ovr.OPAmt),
FROM ldt_tran_loan_det ldt
JOIN lmt_mst_loan lmt 
  ON ldt.ldt_loan_ref_id = lmt.lmt_loan_id AND 
     ldt.ldt_clnt_id IN (262,75,191,49,267,277,23,79)
LEFT JOIN tmp_pmt pay ON pay.LineItemId = ldt.ldt_line_item_id
LEFT JOIN tmp_over_pmt ovr ON ovr.LineItemId = ldt.ldt_line_item_id   
LEFT JOIN lct_tran_loan_close lct 
       ON lct.lct_line_item_id = ldt.ldt_line_item_id
WHERE DATE(ldt.ldt_funded_date) < '2014-01-16' AND 
      (ldt.ldt_status != 'PIF' OR 
       (ldt.ldt_status = 'PIF' AND ldt.ldt_last_pmt_date >= '2014-01-16')) AND
      (lct.lct_hff_entry_date IS NULL OR 
       (DATE(lct.lct_hff_entry_date) >= '2014-01-16' AND 
        lct.lct_txn_type IN ('REC','RCL')))

DDL:

CREATE TABLE `ldt_tran_loan_det` 
( `ldt_line_item_id` bigint(20),
  `ldt_loan_ref_id` int(11) ,
  `ldt_clnt_id` int(11) ,
  `ldt_guar_id` int(11) ,
  `ldt_princpal_bal` decimal(20,2) ,
  `ldt_fund_amt` decimal(20,2) ,
  `ldt_funded_date` datetime ,
  `ldt_last_pmt_date` date , 
  PRIMARY KEY (`ldt_line_item_id`));

CREATE TABLE `lmt_mst_loan` (`lmt_loan_id` int(11), `lmt_corp_id` int(11) );

CREATE TABLE `tmp_pmt` 
(`PaidPrinc` decimal(20,2),
 `PaidInt` decimal(20,2),
 `PaidOther` decimal(20,2),
 `TotalPmt` decimal(20,2),
 `LineItemId` int(11));

CREATE TABLE `tmp_over_pmt` ( `OPAmt` decimal(20,2),`LineItemId` int(11) );

CREATE TABLE `lct_tran_loan_close` 
(`lct_line_item_id` bigint(20),
 `lct_txn_type`char(20) , 
 `lct_hff_entry_date` datetime);

Here is the execution plan: enter image description here

Are your database statistics up to date - do all your tables (apart from lmt_mst_loan) really only have 1 or 2 records in them? If not, try updating your database statistics. — user359040
– user359040, Commented Jan 17, 2014 at 7:31
Thanks for your comment. Yeah I have optimized my tables but still, it takes same time. I have more than 300k records in my tables. — Gowtham VJ
– Gowtham VJ, Commented Jan 17, 2014 at 8:47

Krishna · Accepted Answer · 2014-01-17 07:28:04Z

2

This is not the final solution but just 1 improvement point :
create indexing on ldt_funded_date column. do not use date() function as it will not use index. Instead use ldt.ldt_funded_date < '2014-01-16 00:00:00'

answered Jan 17, 2014 at 7:28

Krishna

4285 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Krishna Over a year ago

Also please make sure you have done indexing on the columns used for joins and where conditions columns as well.

Bohemian Over a year ago

+1 never use date on rows. Instead, always compare the column value to a generated (but constant for the query) value.

Gowtham VJ Over a year ago

No. Only for testing purpose i hardcoded the date. In real time it comes from front end.

Collectives™ on Stack Overflow

Optimize Query with Left Joins

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related