Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL stored procedures use them or not to use them

We are at the beginning of a new project, and we are really wondering if we should use stored procedures in MySQL or not.

We would use the stored procedures only to insert and update business model entities. There are several tables which represent a model entity, and we would abstract it in those stored procedures insert/update.

On the other hand, we can call insert and update from the Model layer but not in MySQL but in PHP.

In your experience, Which is the best option? advantages and disadvantages of both approaches. Which is the fastest one in terms of high performance?

PS: It is is a web project with mostly read and high performance is the most important requisite.

like image 518
Emilio Nicolás Avatar asked Jun 16 '11 08:06

Emilio Nicolás


People also ask

Should you still use stored procedures?

Stored procedures promote bad development practices, in particular they require you to violate DRY (Don't Repeat Yourself), since you have to type out the list of fields in your database table half a dozen times or more at least. This is a massive pain if you need to add a single column to your database table.

Why stored procedures are used in MySQL?

Stored procedure reduces the traffic between application and database server. Because the application has to send only the stored procedure's name and parameters instead of sending multiple SQL statements. Stored procedures are reusable and transparent to any applications. A procedure is always secure.

Does MySQL use stored procedures?

MySQL supports stored routines (procedures and functions). A stored routine is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored routine instead.


2 Answers

Unlike actual programming language code, they:

  • not portable (every db has its own version of PL/SQL. Sometimes different versions of the same database are incompatible - I've seen it!)
  • not easily testable - you need a real (dev) database instance to test them and thus unit testing their code as part of a build is virtually impossible
  • not easily updatable/releasable - you must drop/create them, ie modify the production db to release them
  • do not have library support (why write code when someone else has)
  • are not easily integratable with other technologies (try calling a web service from them)
  • they use a language about as primitive as Fortran and thus are inelegant and laborious to get useful coding done, so it is difficult to express business logic, even though typically that is what their primary purpose is
  • do not offer debugging/tracing/message-logging etc (some dbs may support this - I haven't seen it though)
  • lack a decent IDE to help with syntax and linking to other existing procedures (eg like Eclipse does for java)
  • people skilled in coding them are rarer and more expensive than app coders
  • their "high performance" is a myth, because they execute on the database server they usually increase the db server load, so using them will usually reduce your maximum transaction throughput
  • inability to efficiently share constants (normally solved by creating a table and questing it from within your procedure - very inefficient)
  • etc.

If you have a very database-specific action (eg an in-transaction action to maintain db integrity), or keep your procedures very atomic and simple, perhaps you might consider them.

Caution is advised when specifying "high performance" up front. It often leads to poor choices at the expense of good design and it will bite you much sooner than you think.

Use stored procedures at your own peril (from someone who's been there and never wants to go back). My recommendation is to avoid them like the plague.

like image 83
Bohemian Avatar answered Sep 28 '22 06:09

Bohemian


Unlike programming code, they:

  • render SQL injection attacks almost impossible (unless you are are
    constructing and executing dynamic
    SQL from within your procedures)
  • require far less data to be sent over the IPC as part of the callout
  • enable the database to far better cache plans and result sets (this is admittedly not so effective with MySQL due to its internal caching structures)
  • are easily testable in isolation (i.e. not as part of JUnit tests)
  • are portable in the sense that they allow you to use db-specific features, abstracted away behind a procedure name (in code you are stuck with generic SQL-type stuff)
  • are almost never slower than SQL called from code

but, as Bohemian says, there are plenty of cons as well (this is just by way of offering another perspectve). You'll have to perhaps benchmark before you decide what's best for you.

like image 31
davek Avatar answered Sep 28 '22 05:09

davek