MySQL stored procedures use them or not to use them

Question

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.

Solution 1

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.

Solution 2

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.

Solution 3

As for performances, they have the potential to be really performant in a future MySQL version (under SQL Server or Oracle, they are a real treat!). Yet, for all the rest... They totally blow up competition. I'll summarize:

• Security: You can give your app the EXECUTE right only, everything is fine. Your SP will insert update select ..., with no possible leak of any sort. It means global control over your model, and an enforced data security.

• Security 2: I know it's rare, but sometimes php code leaks out from the server (i.e. becomes visible to public). If it includes your queries, possible attackers know your model. This is pretty odd but I wanted to signal it anyway

• Task force: yes, creating efficient SQL SPs requires some specific resources, sometimes more expensive. But if you think you don't need these resources just because you're integrating your queries in your client... you're going to have serious problems. I'd mention the analogy of web development: it's good to separate the view from the rest because your designer can work on their own technology while the programmers can focus on programming the business layer.

• Encapsulating business layer: using stored procedures totally isolates the business where it belongs: the damn database.

• Quickly testable: one command line under your shell and your code is tested.

• Independence from the client technology: if tomorrow you'd like to switch from php to something else, no problem. Ok, just storing these SQL in a separate file would do the trick too, that's right. Also, good point in the comments about if you decide to switch sql engines, you'd have a lot of work to do. You have to have a good reason to do that anyway, because for big projects and big companies, that rarely happens (due to the cost and HR management mostly)

• Enforcing agile 3+-tier developments: if your database is not on the same server than your client code, you may have different servers but only one for the database. In that case, you don't have to upgrade any of your php servers when you need to change the SQL related code.

Ok, I think that's the most important thing I had to say on the subject. I developed in both spirits (SP vs client) and I really, really love the SP style one. I just wished Mysql had a real IDE for them because right now it's kind of a pain in the ass limited.

Solution 4

Stored procedures are good to use because they keep your queries organized and allow you to perform a batch at once. Stored procedures are normally quick in execution because they are pre-compiled, unlike queries that are compiled on every run. This has significant impact in situations where database is on a remote server; if queries are in a PHP script, there are multiple communication between the application and the database server - the query is send, executed, and result thrown back. However, if using stored procedures, it only need to send a small CALL statement instead of big, complicated queries.

It might take a while to adapt to programming a stored procedure because they have their own language and syntaxes. But once you are used to it, you'll see that your code is really clean.

In terms of performance, it might not be any significant gain if you use stored procedures or not.

Solution 5

I will let know my opinion, despite my toughts possibly are not directly related to the question.:

As in many issues, reply about using Stored Procedures or an application-layer driven solution relies on questions that will drive the overall effort:

• What you want to get.

Are you trying to do either batch operations or on-line operations? are they completely transactional? how recurrent are those operations? how heavy is the awaited workload for the database?

• What you have in order to get it.

What kind of database technology you have? What kind of infrastucture? Is your team fully trained in the database technology? Is your team better capable of building a database-aegnostic solution?

• Time for get it.

No secrets about that.

• Architecture.

Is your solution required to be distributed onto several locations? is your solution required to use remote communications? is your solution working on several database servers, or possibly using a cluster-based architecture?

• Mainteinance.

How much is the application required to change? do you have personal specifically trained for maintain the solution?

• Change Management.

Do you see your database technology will change at a short, middle, long time? do you see will be required to migrate the solution frequently?

• Cost

How much will cost to implement that solution using one or another strategy?

The overall of those points will drive the answer. So you have to care each of this points when making a decision about using or not any strategy. There are cases where using of stored procedures are better than application-layer managed queries, and others when, conducting queries and using an application-layer based solution is best.

Using of stored procedures tends to be more addequate when:

1. Your database technology isn't provided to change at a short time.
2. Your database technology can handle parallelized operations, table partitions or anything else strategy for divide the workload onto several processors, memory and resources (clustering, grid).
3. Your database technology is fully integrated with the stored proceduce definition language, that is, support is inside the database engine.
4. You have a development team who aren't afraid about using a procedural language (3rd. Generation language) for getting a result.
5. Operations you wanna achieve are built-in or supported inside the database (Exporting to XML data, managing data integrity and coherence appropiately with triggers, scheduled operations, etc).
6. Portability isn't an important issue and you do not whatch a technology change at a short time into your organization, even, it is not desirable. Generally, portability is seen like a milestone by the application-driven and layered-oriented developers. From my point of view, portability isn't an issue when your application isn't required to be deployed for several platforms, less when there are no reasons for making a technology change, or the effort for migrating all the organizational data is higher than the benefit for making a change. What you can win by using an application-layer driven approach (portability) you can loose in performance and value obtained from your database (Why to spend thousands of dollars for to get a Ferrari that you'll drive no more than 60 milles/hr?).
7. Performance is an issue. First: In several cases, you can achieve better results by using a single stored procedure call than multiple requests for data from another application. Moreover, some characteristics you need to perform may be built-in your database and its use less expensive in terms of workload. When you use an application-layer driven solution you have to take in account the cost associated to make database connections, making calls to the database, network traffic, data wrapping (i.e., using either Java or .NET, there is an implicit cost when using JDBC/ADO.NET calls as you have to wrap your data into objects that represents the database data, so instantiation has an associated cost in terms of processing, memory, and network when data comes from and goes to outside).

