| Overhead of stored procs [message #375] |
Tue, 21 November 2006 03:52  |
 carpii
Messages: 48
Registered: November 2006 |
Member |
|
|
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]
|
|
|
| Re: Overhead of stored procs [message #376 is a reply to message #375 ] |
Tue, 21 November 2006 04:11  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: Overhead of stored procs [message #380 is a reply to message #376 ] |
Tue, 21 November 2006 12:32 |
carpii
Messages: 48
Registered: November 2006 |
Member |
|
|
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]
|
|
|
|
|
| Re: Overhead of stored procs [message #415 is a reply to message #402 ] |
Thu, 23 November 2006 18:22 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
As MySQL just recently got Stored Procedures this is not commonly used practice.
Normally people do abstraction in the language itself it you would get class Orders and method GetNewOrders which all application will use to talk to the database.
I know it is not exactly the same as you can't change how different actions are executed without applications but this is what MySQL Guys do.
Regarding stored procedures even if they are here the all set of tools is not yet hear to deal with them - such as profiling, debugging etc.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
