-1

Below is my query. limit 0,5000 and running without limit clause for all records (287,795) takes same time 9.5 seconds. I have to repeat this query for 57 times with increasing limit ranges likewise (0,5000), (5000,10000). So time is very critical.

Could you suggest any method to increase the speed. In sql performance increase or any strategical change.

I have removed below inner join for testing prepose then tacked less than 1 second. But the join is required. Because i have to check

"APDMD.DAYS_TO_DEMAND_ARREARS" is within CC.FROM_DPD & CC.TO_DPD 
below is table CC

enter image description here

SELECT   COALESCE ( APDMD.DMS_SOL_ID, '--' ) AS SOL_ID,  
COALESCE ( APDMD.ACID, '--' ) AS ACCOUNT_NO,  
COALESCE ( APDMD.CUST_ID, '--' ) AS CUSTOMER_ID,  
COALESCE ( APDMD.ACCT_NAME, '--' ) AS CUSTOMER_NAME,  
COALESCE ( APDMD.SCHM_CODE, '--' ) AS SCHEME_CODE,  
COALESCE ( APDMD.SANCT_LIM, 0 ) AS SANCTION_LIMIT,  
COALESCE ( APDMD.CLR_BAL_AMT, 0 ) AS OS_BALANCE,  
COALESCE ( APDMD.CAP_OVER_DUE, 0 ) AS CAPITAL_ARREARS,  
COALESCE ( APDMD.INT_OVER_DUE, 0 ) AS INTEREST_ARREARS,  
COALESCE ( APDMD.ACCT_CRNCY_CODE, '--' ) AS CURRANCY_CODE,  
COALESCE ( APDMD.ACCT_MGR_USER_ID, '--' ) AS ACCOUNT_MANAGER,  
COALESCE ( CC.STAGE, '--' ) AS STAGECLASSIFICATION,  
COALESCE ( CC.SUB_CLASSIFICATION, '--' ) AS classification,  
COALESCE ( APDMD.ARRMONTHS, 0 ) AS MONTH_ARREARS,  
COALESCE ( APDMD.ARRDAYS, 0) AS DAYS_ARREARS,  APDMD.NPA_DATE AS NPADATE,  
COALESCE ( APDMD.LOCATION_CODE, '--' ) AS LOCATION_CODE,  
COALESCE ( APDMD.GL_SUB_HEAD_CODE, '--' ) AS GL_SUB_HEAD_CODE,  
COALESCE ( APDMD.IIS_LKR, 0 ) AS IIS_LKR,  
COALESCE ( APDMD.SP_PROVISION, 0 ) AS SP_PROVISION,  
COALESCE ( APDMD.SP_PROVISION_LKR, 0 ) AS SP_PROVISION_LKR,  
COALESCE ( APDMD.BSC_TEAM_LEADER, 0 ) AS BSC_TEAM_LEADER,  
APDMD.ACCT_OPN_DATE AS ACCT_OPN_DATE,  

( SELECT SUM( AA.CLR_BAL_AMT )    FROM app_dms_daily AA WHERE AA.CUST_ID = CUSTOMER_ID )  AS PORTPOLIO,
  
COALESCE (  ( SELECT  TKTH.resolutiondescription  FROM  tickethistory TKTH  WHERE  TKTH.tickethistoryid = (SELECT MAX( TH.TICKETHISTORYID ) FROM tickethistory TH WHERE TH.TICKETID = T.TICKETID )
   ),  '--'   ) AS REMARKS,
  
COALESCE ( DWH_MOBILE_NO, '--' ) AS MOBILENO,   
COALESCE(APDMD.MORATORIUM_GIVEN, '--')      AS MORATORIUM_GRANTED,  
COALESCE(APDMD.MORATORIUM_PERIOD, '--')      AS MORATORIUM_PERIOD 

FROM  app_dms_daily APDMD
 
#adding below increase 9s ---> 9*50 --> 7.5 min   
INNER JOIN classification_configuration CC ON  CASE     
WHEN CC.FROM_DPD IS NULL         
THEN APDMD.DAYS_TO_DEMAND_ARREARS <= CC.TO_DPD     
WHEN CC.FROM_DPD AND CC.TO_DPD IS NOT NULL         
THEN APDMD.DAYS_TO_DEMAND_ARREARS >= CC.FROM_DPD AND APDMD.DAYS_TO_DEMAND_ARREARS <= CC.TO_DPD     
WHEN CC.TO_DPD IS NULL         
THEN APDMD.DAYS_TO_DEMAND_ARREARS >= CC.FROM_DPD     ELSE '' END
  
LEFT OUTER JOIN ticket T ON T.ACCID = APDMD.ACID  
ORDER BY 
DMS_SOL_ID, 
OS_BALANCE ASC  
LIMIT 0,5000;

Strategical change or sql query performance improvement

Tishan Madushanka
  • 1
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [ask] [Help] – philipxy Nov 13 '22 at 19:09
  • [mre] [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) [Re SQL "performance".](https://stackoverflow.com/a/24196511/3404097) – philipxy Nov 13 '22 at 19:10
  • Would it be OK to replace (in the `ORDER BY`) `OS_BALANCE` with `APDMD.CLR_BAL_AMT`? – Rick James Nov 13 '22 at 23:47

1 Answers1

0

Since the only use (that I can c) for ticket is in the subquery with the MAX, can you move that into the subquery?

These indexes may help:

APDMD:  INDEX(DAYS_TO_DEMAND_ARREARS)
CC:  INDEX(FROM_DPD, TO_DPD,  STAGE, SUB_CLASSIFICATION, TO_DPD)
ticket_T:  INDEX(ACCID,  TICKETID)
app_dms_daily:  INDEX(CUST_ID,  CLR_BAL_AMT)
tickethistory:  INDEX(TICKETID,  TICKETHISTORYID)
tickethistory:  INDEX(tickethistoryid,  resolutiondescription)

OFFSET is terribly inefficient for pagination. Normally I recommend that you remember where you left off, but that may not be practical with all those JOINs.

Rick James
  • 135,179
  • 13
  • 127
  • 222