MySQL Stored Procedures: Advantages and Disadvantages

Question

MySQL

In the current project, we actively (already about 10 thousand lines of procedures) use mySQL stored procedures. This is a game service with non-trivial business logic. Using procedures, we significantly reduce the number of queries to the database. But recently the question arose of the profitability of their use. Queries are not cached if they use user variables ( http://www.mysql.ru/docs/man/Query_Cache_How.html ). Thus, using procedures, we significantly reduce the number of cached requests. At the moment we have more than a little over 50% of all requests in the cache. And the question arises: do we harm the project or vice versa? It turns out that stored procedures along with pluses have one minus.

Who has any experience with mySQL stored procedures and thoughts on this?

Answer 1

applications with stored procedures are more difficult to maintain, because logic is messed up.
it is impossible to use normal caching and there will be problems with scaling. the best request is the one that is not made, and this requires a smart cache.

Answer 2

My, not authoritative, opinion:
Stored procedures are the transfer of part of the logic to the database. In some cases, there may be some performance increase, but on average, we can say that the difference is insignificant. Another question is that this logic will be performed by the db server, and not the application server, sometimes this is important.
By the way, I noticed an ambiguous phrase from you, because, just in case: it will not work to measure the load by the number of requests, because there are requests for microseconds, and there are complex ones that can take several days to complete, not to mention the fact that in order to receive one and For the same data, multiple queries may be more efficient than one.
So stored procedures are mostly for the sake of convenience. In websites and web applications, I tried to do everything on the procedures, but in the end I almost completely abandoned them, after several improvements, everything is no longer so beautiful. Now I use it only in rare, specific cases, and mainly in conjunction with triggers.

Answer 3

My experience with stored procedures for MySQL is not great (in MSSQL there is more, but as a user of servers I don’t see a big fundamental difference), so I just want to share my thoughts. It doesn't matter whether you write procedures in C++, JavaScript or SQL, but if you have several technologies in a project, then:
1. Each procedure must be written in its own technology.
2. Isolate technologies as much as possible from each other (ideally, only the transfer of parameters and results) in order to keep the design under control.
3. Look for methods for numerical evaluation of the performance of various stages of programming. This is a separate task.
Maybe everything is going well for you, and you are just afraid that something will go wrong?

Answer 4

Stored procedures and triggers are poorly supported by generic ORMs. Apparently due to large differences in different RDBMS. That's when (if?) They learn to translate DSL models into a specific SQL dialect and synchronize changes with the database (and for "primitive" models, implement them in a "normal" PL), then they will gain a second wind.

Answer 5

hmm ... the use of stored procedures in the database, where and how horrible is good.
We had one "architect" who came to the WEB from the Enterprise App, somehow in one project we added 30% of the logic to the stored files. As a result, the MySQL server ate a lot of resources, constant deadlock ... it is very difficult to debug, I had to write 15-20% of the code, to trace the execution of the storages ...
well, it became slower and slower. When I told Muskul's developers about this, they laughed at building such an architecture for a long time ...
So, if you use storage, then only in the simplest cases,
Muskul is not Oracle for you, even though he is now his little brother ...
in general, I would say that the project was canceled, although it was closed due to funding (there was a crisis) ...
but the architect still turned out to be a fool, although he wanted to do the best