Do You Use Stored Procedures In MySQL or MS SQL or Any Other Database?

We provide remote DBA consulting services and sometimes we need to interview some dot net or Alpha AnyWhere developers and one of the questions is:

Do you use stored procedures?

Some developers said, we don’t use it. Too much of a hassle. We would rather use one development tool to encapsulate all in one set of code.

I was interviewing a dot net developer for a web scraping project and I asked him, if he can show me what stored procedures he worked lately. I was amazed to realize that the developer had difficulties to identify different features in MS SQL management studio and basically does not work with any SP. He said all it is done within the entity framework and LinQ which is part of dot net.

It seems that many developers are losing touch with reality these days and forget what is the back end is all about.

See another article I wrote before about a similar topic: Can you optimize my ms sql or mysql database?

I asked some questions different developers as colleague and here are some explanations I got from them:

Answer 1:

The most resource-effective way, and that is undoubtedly stored procedures:

When caring about performance, the best approach is to do things in the most resource-effective way, and that is undoubtedly stored procedures, which can be optimized much better than any other structure ever will. But, playing the devil's advocate, let me say that things can easily get out of hand if you base much of your business logic on stored procedures. I've faced that in a very large project in the past where performance was key and almost everything was done using SPs. They ended up being huge monsters - encapsulating a large part of the business logic. Hard to debug, hard to code, hard to track changes. I think you must always sacrifice one thing in favor of another - and in the case of SPs this is speed over a clearer business logic tier.

Answer 2:

Business logic gets often encapsulated in stored procedures:

At least based on my own experience, business logic gets often encapsulated in stored procedures. For example, let's suppose you have a stored procedure that decides on whether to insert the data provided to one series of tables or another based on queries it does in the database. It's very easy to be tricked there and encapsulate the decision and the actual data insertion inside a single stored procedure or a chain of SPs (for speed).While in reality there should be a business logic tier there making the decision. I've seen this hundreds of times.

Answer 3:

Of-course you don't have to put business logic into an SP:

Of-course you don't have to put business logic into an SP. I think, though, that many developers get discouraged on using SPs exactly because of that.

Because it's easier for them to even SELECT things writing LINQ queries, which is fast to write but sometimes really slow in execution, especially when complex lambdas are involved. All I say is that a balance must be found.

Answer 4:

Many developers feel that database code is not a real code:

Many developers feel that database code is not "real" code. I've seen countless companies with source control systems properly tracking every app code commit. Meanwhile sprawling databases are manually updated by the developers themselves, or perhaps the DBA (for fancier situations), based on a script that never even gets reviewed.

So why would I create a stored procedure in such an environment? It's not going to get tracked, and anyway SQL is really just an artifact of the app code.

Answer 5:

For most purposes there is not much difference between stored procedures and SQL query sent from a server:

Usually the problem is deployment. Unlike the tables themselves, stored procedures tend to change frequently, this means that with every deployment of the software the database also has to be updated simultaneously.

If you don't have a deployment automation process, either because you are primitive or because you just don't need one, this can become a massive headache and a source for errors.

For most purposes there is not much difference between stored procedures and SQL query sent from a server, especially if you properly parameterize your queries. On the other hand, changes in the app code are maintained by a source control and deployed with the rest of the code.

On top of that, the function responsible for serialization/mapping of the result set into objects is usually in your app's code, which means when you are making a change you need to remember to update 2 separate places even though it’s part of the same functionality. Also debugging becomes a real pain in the behind. There are some advantages to stored procedures of course but it’s very far from being an obvious trade off.

Answer 6:

Stored procedures can be stored in source control, and can be deployed from there:

Stored procedures can be stored in source control, and can be deployed from there. You can have database builds, code verification, etc.

Just like you do with C# or any other language. Also, if you don't have source control and deployment automation you will have a lot of problems no matter what.

Hiding T-SQL inside your app code isn't going to magically cure that. I can hear the argument about convenience, to some extent, but not too much. After all, I can make that argument about all functionality and then I'm back to spaghetti code.

Why with other languages we accept additional maintenance that goes into code separation, and here we don't? Finally, the idea that debugging stored procedures is harder is just a myth. Most inline code can not debugged at all. Stored procedures, on the other hand, can be set-up for easy debugging.

Of-course after some time these companies need to call in a consultant to help unravel their mess, which is why I've had the pleasure of seeing them. Good times for all involved, when we pretend that a key part of our system is second-class garbage.

Check our SQL Consulting services:

For more info about our MySQL and MS SQL consulting services we provide please call us: 732-536-4765 or send us an email via our Contact Us page.