Who Needs Stored Procedures, Anyways?

I have a stored procedure that validates user logins and was wondering whether this was the best approach. The sproc is called from my middle tier and performs some of the following functions depending on whether the user is valid or invalid:

  1. checks to see if the user exists in the user table
  2. logs the login attempt to a log table
  3. logs the user IP to a log table
  4. checks to see if the user IP or username is banned
  5. checks to see if the user status is valid i.e active user vs not verified etc…
  6. updates the user record setting last login date to current datetime
  7. returns invalid user or a valid user record including role, status etc…

The sproc performs multiple functions and I only need to make one call from the middle tier into the database. The application is subject to heavy load, 1000’s of concurrent users and is transaction intensive.

How could this best be achieved using non sproc methods without incurring multiple calls into the database from the application layer ?

Thanks in advance

f00

Anyone who has worked on enterprise level software would understand the benefits of sps over ad-hoc queries.

  1. Using f00’s user validation example, would you rather call
    "uspValidateUser ‘userId’" or would you rather send the whole query each time you need to validate a user? It could a difference of couple of bytes sent to the server or a couple of hundred Kilobytes PER validation.

  2. Do you want to expose the query and its inner workings over the network or would you rather send “uspValidateUser ‘userId’”.

  3. You can deny all permissions to a login and just allow the logins to execute certain sps. This simplifies security immensely. If you allow ad-hoc queries in the validation example, you must give that user access to the underlying tables. There may be columns that you do not want the user/developers to see. How would you prevent this? Column level permissions? Give me a break. Using views? This requires additional performance or space penalties depending on whether it’s an indexed view or not.

  4. If you were to send ad-hoc queries for multi-step transaction processes, such as f00’s validation sp, you’d need to use a transaction for the duration of the connection as you process each step with ad-hoc queries. If it is a 5 step process, you are making at least 5 trips. With the sp, you only need a transaction within the sp, which is for the during of the session. This is orders of magnitude better in terms of blocking, performance, security and simplicity.

  5. How can people say that managing schema changes are easier with ad-hoc queries than with sps. Let’s say there is a change to a table that is accessed by uspValidateUser. If there are many groups( web group, java group, .Net group ) that use uspValidateUser, just changing uspValidateUser would suffice. If all these groups issued ad-hoc queries, each would have to search through their code and update/test.
    Also, many schema changes, such as table or column name changes would not affect the developer if sps are used because the changes can be managed at the sp level. If you use ad-hoc queries, it would involve having to update the ad-hoc queries and compiling the app if it is an app.

  6. As for migrating to different servers, it’s infinitely simpler having the DBAs update the sps and developers update the DAL. This way the developers only have to worry about accessing the new servers because the sps should be the same on the new servers. You don’t have to worry about how the underlying objects( tables/columns/functions/views/etc) are implemented.

Having been through stints as a C++ developer of enterprise software and a DBA of enterprise level rdbms, there is no doubt in my mind that having sps as an interface for raw data access provides more flexibility, performance and security than using ad-hoc queries. Also, sps fit nicely into the N-tier development model. Don’t say that SQL is “archaic”, it makes you sound ignorant. Is assembly language archaic, what about interpretive languages? Is Scheme and other procedural languages archaic? Are markup-language archaic? SQL is a specialized language for a specialized task. The only reason we are having this discussion is solely due to incompetance and ignorance. If you are unable or unwilling to spend the time to learn SQL properly, you shouldn’t be a developer ( especially of data-driven software ). If you are like the 75% of “developers” out there who finished an online PHP tutorial and fancy himself an HTML “hacker”, then go ahead and keep using those ad-hoc queries to your hearts content.

It seems to me that instead of fighting over one or the other we should be able to come to a common agreement: there are pros and cons to either side and there isn’t a simple answer. Each one of us has different goals, viewpoints, experiences, skill sets, likes/dislikes, deadlines, ROI, etc. which can lean it one way or the other. If all the pros were on one side, we wouldn’t be having this discussion. Having said that, I think these discussions are good so that we can make informed decisions.

