Re: Stored Proc SLOWER than embedded sql
You're getting the results I would expect:
- Preparing is slower than not preparing: Remember, you prepare the statement *at each call*!
Using prepare inside a stored procedure is something you only do when you have to (to get around certain restrictions in the syntax). Since the stored procedure is in itself "prepared" (i.e. precompiled), there is no gain in performance when using prepared statements inside a procedure.
On the same note: If you only want a few statements (like, just one :), and don't need the other features of stored procedures (functions, encapsulation, access control, variables, flow control etc), it's probably faster to use prepare directly, e.g.:
prepare stmt from ....;
execute stmt ...;
execute stmt ...;
instead of:
create procedure p() ....;
call p();
call p();
(or possibly it's about the same in performance, I haven't actually tested).
But, as said above, there's no point in putting the "prepare stmt ..." inside p, and then call it, unless you really have to.
- Yes, user variables are slower than local variables. Always use local variables/parameters whenever possible.
And while we're talking about performance: Always use "set variable =..." when possible. :-)
(I sometimes see "select 42 into variable", which also works, but is much slower.)
pem, Senior Software Dev., MySQL AB