File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Java in General and the fly likes Diffrence between StoredProcedure and Prepared Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Diffrence between StoredProcedure and Prepared Statement" Watch "Diffrence between StoredProcedure and Prepared Statement" New topic
Author

Diffrence between StoredProcedure and Prepared Statement

Manish Jaju
Greenhorn

Joined: Jul 21, 2004
Posts: 24
I am a bit confused about the execution of a query through StoredProcedure and PreparedStatement. Will someone flash light over these two ways and the difference between them.
Luis F Garcia
Greenhorn

Joined: Oct 20, 2004
Posts: 6
Manish:

In my experience, a PreparedStatement is an SQL statement which you write in your program and has been previously parsed by the DBMS, and is very useful when you have to repeat that statement several times changing only the values in the placeholders (question marks), because the DBMS does not have to parse it on every execution.

And a Stored Procedure is an SQL statement which lives in the DBMS, in the form of records in one or several tables. These statements are also previously parsed, and may contain placeholders.

The difference resides in your program. If you use PreparedStatement, your program must be updated with every change of the SQL statement or field definition, and if you use Stored Procedures, you only call the appropriate SP from your program, making any modification in the DBMS, sort of like maintaining the tables.

Hope this helps...

Regards
Luis F.
Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1871
also,

Stored Procedure is a "procedure" and we can write if else, for loops etc in there using PL/SQL where as Prepared Statement is a "statement" where we can't write all of those things.

Regards
Maulin
Edwin Keeton
Ranch Hand

Joined: Jul 10, 2002
Posts: 214

It depends somewhat on the database engine and the database drivers you're using, but generally speaking, both stored procedures and prepared statements are parsed, an execution plan is prepared, and are somewhere (usually called a procedure cache) in a memory pool ready to run. Once in the procedure cache, there's little, if any practical difference between a stored procedure and a prepared statement. It used to be that only stored procedures were precompiled and cached, but that's no longer true of modern database engines. (At least AFAIK).

When a SQL statement is executed, the engine looks through the procedure cache to see if an execution plan already exists for that statement. If so, it reuses that plan, otherwise it generates a new plan. It's generally always cheaper to scan for an existing plan to reuse than to simply compile every SQL statement.

Statements are aged out of the procedure cache as memory is needed elsewhere. Sophisticated databases may provide the ability to tune their aging algorithms to favor stored procs, but the algorithms seem to be based mainly on the number of times the statement is referenced and the cost of compiling the statement. Again, no definite advantage for stored procs or prepared statements.

Database drivers can also have an effect. I have heard, for example, that Oracle has tuned prepared statement calls so that they are actually (slightly) faster than stored procs (callable statements). I personally can't vouch that this is specifically true, but it's an example of the kind of things that can be true between different drivers. Except for really heavy load situations, it's not likely to make any practical difference.

There's also the obvious difference that the text of stored procs are stored in the database in some system table. This doesn't effect execution, but can add complexity to your versioning system.


SCJP, SCWCD
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Maulin Vasavada:
Stored Procedure is a "procedure" and we can write if else, for loops etc in there using PL/SQL where as Prepared Statement is a "statement" where we can't write all of those things.


Some modern databases also allow to write stored procedures in Java.

With stored procedures, you can do some preprocessing that wouldn't be possible using simple statements, such as filtering, before the data is send over the network to your actual program.


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1871
Some modern databases also allow to write stored procedures in Java.

Yeah. We can look for "Java Stored Procedures" on google.

Regards
Maulin
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Diffrence between StoredProcedure and Prepared Statement