Diffrence between StoredProcedure and Prepared Statement

[Manish Jaju]

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]

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]

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]

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.

[Ilja Preuss]

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.

[Maulin Vasavada]

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

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

Regards
Maulin