Java Stored Procedure Vs PL/SQL Stored Procedure

Question

In Oracle DBMS, which is better performance, calling a java stored procedure from another pl/sql stored procedure or calling a pl/sql stored procedure from another pl/sql stored procedure.

By the way I have a loop in my pl/sql procedure that will call the java procedure multiple times (i.e. my code is flipping between PL/SQL and Java Stored Procedures), so does this slow down the performance)?

I'd assume that each call from PL/SQL to Java involves a context switch, so I'd guess this runs pretty slow. Perhaps it would be possible to get rid of the loops altogether, but that's impossible to say without seeing your code. — Frank Schmitt
– Frank Schmitt, Commented Jul 25, 2011 at 20:27
Thanks Frank, actually i,ve calculated the time in milliseconds, of calling the equivalent pl/sql procedure the same number of loop cycles (600 loop cycle), and the result was, almost there was no difference, except that it finished about 100 milliseconds earlier. — Khalid Galal
– Khalid Galal, Commented Jul 25, 2011 at 20:58
I agree with Frank that there will inevitably be two context switches for each call to the Java stored procedure (PL/SQL -> Java and back again). If the called procedure is pure PL/SQL (i.e. no SQL access) then the PL/SQL option would be faster (try it over 10000 iterations rather than 600). Also, could the PL/SQL called procedure be defined as DETERMINISTIC? If so, that could potentially save you some time if any of the parameters are repeated in your loop. Also, what version of Oracle are you running? If it is 11g the inlining feature should make the PL/SQL to PL/SQL call really fast. — Ollie
– Ollie, Commented Jul 26, 2011 at 15:50
Can you explain in more detail on what you are trying to achieve? If it were my choice, I would stick with one technology (pl/sql). If a problem comes up, you don't have figure out what side is causing the issue. I've seen lots of applications that use certain technologies just because someone wanted to use/learn it and it ends up causing tons of more maintenance. — Jeff Moore
– Jeff Moore, Commented Aug 3, 2011 at 19:32

mauryat · Accepted Answer · 2012-06-05 18:18:05Z

12

Any switch from one language to another will involve an overhead (it might be small but it'll still be there). If it's in a loop it will be accentuated.

Keep it simple and if you can stick to PL/SQL then do so.

Tom Kyte (Oracle Corporation Vice President and Guru) has a mantra which seems fitting to repeat here:

(Reference: http://tkyte.blogspot.com/2006/10/slow-by-slow.html)

You should do it in a single SQL statement if at all possible.
If you cannot do it in a single SQL Statement, then do it in PL/SQL.
If you cannot do it in PL/SQL, try a Java Stored Procedure.
If you cannot do it in Java, do it in a C external procedure.
If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

edited Jun 5, 2012 at 18:18

mauryat

1,6666 gold badges29 silver badges56 bronze badges

answered Aug 19, 2011 at 15:41

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java Stored Procedure Vs PL/SQL Stored Procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related