Skip to Main Content
  • Questions

  • Stored procedures vs Java Prepared statements

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dilip.

Asked: May 31, 2007 - 11:53 am UTC

Last updated: March 28, 2012 - 8:51 am UTC

Version: 9i

Viewed 10K+ times! This question is

You Asked

Hi Tom
I am a Java programmer. I have been working on Oracle for close to a year now. Big fan of your site and the work that you r doing!
One fight has always been going on between java and Oracle communities on which is better Stored procedures or Java Prepared statements.
Focusing purely on performance (Ignoring scalability, programmer screw ups ....etc) how can i prove that Stored procedures actually performs better. Thats something i have not been able to find on the internet yet.
every time i search i get conflicting web sites one supporting Stored procedures and next supporting Prepared statements.
Please help me out!

and Tom said...

this is like asking "which is better, sql or sql"

a java prepared statement is not something you compare to plsql stored procedures. Heck a java prepared statement could in fact BE a call to a plsql stored procedure.

How can you say "focusing purely on performance (ignoring scalability)". Scalability *is* performance!!!

seems obvious that:

a) insert
b) update
c) delete

from a client - with the round trips and the extra statements to parse and their round trips etc.... is less performant than:

a) proc()

that does insert, update, delete in the server... :)


stored procedures are not entirely about performance - but they sure help. Java guy sticks poorly written query into java program. DBA is told "database performance is your problem, fix it, wave a wand, make it go fast". Well, that ain't going to happen. Put poorly written query into plsql and DBA can actually fix it, fix it right up, right now, right then - FOR EVERYONE.

there is one obvious "performance" benefit.

But I will never forget the OTHER THINGS, as they are just as important.

the dependency mechanism you get with plsql (and don't with java)
the strong type linkage between plsql and sql
the ultimate in code reuse, the likes you will NEVER SEE in any other environment you get with plsql.
the ability to identify and fix things
the ability to have someone that can spell database - generate the database code, ensure transactions are transactional and all.

they all carry weight.

so to you I say "forget performance, think about all of the other things".

and realize that you cannot compare prepared statements to stored procedures, it doesn't even MAKE SENSE. I don't know what you would compare.

You can compare the logic behind

a) putting all SQL into a client application, locking it up, making it unavailable to lots of other stuff. Making it hard to fix and tune. Making it impossible for a database person to fix a thing.

versus

b) putting it into nice modular stored procedures that represent your transactions. that can be infinitely reused, maintained, tuned, fixed, whatever - easily - by a developer, by a DBA, by someone from the outside.


and the fact you get some nice features like cursor caching, the inability to screw up binding SQL, dependency tracking between your code and schema objects, native datatype support....... and so on and so on and so on...

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Clarification

Dilip, June 01, 2007 - 4:00 am UTC

Hi Tom
Think you misunderstood my intentions a bit.
I know for a fact that putting queries in hand of a DBA will result in better efficient queries. I know that java programmers can screw up SQLs. I know its easier with Stored procedures to change stuff in a live project environment.

My question is plain and simple (at least i think it is :-) ).
If i execute an efficient query from java will it be slower than executing the same query(or equally efficient) using a stored procedure. Sorry if i am not being very clear.
Tom Kyte
June 01, 2007 - 12:04 pm UTC

I didn't misunderstand you, I don't like the question. For the partial out of context answer would give people the motivation to say "ah hah, just do it in java, see Tom said so".

But I won't, I can't, and they shouldn't.

The answer: it depends.

(anyone see that coming??? :)

are you executing the query, processing the rows and doing something else database wise with them?

for x in ( select .... )
loop
   something
   something database'ish
end loop


High probability that PLSQL would excel performance wise over a CLIENT APPLICATION (any of them, don't care what language you pick).

You lose the round trips.
You have datatype nirvana.
You have all I wrote about above.



If you just run a query and print the results on a screen, then the runtime performance difference between:

a) a CLIENT APPLICATION opening it's own cursor
vs
b) a CLIENT APPLICATION getting a cursor opened by plsql

would be not measurable. For you see, you have a client getting a cursor, a cursor is a cursor is a cursor.


so, in closing, I can find lots of reasons NOT to put select, insert, update, delete in a client application.

I am hard pressed to find any reasons NOT to put them in stored procedures.

How about you?


I refuse - absolutely and adamantly - to make this about a single query performance. It just gives fodder for the wrong people to come to the wrong conclusion.

obvious

Sokrates, June 01, 2007 - 5:25 am UTC

great answer !

and it is so *OBVIOUS* that I don't understand why
we (the database guys) have always to fight trying
to convince programmers (java, c#, ...) of the advantages
of b) over a) !!


Choices

Keith McAlister, June 01, 2007 - 6:59 am UTC

You have choices to make and they are influenced by your environment and experience.

Operating as close to the data as in a language which avoides type conversons for database objects will run fastest.

If you are remote from the data, then network latency + throughput issues will slow things down. Expect to loose in the region of 10ms in latency plus another 50 or 60ms in transit time for your data, which is significant - it could double the submit to result times. Your milage will vary:))

If you are doing simple SQL statements then Java will only add overhead. (Type conversion, etc). If you have more complex things to do that Java libraries (or your application)supports then Java stored procedures are viable and get as close the data as possible.

