Hi, what are your thoughts on the benefits to be had from stored procs?

Im from an MS SQL background, and the benefits there are quite clear. But several places I read about MySQL Stored Procs and they warn 'there is an overhead of stored procs because some of the processing moves from the client to the server'.

I dont really understand this.
What overhead does using a precompiled stored procs involve?
Is it referring to the overhead of dynamically building the query, and if so why would this be an issue other than the first time its run (ie compiled)?

For the purposes of my question, pleae ignore the benefits of stored procs caused by reduced network traffic and all security issues. Im aware stored procs can often provoke a religious debate

Im talking purely about the caching of compiled batches, which is the performance benefit Im hoping to gain on my heavily used website

Thanks
Carpii

[Updated on: Tue, 21 November 2006 03:53]

Hi Peter. Sorry I must be a bit of a thickie, Im still not grasping what sort of data processing is being referred to.

Lets say I pass an adhoc query to MySQL.
The server recieves this from a TCPIP socket, parses and compiles the query, executes it, and returns a result set.

I would have thought that by converting this into a stored procedure, Im not placing any more data processing load on the server. Im sure what you say is correct, but I dont understand why

The server might read the cached and compiled stored proc from memory (I guess), execute it, and return a resultset.

Which bit is introducing the extra data processing, from what I can see: For the steps mySQL has to do to execute the stored proc, it has to do all those steps (plus more) for the adhoc query

Thanks
Carpii

[Updated on: Tue, 21 November 2006 12:35]