Overhead of stored procs

[carpii]

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

[Peter]

It is the same for any SQL Database - if you use stored procedures you’re moving data processing from your client to your server, unless your stored procedures do simple bunch of queries of course in which case server load actually can be faster due to reduced network and query parsing.

CPU time on the clients are usually cheaper than on server so it is often best if you keep complicated data processing there.

[carpii]

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