Performance issue - will collections improve perf?

Question

I have a performance issue that maybe you could help with. When I open the cursor, I then run several other SELECT statements to retrieve values using the variables from the cursor (see below). This seems to slow down the whole proc; I assume this is because the switch between PL/SQL and SQL engine. Would using table collections help? But as I see, since I need different columns from different tables I would need to have several different collections, how could I output everything in one record to return the result set?

CREATE OR REPLACE procedure sp_test (in_input in number)  
as 
  v_calc number;
  v_calc_res number;
  v1 number;
  v2 number;
  v3 number;
  CURSOR C_test IS 
    select col1 from test where col1 = in_input;
begin
  open c_test
  loop
    fetch c_test into v_calc;

    select col1 into v1 from t1;

    select col1 into v2 from t2;

    select col1 into v3 from t3;

    v_calc_res := v_calc * 5 * v1 * v2 * v3;

    dbms_output.put_line(v_calc_res);

  end loop;
end sp_test;
/

You may feel the need to obfuscate your query but unfortunately all that *blah-blah-blah* is just so much *meh*. There's little we can do to advise you. Please read [this answer on asking Oracle performance questions](https://stackoverflow.com/a/34975420/146325). It will explain how much information you need to provide to get a satisfactory answer here; it may also give you a steer on how to diagnose - and maybe solve - your problem for yourself. — APC, Jun 30 '19 at 21:39
Your example (which I edited to simplify the identifier names) is missing an exit condition inside the loop; hopefully your real code isn't missing that? Also, how do the select statements inside the loop relate to the cursor? Finally, how are you making sure that the selects in the loop are returning at most and at least a single row? — Boneist, Jul 01 '19 at 08:57
Hi Boneist, yes there is exit condition ( I just gave a quick example and not the actual code ). The Select statement inside the loop just take the variables from the cursor in the Where clause. — jayaramans, Jul 01 '19 at 12:17

score 4 · Answer 1 · answered Jun 30 '19 at 20:49

4

Row-by-row is usually slow-by-slow.

The fastest way - if possible - would be to skip the loop and do everything in a single SELECT statement, joining all tables involved into the operation. That might be a huge query so I'd suggest you to go step by step, adding one table after another, check performance, pay attention to execution plan, index columns involved and anything else that might help to improve performance.

answered Jun 30 '19 at 20:49

Littlefoot

131,892
15
35
57

Thanks! But actually I will end up with huge query with outer joins - and from a debug perspective not that nice if one particular SQL doesn't return. But this must be a common issue. I did think of having several collections and updating a result set into a GTT - but I don't know if this is necessary the best way forward. – jayaramans Jul 01 '19 at 08:52
1

You're welcome. I understand that procedural option is easier to maintain (*if this than that, else ...*), but - as you already know - performance suffers. Outer joins aren't *evil*, use them if necessary. Test frequently! Would collections do any good? Try and see; let us know the outcome. In my opinion, one `select` should be the way to go, but feel free to do anything you find appropriate. Perhaps code you write doesn't have to be *the best*, but *good enough*. Good luck! – Littlefoot Jul 01 '19 at 09:24

score 0 · Answer 2 · answered Jul 01 '19 at 05:31

Try to use everything in one query of the cursor.

Following is the code which is the try to just show you the demo considering your code.

CREATE OR REPLACE PROCEDURE SP_TEST (
    IN_INPUT IN   NUMBER
)  
as
    -- COMMENTED ALL THE VARIABLE EXCEPT NECESSARY ONE
    --V_CALC NUMBER;
    V_CALC_RES NUMBER;
    --V_BLAH_BLAH NUMBER;
    --V_BLAH_BLAH_BLAH NUMBER;
    --V_BLAH_BLAH_BLAH NUMBER;

CURSOR C_TEST IS 
SELECT --T.blah, T1.blah_blah, T2.blah_blah_blah , T3.blah_blah_blah_blah
    T.BLAH * 5 * T1.BLAH_BLAH * T2.BLAH_BLAH_BLAH * T3.BLAH_BLAH_BLAH_BLAH -- CALCULATION HERE
FROM
    TEST T -- ADDED ALL JOINS HERE
INNER JOIN T_BLAH_BLAH T1 ON (<JOIN CONDITION>)
      INNER JOIN t_blah_blah_blah T2 ON (<JOIN CONDITION>)
      INNER JOIN t_blah_blah_blah_blah T3 ON (<JOIN CONDITION>)
      where T.blah  = in_input;  -- WHERE CONDITION HERE
BEGIN 
OPEN C_TEST
LOOP
    FETCH C_TEST INTO V_CALC_RES; --RESULT OF THE CALCULATION DIRECTLY HERE


-- ALL SINGLE STATEMENTS ARE COMMENTED
--select blah_blah into v_blah_blah from t_blah_blah;
--select blah_blah_blah into v_blah_blah_blah from t_blah_blah_blah;
--select blah_blah_blah_blah into v_blah_blah_blah_blah from t_blah_blah_blah_blah;


-- CALCULATION ALREADY DONE IN CURSOR
--v_calc_res := v_calc*5*v_blah_blah*v_blah_blah_blah*v_blah_blah_blah_blah;
    DBMS_OUTPUT.PUTLINE(V_CALC_RES);
END LOOP;

END;
/

Cheers!!

Performance issue - will collections improve perf?

2 Answers2