CODEWITHSUNDEEP
sqloracle-database
hulkbuster001
hulkbuster001

Reputation: 33

Optimize a SQL select query in a loop

I want to optimize a select query in a loop of a SQL procedure. The loop iterates around 10000 times and the select query takes approx. 30 ms for each iteration which increases the overall execution time of the procedure 

SELECT *
FROM BANKACCOUNTS B,
     MAPPING M,
     UPL_DTR_UPLOAD UP,
     (SELECT * FROM MAPPING WHERE SOURCE = 'KARVY_BANK_CODE') M1
 WHERE B.SCHEME_CODE = M.INTERNALCODE
   AND M1.INTERNALCODE = B.BANK_CODE
   AND M.SOURCE        = 'R0'
   AND B.AC_TYPE       = 'FUNDING'
   AND M.EXTERNALCODE IS NOT NULL
   AND UPPER(TRIM(M.EXTERNALCODE || M1.EXTERNALCODE || B.AC_NO)) =
           Upper(UP.Scheme || UP.Fundingbnk || UP.fundingacc);

Upvotes: 1

Views: 956

Answers (2)

Mark Stewart
Mark Stewart

Reputation: 2098

As @LoztInSpace mentions, you can almost certainly replace your PL/SQL loop to iterate "about 10,000 times" to become the driving query. IE: if you need to do something with the results from each row returned in your query you posted, for each row in the "do something about 10,000 times" that implies the outside loop is another query, then nest your query (well, Kedar's version of the query) inside your outer loop.

Each execution of the PL/SQL loop is going to have to invoke the SQL engine, forcing a context switch; that is probably 10 ms of your 30 ms if not more. Search https://asktom.oracle.com with keywords PL/SQL "nested loop" for examples.

You can also look at PL/SQL bulk processing statements FORALL and BULK COLLECT for possible improvements.

Upvotes: 0

Kedar Limaye
Kedar Limaye

Reputation: 1041

There are lot of solutions

  1. But first use modern,explicit joins.

  2. Your query for column m1 contains *, use required columns only

  3. Check explain plan and use of index

Code:

SELECT * 
FROM bankaccounts B 
JOIN mapping M ON B.scheme_code = M.internalcode 
JOIN 
    (SELECT internalcode, externalcode
     FROM mapping 
     WHERE source = 'KARVY_BANK_CODE') M1 ON M1.internalcode = B.bank_code 
JOIN upl_dtr_upload UP ON UPPER(TRIM(M.externalcode || M1.externalcode || B.ac_no)) = UPPER(UP.scheme || UP.fundingbnk || UP.fundingacc) 
WHERE  
    M.source = 'R0' 
    AND B.ac_type = 'FUNDING' 
    AND M.externalcode IS NOT NULL;

Upvotes: 2

Related Questions