Although someone made performance claims about stored procedure and Java
language is not the cause of performance differences.
In my testing logic written in PL/SQL runs approximately 300 times slower
than the same computation in Java.
PL/SQL is also lacking many basic language/library features which are
available for Java.
The performance advantages from using stored procedures come from lower
latency accessing data.
We found large performance improvements accessing our databases which are
about 100 miles from our application server.
Note that doing a few selects, inserts and updates in one go can be done
with Oracle, MS SQL, and Sybase without *stored* procedures.
In Oracle of you just send some sql like:
DECLARE
FRED VARCHAR2(20);
BEGIN
SELECT FOO INTO FRED ...
WHILE ... LOOP
INSERT INTO BLAH....
END LOOP;
UPDATE BLAH....
END;
with executeImmediate and it will work fine.
Another issue with *stored* procedures is with versions.
A particular database can only have one version a stored procedure with a
particular name.
This makes version control more complex and makes you environment less
flexible.
If you are on Oracle there are some special minuses to the use of stored
procedures.
If a database is somehow modified (restored from backup etc.) then stored
procedures become invalid and all your applications will stop working.
You need to recompile them.
There is the well know issue that many client environments including exotica
like NetDynamics, and more wide spread tools, like Visual Basic, can not
access receive result sets or complex types from Oracle stored procedures.
Brendan Johnston
TeamND
-----Original Message-----
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 04, 1999 3:08 AM
To: [EMAIL PROTECTED]
Subject: Re: [ND] Using Stored procedures for transaction
management
That's true. But I must admit I really miss Oracle's handy DECODE!
Thx for replying.
JT
(Embedded
image moved Curt Springer <[EMAIL PROTECTED]>
to file: 06/04/99 03:39 AM
pic24330.pcx)
To: [EMAIL PROTECTED]
cc: (bcc: Janet Traub/IS/SSC/THD)
Subject: Re: [ND] Using Stored procedures for transaction
management
That route will be more attractive when (and yes, it's coming or
here
already, depending on dbms and version) one is able to write stored
procedures in vanilla java and not some bastardized proprietary
extension
of SQL.
-- Curt
At 01:40 AM 6/4/99 -0400, [EMAIL PROTECTED] wrote:
>
>
>
>I'm not used to using stored procedures but recently I was looking
over some
>one's ND4 code (for an app that talks to Informix 7) and I noticed
that
stored
>procedures were used whenever a series of INSERT/DELETEs needed to
be
executed
>as a transaction. That looked like a smart coding technique to me,
and I'd
like
>to get other opinions.
>
>Do you recommend using SPs like that? Pros/cons?
>
>Thanks!
>Janet
>
>
>_________________________________________________________________________
>
>For help in using, subscribing, and unsubscribing to the discussion
>forums, please go to:
http://www.netdynamics.com/support/visitdevfor.html
>
>For dire need help, email: [EMAIL PROTECTED]
>
_________________________________________________________________________
For help in using, subscribing, and unsubscribing to the discussion
forums, please go to:
http://www.netdynamics.com/support/visitdevfor.html
For dire need help, email: [EMAIL PROTECTED]
<< File: pic24330.pcx >>
_________________________________________________________________________
For help in using, subscribing, and unsubscribing to the discussion
forums, please go to: http://www.netdynamics.com/support/visitdevfor.html
For dire need help, email: [EMAIL PROTECTED]