If you are working in a Java environment and the policy is to put all business logic in mid tier, you might have a hard time selling PL/SQL stored procedures. Be pragmatic: if it runs OK leave it alone, if it's a dog then fix it and look at Java (JDBC) calling stored procedures.

You did'nt expect it to be black/white did you?

SQL = SQL

John Flack, June 01, 2007 - 9:31 am UTC

I think Tom's response was quite clear. If you execute the same SQL from a stored procedure as from a JDBC call against the same database, the performance of that command will be identical. The database doesn't care what client executed the SQL.

The performance difference will be in what happens AROUND the SQL command. If the command is a query, what will you do with the result set? If you are going to use it to drive further database activity, you'll probably get better performance by keeping it in PL/SQL close to the database, because you will reduce communication between the server and the client. If you are going to display the data to the user, you may be okay issuing the query from JDBC. However, I am presenting a paper at ODTUG Kaleidoscope this June where I suggest that there are advantages to doing the query in a stored procedure and returning a REF Cursor to the Java program. I echo some of Tom's points in my paper.

People = People

Stew Ashton, June 02, 2007 - 5:38 am UTC


The tools don't mean as much as the people who use them. Just yesterday I audited some PL/SQL code: I found 545 cases of
execute immediate 'select columns from a_table where some_key = '
|| input_parameter into some_variable;
or equivalent INSERT and UPDATE statements. How 'bout that for performance, scalability, dependency mechanisms and strong type linkage?

The same day a Java project complained that a query was too slow: the query was returning 36,000 lines of 44 columns, and the application was displaying 4 columns of sub-totals. Will it help to wrap that query in a stored procedure? No: the answer is to think about the requirement and ask the database for what the application really needs "right now" without trying to cache huge chunks of the database in Java memory.

If the question is "which is better, a perfect application with SQL embedded in Java or a perfect application with SQL embedded in PL/SQL?", the only possible answer is: "who cares, the application is perfect!"

If the question is "how can I pretend the database isn't there, yet blame it when my application goes down the tubes?", then the answer is to use (and abuse) an object-relational mapping framework like Hibernate.

Now suppose you have a developer who just wants to do his job right and has an open mind. He can start with Java Prepared Statements, but he will one day find that some requirements are much better served with PL/SQL. Once he takes that step, he may start to understand the advantages that Tom listed above and want to generalize the use of PL/SQL.

embeded vs dynamic

A reader, November 28, 2007 - 3:21 pm UTC

Would you agree that an embeded sql statement (jave or others) has the same db performance as executing a dynamic sql in plsql? This is a question from one of our Java developers.

Tom Kyte
November 29, 2007 - 8:11 am UTC

all SQL is Oracle is dynamic sql

even static sql is dynamic sql - the programming API (plsql, pro*c, sqlj) fakes us out and makes us believe it is "static", all they are doing is executing it dynamically for us.

so yes, it matters not what environment submits the sql statement.


it does matter greatly how the environment deals with the sql statement - bulk fetching (like plsql does for us) cursor caching (which plsql does for us) binding the correct datatypes naturally to avoid conversions and errors (which plsql does for us)...

Tom is missing one aspect

BillR, March 28, 2012 - 12:05 am UTC

If you have a really busy database, overloading it with stored procedures will hurt performance. There is only one database. It can be clustered/hot backup etc. but really it is one active set of disks at a time. So if you start putting processing horsepower on the database server that can be done on an application server you can hurt everything if it starts to bog down the database. And yes, I HAVE worked on systems where there is that much work going on. And upgrading the db boxes was not an option as they were running HP superdomes already.

There are many times where updates are not just simple updates and there is some procedural logic involved. If there is more than just a little, then it can be (given a minimum volume of transactions going on) beneficial to do the work on an app server since you can add more of those and can't add more database servers (as mentioned earlier). Oracle has some good facilities built into PRO*C and lower level libraries that allow you to move large blocks of data into into memory for processing and then can throw it back to the server in one go to streamline things that way.

In any case, where I was at, the database was already working too hard to allow us to put more than a relatively small number of essential stored procs on it.

I do agree whole-heartedly that quite often database performance is messed up by app developers who can't or usually don't want to learn how to use a database properly or write efficient sql... or insist that "ORM is all you need, you don't need to know how to program sql." But education (enforced if needed) is the better solution there. Going around them as a solution is just not a good practice for an organization. Education is far better.

In the end, if you can use prepared statements use them. If you can use stored procs, use those if you think they will help. But in general if your database is really busy, it is better to eat a little network lag and use the prepared statements.
Tom Kyte
March 28, 2012 - 8:51 am UTC

if you have a really busy database, and you want it to be less busy, you will demand that developers only use stored procedures and never issue select/insert/update/delete/merge from the client side.


The tendencies to overwhelm a database will billions of stupid, tiny, repetitive bits of sql - which *kills* the database - is there when they do not use stored procedures.

when using stored procedures, at least you have a chance of correcting this, using bulk sql, doing things like *joins* etc.


what do DISKS have to do with anything in this discussion? In the best of worlds - the disk IO would be identical. In the worst of worlds, the disk IO is orders of magnitude larger with client code (no stored procedures) because of the slow by slow mentality. how does *processing* (CPU) impact *disk*? that doesn't make any sense.


I disagree with your premise entirely. I didn't miss anything, in my view - you are.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library