Optimize a SQL select query in a loop

Question

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);

Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh
– jarlh, Commented Dec 10, 2018 at 12:24
What's the loop? If it iterates around something in the database then your best chance at optimisation will be to incorporate that into your query. — LoztInSpace
– LoztInSpace, Commented Dec 10, 2018 at 12:56
Whenever you're asking for help on query optimization, it's important to tell us what indexes you have on your tables. If you don't have indexes, then that's the first thing to address. — Andy Lester
– Andy Lester, Commented Dec 10, 2018 at 16:58

marc_s · Accepted Answer · 2018-12-10 13:04:55Z

2

There are lot of solutions

But first use modern,explicit joins.
Your query for column m1 contains *, use required columns only
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;

edited Dec 10, 2018 at 13:04

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 10, 2018 at 12:43

Kedar Limaye

1,0418 silver badges15 bronze badges

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

Comments

Mark Stewart · Accepted Answer · 2018-12-11 18:54:50Z

0

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.

edited Dec 11, 2018 at 18:54

answered Dec 10, 2018 at 16:32

Mark Stewart

2,1184 gold badges27 silver badges33 bronze badges

Collectives™ on Stack Overflow

Optimize a SQL select query in a loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related