Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Transactions - Stored Procedures vs Embedded SQL
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
![]() |
![]() |