Using of application-layer driven solutions tends to be more addequate when:

1. Portability is an important issue.
2. Application will be deployed onto several locations with only one or few database repositories.
3. Your application will use heavy business-oriented rules, that need to be agnostic of the underlying database technology.
4. You have in mind to do change technology providers based on market tendencies and budget.
5. Your database isn't fully integrated with the stored procedure language that calls to the database.
6. Your database capabilities are limited and your requirement goes beyond what you can achieve with your database technology.
7. Your application can support the penalty inherent to external calls, is more transactional-based with business-specific rules and has to abstract the database model onto a business model for the users.
8. Parallelizing database operations isn't important, moreover, your database has not parallelization capabilities.
9. You have a development team which is not well-trained onto the database technology and is better productive by using an application-driven based technology.

Hope this may help to anyone asking himself/herself what is better to use.

View more solutions

Comments

[Emilio Nicolás]

Thanks for your answer. I have explained in which cases we would use them. For be even more specific, for example, we have a user entity, that is represented in three vertically partitioned tables. The PHP code would call the procedure of insertion or update, and the only responsability of those procedures is to insert/update information demmanded in the right table. Do you thinki is correct/useful/recommended to use them in this specific case?

[BerggreenDK]

"require far less data to be sent over the wire as part of the callout" ? please explain that POV, I agree that a SQL query can be long, if badly written or it performs too much in one query, but I believe the amount of data transfered back is exactly the same as the result ought to be the same result? so we are speaking about the difference of few hundred bytes pr. DB call in difference. Not "far less" in my mind where we use broadband. Less is true, but "far less" is over-exaggerated/subject.

[davek]

Yes, I mean the callout, not what's returned: typically you would issue a call like "call myProc(x,y,z)" which can be far less data than an intricate SQL query. And depending on your mileage that can add up.

[MarkR]

I disagree: 1. You can keep your queries organised without stored procs. 2. You can perform a query batch without stored procedures; 3. Stored procedures are not pre-compiled in MySQL. 4. You can performa a query batch without stored procedures (again).

[MarkR]

1. No, SQL injection attacks are not impossible, because in practice developers often use dynamically prepared queries. Parameterised queries can be used without Sprocs. 2. less data across the wire? Trivially perhaps, but hardly any difference in practice. 3. MySQL does not precompile or cache plans in sprocs. 4. YES they are testable in isolation, but if you factored your queries into a testable unit on the client, they would be too.

[Abhay]

@MarkR: I agree but I didn't mean to say that there are no ways other than stored procedure to organize your queries or run batch queries. I think I didn't know that MySQL stored procedures are not pre-compiled, that's weird but thanks for that bit.

[MarkR]

This post is very confused: 1. It confuses DWH with OLAP workloads. 2. It is highly opinionated 3. It makes arguments about performance with no evidence or citation. 4. It makes other arguments with no rationale (e.g. "SP will definitely benefit" without saying how). Also the poster has clearly never worked on a real development team for an application which used stored procedures- the procedures do NOT in practice, get maintained by any magical "database team", it is up to them.

[R. van Twisk]

Mark, I would say these are personal experiences. I do work in a real development team and I do have both MySQL and PostgreSQL experiences with larger databases. We use stored procedures within our team with great success and that helps to work on complex databases.

[Bohemian]

@EmilioNicolás No I would NOT recommend using them in this case. It is an illusion to believe it is "good design" or it will "make your code cleaner". Although it seems like a good idea, it is not. If you want to encapsulate that action, create a PHP function that does it and call that. Believe me, you will regret it if you go down the stored procedure road if you don't absolutely need to.

[Emilio Nicolás]

Two years from the begining of the project. Finally, I have realised the bad decision we made encapsulating a little data base functionallity in the store procedures. Never again :-). It's not as bad to change it becouse is very little, but in a future project the database strictly will save the data. I mark now this answer as the good one for future readers.

[Sebas]

As it is correct to say SP are specific and hence not portable, I hope that an IT dpt building an entire DB package is certain of not changing that RDBMS tomorrow morning...

[Sebas]

@EmilioNicolás I'm certain this is because you had a bad architecture to begin with. I can say that after 3 years I'm even more in love with one of my SP based systems.

[Pacerier]

@Bohemian, When you need to update several tables in a transaction, wouldn't you use stored procedures?

[Pacerier]

@davek, When you say "are almost never slower than SQL called from code", what does this "code" refer to? Do you mean the application layer like PHP etc?

[Pacerier]

Regarding "Independance from the client: if tomorrow you'd like to switch from php to something else, no problem.", what if it's the DB layer that needs changing ?

[Pacerier]

