Home Forums SQL Server 2008 Working with Oracle Best practice for temporary working tables. RE: Best practice for temporary working tables.

  • January 28, 2016 at 12:43 am

    #1854875

    Hugo Kornelis (1/27/2016)

    Disclaimer first: I have never used Oracle. So take the below with a huge helping of salt, please.

    However, I have heard many times that Oracle, unlike SQL Server, does not have a huge performance difference between set-based and row-based operations. In other words, using a cursor or other iterative approach in Oracle will not hurt you quite as bad as it does in SQL Server.

    So for the problem as described by Rich, I am assuming that the typical Oracle way of handling it would be to create a cursor for the first convoluted-logic query, then step through the results and handle the rest of the logic for each row individually.

    I worked with Oracle for only 3 years but, in that time, properly written set based code smokes cursors even in Oracle. The really bad part is (or at least was), a SELECT can't be used to return a result set directly to a GUI. Instead, it's usually better if you write a Global Reference Cursor and embed it in the same package as the procs. Because of the way those are built, they really good at speed. Cursors inside of stored procedures? Not so much. It is true that they don't hurt as bad as they do in SQL Server but they're still comparatively very slow compared to properly written set based code.

    The problem is that you sometimes can't get away from cursors. Even triggers are row based. You write the code you want and the say "FOR EACH ROW". Again and thankfully, triggers are another place where cursors are too bad... especially since they're the only show in town there.

    As a bit of a sidebar, I do wish that MS would build true "BEFORE" triggers like what they have in Oracle. Makes validations a whole lot easier and really helps avoid rollbacks due to illegal data trying to go into a table.

    I'm still sold on SQL Server, though. You've just absolutely gotta love TempDB and temporary tables. I also love the fact that it allows me to bend the rules when I need to. Oracle is comparatively very rigid. And I absolutely hate the UPDATE with no FROM clause. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)