posted 20 years ago
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.