@ries, Joins with over 10 tables are not rare actually, even for normal applications (assuming db is structured for high normalization). Btw what do you mean by "true warehousing"? Do you have some examples?

[Pacerier]

How did you get -2?

[Martijn]

I'd add some more reasons not to use stored procedures: The are not easily deployed using standard deployment tools and are als not easily managed by most version control systems.

[Bohemian]

@pacerier no way. Modern persistence frameworks (eg hibernate) take care of everything - you don't even need to write any SQL. There is no benefit, and consuderable downside, to using a SP because you are updating multiple tables - the downside being writing, testing, maintaining, deploying an SP vs writing no database code whatsoever. Doing it for "performance" reasons is also not a reason - it may actually be slower, but at least I would not expect the SP to be significantly faster, not enough to warrant all the development headaches a consisted with SPs

[Pacerier]

@Bohemian, Just ask a proper DBA worth his salt, rest assured that ~"Don't need to write SQL" is a tried and true myth. It's good for small apps and fast development, yet DB-independent persistence frameworks only offer a small subset of the features the database offer. You have zero flexibility to tune the queries.

[Bohemian]

@pacerier the myth is that you need, or even should, use "all the features of the db". It's not a myth that you can avoid writing queries. The project I'm on now doesn't, and we follow best practices. Your comment that it only works for small apps may be the case, but the landscape has changed in the last few years: It's best practice for all apps to be small (google micro services). FYI I used to be a DBA, and a good one. The thing is, when all you have is a hammer, everything looks like a nail - ask a DBA to do some coding and you get a stored procedure, which is using the wrong tool

[Pacerier]

@Bohemian, "Best practices" is not to use ORM unless you are prepared to sacrifice database speed for dev speed. Microservices is simply a restatement of separation of concerns, and owning just a single concern does not give you the excuse to be slow. Indeed, it's best practice for all apps to be as small as possible and fast. Also, a single concern can be huge, just like a single authentication mechanism for your users all located in a global namespace which does nothing besides authentication.

[Bohemian]

@Pacerier the only good reason to SPs is "performance" (which I doubt is significant anyway); so to design a system from the start to use stored procedures is premature optimization, which is bad design. Simple CRUD is very fast (a millisecond or so) out of the box (no coded SQL). If you have a specific need for a query, code/tune the query (I had such a situation recently and the result was 7ms for a complex query, way within SLA) and deploy it as part of the app. Wrapping a query in a SP gives little if any actual performance benefit, but makes deployment and maintenance a nightmare.

[Pacerier]

@Abhay, Shouldn't you remove that misleading information regarding the precompilation then?

[Pacerier]

@Bohemian, We're talking about a mature system on the web, not a pre-alpha prototype where dev speed weighs over everything else. Performance is a feature and it is the reason to use SP. Deployment and maintenance is not such a huge nightmare as you described, It's raw data in and raw data out, things with nothing to do with raw data is not going to be coded in the SP. The so-called "nightmare" primitive language is localized to a very specific area and can be tackled in a wieldy way.

[Rakesh Waghela]

This is the sane answer, accepted answer is correct too but this one feels like a wisdom of an experienced person

[HonoredMule]

Stored procedures are the poor man's microservice architecture. Having multiple applications directly access the same database is the real root problem, equally solved by stored procedures or a middle tier access layer. If you accept that, then just ask yourself which you'd rather build and maintain - and I'll refer you back to the accepted answer.

[chuckjones242]

That iterative process you mentioned is what I see a lot of in application logic. Not realizing you can do the same in MySQL and making for each loops in apps that are just slow and unnecessary.

[Neil Davis]

Other reasons -to- use stored procedures include strong typing, and input size limitations on input variables. If you have an integer and a date parameter on a procedure, and someone tries to inject SQL using either input variable it will fail out of hand. As well if your web server gets compromised in some way, the attacker can't run ad hoc queries if the web server db user only has execute permissions. There are some pretty lame arguments in this page against stored procs, the worst of which is "because it's hard". Toss performance out the window and there still good reasons to use them.

[Brian]

Two things, @MarkR. 1.Davek said "almost impossible". 2. The "trivial" difference in the amount of data across the wire depends on size of the queries and amount of data returned.

[Brian]

@davek you neglected to mention the improved design flexibility that they provide to the DBA staff. If the only access allowed to the underlying tables is views and stored procedures, then the underlying structures can be modified without affecting application code.

[Brian]

"Task force" is an illusion - writing efficient SQL code requires specific skills, irrespective of whether that code is written in stored procedures or application code.

[Abhinav]

I don't think that your answer anyhow helps the user to resolve the query.

[Bohemian]

There are some problems with your statements: 1) business logic absolutely does not belong in the database, whose purpose is only to save/retrieve data. Using SP's turns your DB into an app server + db Swiss Army knife (which is bad). 2) "one command line under your shell and your code is tested". Not true. Firstly, only automated tests run on build servers count as actual tests. Secondly, if you do that, you must Install and forever maintain a DB for the build server and have your build deploy your new SP's to it to test your SP code, which is more complicated than you might think.