DB wtf



  • Wierd situation at work, not sure where to post (sorry) - please help?

    We have 30+ DBA's, in another state, and our PBX and e-mail systems just went down. Corporate policy precludes using IM (ever), and we are *not* allowed to use our cell phones for corporate business. Someone just discovered the following pattern is used in about a zillion places, and the performance sucks:

    Oracle:
    CURSOR cursor1 return Table1%RowType is select * from Table1 where where-clause-1 for update nowait
    CURSOR cursor2 return Table1%RowType is select * from Table1 where where-clause-2 for update nowait
    ...
    For rec1 in cursor1 loop
        For rec2 in cursor2 loop
            update Table1 set ...using data from rec1  where Current of cursor2;
            update Table1 set someField=variableFromOutsideBothLoops where Current of cursor1;
        end loop cursor2;
    end loop cursor1;
    

    I don't know set processing well enough to fix this, but there has to be a (better) way to do it. Any ideas?

    Thanks in advance.


  • @snoofle said:

    Wierd situation at work, not sure where to post (sorry) - please help?

    We have 30+ DBA's, in another state, and our PBX and e-mail systems just went down. Corporate policy precludes using IM (ever), and we are *not* allowed to use our cell phones for corporate business. Someone just discovered the following pattern is used in about a zillion places, and the performance sucks:

    Oracle:
    CURSOR cursor1 return Table1%RowType is select * from Table1 where where-clause-1 for update nowait
    CURSOR cursor2 return Table1%RowType is select * from Table1 where where-clause-2 for update nowait
    ...
    For rec1 in cursor1 loop
    For rec2 in cursor2 loop
    update Table1 set ...using data from rec1 where Current of cursor2;
    update Table1 set someField=variableFromOutsideBothLoops where Current of cursor1;
    end loop cursor2;
    end loop cursor1;

    I don't know set processing well enough to fix this, but there has to be a (better) way to do it. Any ideas?

    Thanks in advance.

    Try to do it all in one or two update statements.

    update table1 set x,y,z = (select x,y,z from table1 where ...) where ...;

     

    A few weeks ago, we had a similar problem - lots of insert statements in a for-select-loop. Took many hours - too long to finish in time.

    After changing that to a single (admittedly long) insert ... select ...-statement, it now takes less than 2 minutes.



  • *Bingo*

    (Thanks :)


Log in to reply