1 2 Previous Next 15 Replies Latest reply: Apr 16, 2012 10:37 AM by rp0428 RSS

    Using lots of cursors vs temp tables

    755501
      I have a very large procedures that executes many cursors in it. Basically, there is 1 main cursor that gets the employee and within the FOR loop executes lots of others. Most of these other cursors return a couple records per employee, but they are beinge executed 20-30,000 times withing the main cursor. Thought about executing the cursor independenty for all employees and storingt he data in PL/SQL tables, btu not sure if that will help the performance of the procedure any. Does anyone have suggestions?

      thanks
        • 1. Re: Using lots of cursors vs temp tables
          Frank Kulash
          Hi,
          bobmagan wrote:
          I have a very large procedures that executes many cursors in it. Basically, there is 1 main cursor that gets the employee and within the FOR loop executes lots of others. Most of these other cursors return a couple records per employee, but they are beinge executed 20-30,000 times withing the main cursor. Thought about executing the cursor independenty for all employees and storingt he data in PL/SQL tables, btu not sure if that will help the performance of the procedure any. Does anyone have suggestions?
          Combining the cursors into one sounds like a good idea. Using nested cursors may be eaiser to understand and to code, but it tends to be much slower.
          In Oracle, temporary tables are rarely necessary.

          I realize this answer isn't very detailed. If you ask a more specific question, describing exactly what you need to do, and post the code that's doing it correctly (if slowly) now, then someone could give a more detailed answer.
          • 2. Re: Using lots of cursors vs temp tables
            Stew Ashton
            I like to use subquery factoring (the WITH clause) for this kind of thing.

            Instead of:
            loop
              fetch empno into v_empno;
              select data from somewhere where empno = v_empno;
              ...
            end loop;
            I would write:
            with sub_query as (
              select empno, data from somewhere
            )
            select emp.*, sub_query.data 
            from emp, sub_query
            where emp.empno = sub_query.empno
            Just replace each little "one shot" cursor by a subquery that gets all the data and the crucial column that you can join on.

            In 2007 I worked on a batch job that took an hour doing things row by row. With this technique the batch job took 5 seconds.
            • 3. Re: Using lots of cursors vs temp tables
              755501
              Thanks for the info, i'm a bit confused. here is a sample of the code that i am running that i am trying to tune. You will notice the first main cursor grabs all of the records (approx. 20,000), within that loop the next cursor returns anywhere from 1 to 10 records.


              cursor get_students is
              select C.student_id, C.state_stu_id,
              C.pk_id fk_stu_base
              from stu_year A,
              stu_school B,
              stu_base C,
              contact_link D,
              contact E,
              lodging F,
              sch_base G,
              zip I
              where
              B.fk_stu_year = A.pk_id
              and ('842C402A37A84C3A87F74E311182600D:F54BF7AF35D846218D208F59837CC829') like '%' || B.fk_school || '%'
              and ('2' <> '9' or
              ('2' = '9' and
              c.state_stu_id is null) )
              and C.pk_id = A.fk_stu_base
              and D.fk_stu_base = C.pk_id
              and D.relation_resides = 'Y'
              and E.pk_id = D.fk_contact
              and F.pk_id = E.fk_lodging
              and G.pk_id = B.fk_school
              and I.pk_id = F.fk_zip
              and exists (select '' from school_year H
              where h.pk_id in ('9763A65F52514861878D0FF856FACCE6','9763A65F52514861878D0FF856FACCE6'))
              order by 1,2,3;
              --
              --
              cursor get_pccs IS
              select B.pk_id fk_stu_enrollment,
              C.pk_id fk_stu_school
              from stu_year Y,
              stu_school C,
              stu_enrollment B,
              school_calendar A,
              pcc_code P,
              sch_base J,
              stu_auxiliary G
              where Y.fk_stu_base = l_fk_stu_base
              and B.fk_stu_school = C.pk_id
              and B.pk_id = B.pcc_transaction_id
              and A.pk_id = B.fk_school_calendar
              and A.calendar_date <= to_date('03/31/2012','mm/dd/rrrr')
              and A.day_possible <> 0
              and P.pk_id = B.fk_pcc_code
              and P.report_to_state = 'Y'
              and J.pk_id = C.fk_school
              and C.fk_stu_year = Y.pk_id
              and G.fk_stu_year (+) = Y.pk_id
              and (Y.year = '9763A65F52514861878D0FF856FACCE6' or
              Y.year = '9763A65F52514861878D0FF856FACCE6')
              order by A.calendar_date desc, p.pcc_type asc;


              begin
              FOR x in get_students LOOP
              l_fk_stu_base := x.fk_stu_base;
              FOR y in get_pccs LOOP
              null;
              END LOOP;
              END LOOP;
              end;
              • 4. Re: Using lots of cursors vs temp tables
                sb92075
                >
                begin
                FOR x in get_students LOOP
                l_fk_stu_base := x.fk_stu_base;
                FOR y in get_pccs LOOP
                null;
                END LOOP;
                END LOOP;
                end;
                above can be optimized as below

                NULL;

                BTW - TEMP tables are rarely required with Oracle & the same goes for row by row PL/SQL processing

                Edited by: sb92075 on Apr 13, 2012 11:25 AM
                • 5. Re: Using lots of cursors vs temp tables
                  755501
                  I don't see any sample code in your example. Just 'NULL'
                  • 6. Re: Using lots of cursors vs temp tables
                    rp0428
                    >
                    I don't see any sample code in your example. Just 'NULL'
                    >
                    Exactly what SB was telling you. There is nothing in your loop that tells us what you are trying to do.

                    Are you rolling the data up for each person and creating aggragated tables?
                    Does your produre try to collect a hierarchy of data and return it to a client for display?

                    What is the procedure designed to accomplish?
                    How often is the procedure executed? Once a month? 10,000 times an hour?
                    • 7. Re: Using lots of cursors vs temp tables
                      Peter vd Zwan
                      Hi,
                      I don't see any sample code in your example. Just 'NULL'
                      And that is exactly the same as your code is doing:
                      Nothing.

                      When you give an example you must give some code that is doing something. Not just two loops with a NULL statement.

                      Show what you want to do and we can maybe help you better.

                      Regards,

                      Peter
                      • 8. Re: Using lots of cursors vs temp tables
                        Stew Ashton
                        Yeah, I think his point was that where you put "null;" (near the end of your example) is the interesting part, and you left it out.

                        Where that "null;" is, what processing is going on? Data modifications or reporting? Give me some details.

                        Based on those details, I can suggest "just joining", and so what if some of the data is repeated a few times --
                        or reducing the inner loop to to one line with some more advanced SQL.

                        Edited by: Stew Ashton on Apr 13, 2012 8:46 PM

                        P.S. There is an alternative that I call the "COBOL way" which will work if neither of the other two do, but I've only had to do that once so let's not worry about it right now.
                        • 9. Re: Using lots of cursors vs temp tables
                          755501
                          Ok. Maybe I should have been more specific. There is lots of pl/sql code in the inner loop (just left it out for readability).
                          So basically my outer loop is returning 20K records and the inner loop return about 1-10 records for each outer loop record.

                          begin
                          FOR x in get_students LOOP
                          l_fk_stu_base := x.fk_stu_base;
                          FOR y in get_pccs LOOP
                          --- lots of PL/SQL code
                          END LOOP;
                          END LOOP;
                          end;
                          • 10. Re: Using lots of cursors vs temp tables
                            Stew Ashton
                            So tell us in layman's term what that "lots of code" is trying to accomplish? What is the output?
                            • 11. Re: Using lots of cursors vs temp tables
                              rp0428
                              >
                              Ok. Maybe I should have been more specific.
                              >
                              But what are you doing with the data? In plain English what is the procedure designed to do?

                              And the initial question said the loop executes a lot of cursors. That sounds like the loop is trying to acquire all of the hierarchical data for a person entity from all of the linked child and lookup tables.

                              When I have encountered that in the past it was either to return a REF CURSOR for reporting purposes or to construct an object instance to pass to the middle-tier for use in a GUI.

                              We don't need the specific code but we need to know what the code is designed to do.
                              • 12. Re: Using lots of cursors vs temp tables
                                Frank Kulash
                                Hi,

                                It doesn't look like you're actually doing anything. I assume in your real code there's something (besides NULL) inside the inner loop. Without knowing what that is, I can't say how to improve the whole process.

                                Also, without test versions of your tables, I can't do anything.
                                It looks like you're doing soemthing analagous to this:
                                DECLARE
                                    this_deptno          scott.dept.deptno%TYPE;
                                
                                    CURSOR  dc  IS
                                             SELECT    deptno
                                         FROM      scott.dept
                                         WHERE     dname     < 'T'
                                         ORDER BY  dname;
                                
                                    CURSOR  ec  IS
                                             SELECT    ename
                                         FROM      scott.emp
                                         WHERE     deptno     = this_deptno
                                         AND           sal     < 3000
                                         ORDER BY  ename;
                                BEGIN
                                    FOR  dr  IN dc
                                    LOOP
                                        this_deptno := dr.deptno;
                                
                                     FOR  er  IN ec
                                     LOOP
                                         dbms_output.put_line (er.ename);
                                     END LOOP;
                                    END LOOP;
                                END;
                                /
                                where, for each row returned by dc, you run ec.
                                You can always combine them into one cursor like this:
                                    
                                DECLARE
                                    CURSOR  dec  IS
                                        WITH  dc  AS
                                     (
                                             SELECT    deptno
                                         FROM      scott.dept
                                         WHERE     dname     < 'T'
                                     )
                                             SELECT    ec.ename
                                         FROM      scott.emp     ec
                                         JOIN      dc          ON     ec.deptno     = dc.deptno
                                         WHERE     ec.sal     < 3000
                                         ORDER BY  dc.deptno
                                         ,           ec.ename;
                                BEGIN
                                    FOR  der  IN dec
                                    LOOP
                                        dbms_output.put_line (der.ename);
                                    END LOOP;
                                END;
                                /
                                Note that subquery dc in the second example is exactly like cursor dc in the first example, except that, since it's a sub-query, it doesn't have an ORDER BY clause.
                                The main query in the second example is very much like cursor ec in the first example. Instead of getting the deptno from a local variable, it gets it from a join, and the ORDER BY clause of the main query includes what used to be the ORDER BY clause of the outer query (in case that's important.)

                                Depeneding on exactly what you're doing, there may be more efficient ways to combine the cursors. In the example above, for instance, you don't need a sub-query; everything could be done in a single query. I just wrote it as I did to show a method of combining cursors that will (almost) always work, and will be much more efficient than using nested queries.
                                • 13. Re: Using lots of cursors vs temp tables
                                  Marwim
                                  Hello,

                                  if you can read the data in a single cursor as Frank has suggested, you can process it using "control break". Maybe this is what Stew called the "COBOL way".

                                  A way to programm that Billy describes as
                                  [1] anyone that programmed in Cobol or wrote software in the pre commercial database days where data was on magnetic tape, will be very familiar with it.
                                  [2] This is made worse by the standard education developers receive today, where something as simple as a control break algorithm is beyond their ability, or understanding the basic concept of modularisation
                                  http://en.wikipedia.org/wiki/Control_break

                                  Regards
                                  Marcus


                                  [1] {message:id=9344402}
                                  [2] {message:id=9549288}
                                  @Billy no offence intended. We might disagree about coding standards or the naming of features, but [2] I have very often seen myself.
                                  • 14. Re: Using lots of cursors vs temp tables
                                    Stew Ashton
                                    Marwim wrote:
                                    if you can read the data in a single cursor as Frank has suggested, you can process it using "control break". Maybe this is what Stew called the "COBOL way".
                                    That's part of what I mean.

                                    Right now the OP has big cursor 1A and little cursor 2A.
                                    It seems like most responders agree that there should be just one cursor that joins all 1A information to all 2A information.
                                    If the OP does that, the 1A information will sometimes be repeated.
                                    This is where the "control break" logic comes in. I think this is what you mean.

                                    I was thinking of a variation, which we used to call "master file" and "transaction file", where you would have one big 1A cursor and one big 2A cursor, both sorted the same way. In this situation, the "control break" would be more complicated: it would have to walk you through both cursors in sync.

                                    I have only used the second method once, and I certainly hope it can be avoided here.

                                    I agree with Frank and you about what the best way probably is.
                                    1 2 Previous Next