I think the closest we can ever come to a common conclusion is a decision tree, but even then there will be differences of opinion. Here are some ideas to prove my point and get you started. I flattened the tree for this post. I am by no means suggesting I know it all, so bear with me.

  1. Are you creating simple throw-away software? Are you sure? Use whatever is fastest to develop (RAD, DataSet, no n-tier/n-layer whatever).
    1a. Does the throw-away software run for a long time (batch) and needs to perform better? Consider optimizing your code, using faster classes (no DataSet but rather DataReader, custom class…), less recursive functions, etc.
    1b. Will you be working with lots of data where memory could be a problem? Process one piece at a time.

  2. Are you creating a LOB app for a small amount of users? Are you sure it won’t need to scale soon? Focus more on coding/maintenance and not so much on performance. An ORM, dynamic SQL, would probably work fine.

  3. Are you creating a website for a large amount of users? Consider using stored procedures to reduce roundtrips and the amount of data that goes back and forth.

  4. Are you creating a website for a VERY large amount of users?
    4a. Is it mostly used as read-only (e.g. news site)? Consider using application caching to reduce database load. Consider having one write-only database and replicate to read-only databases.
    4b. Is it mostly used for writing? It’s easier to scale a web farm than a database, so consider not using stored procedures, triggers, etc.

  5. Is there a real chance you will want to switch databases?
    5a. Do you need it to perform well? Consider using stored procedures to tune the queries for each database type. This will require more work.
    5b. Otherwise, keep everything in a DAL/BLL to reduce dependency on database type.

  6. Will you be accessing the business logic from different languages (C#, Java, etc.)?
    6a. Do you want to go through the trouble of creating a web service with the pros/cons it has? Would all systems go through the web service? Keep your business logic outside of the DB and in your code.
    6b. Do you want to flatten some layers and keep it simple? Put your business logic in the DB.
    6c. Will it need to scale a lot (#4)? Reconsider the web service. You may need a web service farm as well.

  7. Will the business logic always be accessed from the same language, but several applications, websites, etc.? Keep it in the BLL.

  8. Do you have expert DBAs, able/willing to make changes as needed when needed?
    8a. Are the DBAs not that expensive and/or you need performance and you’re not scaling to extremes (#4)? Consider using the database more.

  9. Do you only have programmers who don’t understand SQL? Consider getting DBAs or keep more out of the database, use an ORM, etc.

  10. Did you decide to not use stored procedures? Do you have a process that requires a lot of back/forth to the DB that could really be optimized with a stored procedure? Consider using a stored procedure anyway in this case.

Note that some questions can go back/override some of the previous “conclusions”. I know it’s not accurate or complete (could it ever be?) and I’m sure many of you could add and improve on this, but that’s not the point. The point is there are a zillion variables and there is no “silver bullet” or “one size fits all”. Yes, sometimes one way of doing things is usually better, but there is generally at least one extreme/edge circumstance that overrides “usually better”. Other things are closer to middle ground and generally start “religious” wars such as this.

Choose the right tool for the task and good luck with deciding what the right tool is!

Just to clarify, it’s a very complex decision and I completely skipped over some parts such as security considerations. :slight_smile:

Anti-SPROC developers must be writing the equivalent of “See spot run” programs. It’s a rare developer indeed that knows what they are talking about when it comes to databases. 100 million record+ systems that have dozens of applications written against them better wall off “programmers” from the database with SPROCS or face a world of woe. Every idiot thinks he can write SQL or worse yet relies on ORM to do the idiot work for them. Do your work and let the people who know do theirs.

This is a great article (at least if you read through all the comments), and as mentioned before, it all depends on the requirements. All in favor of SP’s seem to only be on 1 DBMS, and in that case I would propably agree, but what about a retail package which must support more DBMSses because the client already has Oracle or MS SQL Server and doesn’t want another DBMS on their network, or isn’t willing to pay for a license and wants to resort to MySQL? What if you don’t have the ‘direct’ control of the database and everything has to go through an updateapplication.
I’ve been reading this article because we are starting up a new projec which must at least be able to support MS-SQL Server, Oracle and MySQL, as it all depends on the client. After reading this whole article I get the impression that SP’s aren’t the way to go for this kind of project, unless we are willing to invest a lot of time on having to spend updating/maintaining all those SP’s.

But as said before, it all depends on what your needs are, and budget/time available…

Hate to bump this topic, however this was 4 years ago and I want to see what people think about stored procedures in Microsoft Sql Server with the advent of SSDT and Visual Studio SP debugging support?

With SSDT you can manage your entire MSSQL database in a Visual Studio project with publishing support (create profiles for deploying to different servers and it handles the deploy/update for you). Which means you can check the database into source control.

VS2013+ also supports stepping through MSSQL Stored Procedures with the debugger in Visual Studio.

And now MSSQL 2016 supports JSON so we can query json objects, which means creating stored procedures with fewer parameters and using the new JSON support to process them as JSON Strings.

I haven’t done anything with JSON support yet, but a recent DB I am working on is 100% SSDT and stored procedures for most insert/update operations, I even validate credentials against Active Directory and do AD queries in stored procedures.

I’ve found that it’s actually faster for me to build procs in SSDT then write logic in my Service layer in c#. It keeps my Service Layer pretty clean, Instead of:

var employee = new EmployeeDto() { id = "1", name = "Bob", etc};
if (uow.Db.Exists<EmployeeDto>(employee))
    uow.Db.Update(employee);
else
    uow.Insert(employee);

I have something like
> uow.Db.ExecuteScalar(“EXEC [xyz].[addUpdateEmployee] @@id = @id, @@name = @name”, employee);

Should note that all my SP’s have a parameter called ignoreNulls so that if I set it to 1, null parameters are ignored and don’t overwrite existing values.

I am using PetaPoco and it’s T4 Text Templates to generate poco’s from the database I built with SSDT.

One thing I did not see you mention as that stored procedures can cause duplication of implementation.

In a reporting site we were making we needed to get many sums and counts grouped by either month, area, country and/or some other values. When joining the team there were about 150 stored procedures all with (for example):

JOIN [Calendar] on Entity.Year = Calendar.Year and Entity.Month = Calendar.Month

And something like:

SELECT ..., [Calendar].[Month] ...[the JOIN] ... GROUP BY [Calendar].[Month]

Or when filtering (filtering does not need to select the group):

SELECT ..., [the JOIN] ... WHERE [Calendar].[Month] IN (....)

When adding the option to filter and group by quarterly value another 150 stored procedures needed to be created or 150 stored procedures needed to be changed since the join and group were copy pasted in all the 150 procedures.

I added a SQL builder class and DataLayerHelper types to build a SQL statement that inherits from BaseDataLayerHelper so adding grouping by quaterly value would require me to only change one function; the BaseDataLayerHelper.prepareGroup (pseudo code):

prepareGroup(Query q, dataType, groupType){
    .....existing code
    if(groupType == BaseDataLayerHelper.GROUP_BY_QUARTER){
        IField dateField = this.joinCalendar(q, fromTable, groupType);
        ((Field)dateField).group = true;
        ((Field)dateField).sort = "ASC";
        q.addField(dateField);
    }
}

This would never be possible using stored procedures. Any new grouping or filtering feature that needed to be applied to all or many entities can now be implemented in the BaseDataLayerHelper.

Some people would still argue stored procedures is better because of performance (even though performance is not a problem) and because things get done quicker when people can use stored procedures.

Maybe sprocs for new things (new entities) get done quicker because some people in the team do not know how to use the helper and SQL builder but implemening application wide new filtering and grouping options would definetely take a lot longer with stored procedures.

It is funny though that even though in this particular case; using strored procedures was such an obvious Maintainability and extensibility problem, some people would still stubbornly vote to use them.

1 Like

Such a non-sense you claim. I had to login just to comment on this article.

First, you say “We should consider SP as DB Assembly”, then you close “We should use it for situations where the performance is critical”.

Every single system on earth would be greatly benefited from ANY performance improvement that can be done, no matter the size of the project.

You have a quite poor philosophy by believing it’s OK to ignore “critical” performance to your subjective understanding of the problem by the time the system is developed.

After so many years have passed, you should do a follow up entry on the same subject. In all honesty, I disagree with just about everything you said, but it would be good to hear your thoughts after all this time.
How’s that for a challenge :wink: ?

I would also be curious to see if the original author has changed his position on this topic. In case anyone cares (you probably don’t) here is my spin on this.

Stored procedures insulate applications from having to know or care about the specifics of where in the schema a particular data item resides. Application software in most instances should not need to concern itself with the pedantic details of the data storage model. If a particular application data object contains properties that were gathered from several different tables or even different schemas why should the application programmer need to care about this? To us application devs the business objects served up by the data access layer are widgets and I could not care less about the details of which cubbyhole the database keeps them in. Since us devs generally have to write the data access layers as well as the actual software my preference every time is to call a stored procedure to store or retrieve data rather than write some OE Linq gobeldygook that has virtually no protection from schema design changes in the database. As soon as you step on that OE train then a side-effect that you cannot get around is that it introduces a requirement of schema awareness into your application which in my opinion is highly undesirable. Is the SP model 100% decoupled from the schema? No it is not because as others have pointed out the parameters are definitely part of the schema too but they are relatively easy to maintain compared to table relationships and data storage locations.

Does it cost more to implement a SP interface up front? Yes of course it does. Does it save money in the long run? Like buying insurance, that entirely depends on what happens in the long run. If you are the type of developer who parachutes into a project, does your thing, and then leaves never to be seen again you probably do not care about this very much. If you care about your clients (or your employer, whatever the case may be) then perhaps your thinking will be more long-term.

As many other posters have already said, there are cases when one approach to data access is clearly superior to another so that one should be used, however in most cases I will take the schema independence advantages over the expediency of an OE type of design.

Given the age of the original post I am going to ignore the author’s obvious lack of understanding of what web services are and reserve comment on that aspect of the post.

Link for the “Daily WTF entry on Stored Procedures” is broken, new url:
http://thedailywtf.com/articles/Who_Needs_Stored_Procedures,_anyways_0x3f_

1 Like

I have a React front-end a Golang server cluster behind an NGINX proxy with MySQL as the DB. I use Stored Procedures, but I don’t do any business logic in them. I like to keep them simple and strictly related to fetching/storing data and if there is some dynamism ensuring that it always returns the same columns. But I generally do most of the rest from Golang. On the front end I actually have a datatypes.js file that defines the shape of all my data types with the default values that works with a library I wrote to do various manipulation based on it. The fields in these data types are actually the same as the names of the database fields so the Golang JSON field annotations can be the same as the ones in the data type JS declarations.

Nice old thread here but a topic which was actually answered for me over the time span of the thread

I’ve been in IT system development now for 25 years and the only constant, really, has been the DB and its SQL. How many programming flavors and ‘best practices’ have come and gone? All those grand ideas peaked and faded into obscurity. Every 5 years a chorus of voices chanting that the ‘latest’ must be implemented or face the risk becoming shock horror * ‘LEGACY’ … yawn …

Thank god the company I have worked at for the last decade relies heavily on DB side SQL logic. We have gone through two complete code (language level) rewrites in that time with relative little difficulty… The third is about to start. (See paragraph 2) I am referring to a fully fledged, highly automated, enterprise scale financial services application btw…

Take a long term view and its easy to see … the DB SQL rules the roost. Programmers (and I am one) wire up its front-ends and interfaces. End of story.

  1. “Stored Procedures are written in big iron database “languages” like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy”. This is true when you uneducated developers writing code. The same developer can write crazy JavaScript code.

  2. “Stored Procedures typically cannot be debugged in the same IDE you write your UI.” Another misstatement. Stored Procedures can be very well debugged with Visual Studio, SQL Developer.

  3. "Stored Procedures don’t provide much feedback when things go wrong. " This shows you have not written a single stored procedures in your life. T-SQL and PL/SQL has very strong exception handling mechanism.

  4. “Stored Procedures can’t pass objects.” Another misstatement. PL/SQL stored procedures can pass objects.

  5. “Stored Procedures hide business logic.” Yes. Because that’s what it is supposed to do. SQL is written for DB. Java or JS or those fancy languages cannot handle data processing.

This is a complete misleading article. Please first understand how database works before writing an article on Database.

This article is not only misleading it is nothing more than one persons opinion and has no basis in facts.
Most ORM mappers will auto generate CRUD stored procedures anyway.
However I must respect one fact stated by Einstein:
“It takes a bigger fool to argue with a fool”
And therefore I will choose not to argue with the fool who wrote this article as I would not want to be a bigger fool.

Reach your own conclusion. Stored procedures are a powerful tool when used appropriately. When used inappropriately they are a curse.
Consider this. Give a razor blade to a monkey and the monkey decides to shave it self. Does this mean that the razor blade is a bad thing? Then of course that monkey shivering in the cold would complain to high heaven that razors are evil. Another monkey observing and listening to the shivering monkeys complaints might also conclude that razors are evil. An intelligent human could however think intelligently and reach their own intelligent conclusion.
Of course I am NOT implying that whoever wrote this article is a monkey. Far from it. I am just illustrating a point that every tool has a purpose.

1 Like