Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Transactions - Stored Procedures vs Embedded SQL

Transactions - Stored Procedures vs Embedded SQL

From: James Burnetti <burnetti_at_mitre.org>
Date: Wed, 29 Jul 1998 16:11:41 -0400
Message-ID: <35BF81FD.3438535D@mitre.org>


I've got an application that listens to a message stream, uncompresses the message, parses it, and then decides which of some 50 possible inserts, updates, or deletes to do based on the contents of the message.

The application performs very well when the inserts, updates, or deletes (a single row at a time) are implemented as embedded SQL. It performs like
a dog when I replace the embedded SQL with calls to stored procedures.

In both cases, I start a transaction when the application starts up, commit after every N messages are parsed (each message can result in up to
10 separate inserts, updates, and deletes), and begin a new transaction. A bigger transaction size improves the performance of the embedded SQL version,
but not that of the stored procedures.

The performance of the stored procedures is about the same as that of embedded SQL with a commit after each individual insert, update, or delete -
making me suspect that the stored procedures are "autocommiting" after each time they are called. My shared pool is rather large, so I don't think
the PL/SQL is being parsed on each call.

Is there something I ought to know about defining transactions using a combination of embedded SQL (for the BEGIN TRANSACTION and COMMITS) and stored procedures (for the INSERTS, UPDATES, and DELETES)? Any other
ideas?

Thanks
Jim Burnetti
burnetti_at_mitre.org Received on Wed Jul 29 1998 - 15:11:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US