March 6, 2020 at 2:03 pm
My arguments for using stored procedures is simple. Writing database manipulation code in C# is like writing a user-interface in T-SQL -- if it works at all, it doesn't work well because that's not what it's designed for. T-SQL is designed to manipulate data efficiently more so than C#, just as C# is designed to perform procedural processes more so than T-SQL. I have seen (and fixed) some really badly written T-SQL because it was written from a C# perspective. Again, what works great in C# does not translate as well into T-SQL. I saw looping structures, procedural logic, multiply nested stored procedures (treated as methods) and other such constructs that were written as though it were like C#. Once I translated them into set-based logic, the code was simpler, easier to follow, and way more efficient!
One of the most successful projects I was on had two groups in the development team -- one for C# and one for T-SQL. The division of work and interactions between the two groups was phenomenal! The resulting solution was rock-solid and employed the best of both worlds working in unison.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
March 6, 2020 at 2:29 pm
I am a C# developer for a state agency. Yes, it is easier for us to just embed the SQL in the SqlCommand, but calling a stored procedure uses the same SqlCommand.
Bad Example:
Before I was hired on, the agency contracted with a outside company to develop a large system. Many years later, that company no longer exists, but the agency now owns the source code. The outsiders use stored procedures for everything from SELECTS, INSERTS, UPDATES. If any of the DBAs on SQL Server Central looked at the database design, you would be asking "Who designed this crap?" There are very few primary keys and even less foreign keys. The outsiders used the stored procedures to enforce referential integrity outside of SQL Server. As a developer, it is a PIA (Pain In the …) for me to debug the C# in Visual Studio, hit a stored procedure, open up SSMS and find the stored procedure. Later, I scripted the database and added the tables, views, and stored procedures to the Visual Studio project and well as add those to source control. Now, I can examine the source code and the stored procedures from Visual Studio.
Good Example:
I developed a clean slate C# application to replace a Delphi application that matched Medicaid patients against immunization records in a DB2 database. It would match the patients either by Medicaid ID with birth date or the name fields with birth date. If it didn't find by Medicaid ID then it would do a search by the name fields; that involved two separate trips to the database. I thought, if the two searches were combined in a stored proc on the database, that would eliminate a possible second call for a search. Having DB2 do the two searches in a stored proc decreased the runtime of the application by 25%.
Another Bad Example:
Another system that was brought in-house was from an outside company. It had a server that managed phone and fax lines to call or fax notifications and had another system that functioned as a fancy front end web application. The agency got the code for the front end from the company, but there was a proprietary DLL which we did not have the source code for. A month after taking over the system, the system broke when trying to send out a health alert notice. I traced the problem to the proprietary DLL where it inserts the information into the server that manages the phone lines and faxes. It stayed broken for a month. The "project manager" was hounding me every five minutes "Is it fixed yet? Is it fixed yet?" Finally, with no access to the source code for the proprietary DLL, I used a UNIX style strings dump on the DLL looking for a smoking gun. I found a SQL statement that converted a character column to int to get the max for the next sequence ID. Yup, that column in the database had a few rows with non-numeric characters in it. I deleted the rows and that fixed the problem. The vendor was furious and the "project manager" was furious that the vendor was furious. My manager and boss were happy. I did what I had to do. BTW, the vendor dropped support for the system that we took over.
March 6, 2020 at 2:33 pm
A good point, very well made. The correct tool for the job is a maxim that holds true in many fields.
One of the most successful projects I was on had two groups in the development team -- one for C# and one for T-SQL. The division of work and interactions between the two groups was phenomenal! The resulting solution was rock-solid and employed the best of both worlds working in unison.
I wish there were more of this. Was this a product of the company culture or just that department?
March 6, 2020 at 2:50 pm
I wish there were more of this. Was this a product of the company culture or just that department?
I'm not sure about the company, but it definitely was due to the outstanding team I had. As the lead architect I was the de facto development team lead. Although we were dispersed across the country and were 100% remote, we became like family and worked great together. I am convinced that the success of the project was due to this outstanding team. Unfortunately, my company was purchased by a larger one, then later sold to another one that broke up the company and discharged the bulk of my team. I hear that the product that we developed and had invested so much time and effort has since crashed and burned.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
March 6, 2020 at 2:59 pm
I am loving these excellent comments.
I will point out that developers have a very short and predictable list of persistence requirements: load a grid, populate a form, save on click.
If DBAs take care to lay down an architecture that facilitates standard functions, developers will use it.
So I guess the upshot is: If you are cracking the whip on developers to implement standard procs, you are cracking on the wrong team.
March 6, 2020 at 3:00 pm
I always heard it was a good ides to embed the business processes into stored procedures so the same logic was applied, regardless of the application.
March 6, 2020 at 3:07 pm
First, what David Poole said. 🙂
In my career I've been primarily a lone wolf developer. Meaning I've had to be ruthless in finding every trick to lessen my workload. Databases eliminate a huge amount of code when it comes to validation and automation of donkey work. That's what it's for.
Most developers don't understand that. To them the database is just a dumb "persistence layer" at best, at worst it's a big ball of mud with arrogant bastards guarding it like it was Fort Knox.
So the first thing you have to do is convince them this SQL Server thing can cut their workload. That it can do it trivially. That it can save them hours per day in boring grunt work so they can concentrate on the fun stuff.
Before you can convince them of the virtues of stored procedures you have to convince them that referential integrity (with cascading deletes) is the best thing since mana from heaven. Then start pointing out that check constraints mean they don't have to write any validation code (the bane of every developer).
Then show them the power of indexes and tell them how it can speed their data retrieval by orders of magnitude. I guarantee that will grab their attention.
Then start showing them the speed and size advantages 3NF gives them.
Once you've done all that then you can start listing the advantages (and disadvantages) of stored procedures.
Don't sing the praises of stored procedures and writing T/SQL without being upfront about the disadvantages too. For every benefit stored procedures grant they exact a price. I'm not saying SPs aren't worth the cost, but they definitely aren't free. Being upfront about the pain will go a long way to convince skeptics you aren't peddling snake oil.
First, they'll have to learn T/SQL to write stored procedures. They're going to hate T/SQL with a passion! It requires learning to think in sets, it's primitive, it's clunky, it violates DRY with vicious glee. T/SQL sucks as a general purpose language which is the only kind most developers know. So, in T/SQL while you can use loops, call functions, and so on it will cripple performance. So not only does T/SQL kill any hope of DRY, you actually need to forget just about every advanced programming concept from the last 50 years.
On the upside the handful of things T/SQL does do well it does with god-like speed and perfection. Basically, database I/O, set based updates, and so forth. Oh, and you get bulletproof referential integrity and data validation too.
Second, using stored procedures means they have to create (or have created for them) one stored procedure for every different SQL statement they need. That's going to be thousands in a large application. Every time they read or write to the database, every time they need to look up something it requires a new SP.
After they finish laughing at you, calmly explain how it simplifies the permissions issue. The only permission needed for an SP is Execute. The granularity of SPs is atomic, if you need a specific lookup from a table just those fields get returned, you don't need to grant read permission to the entire table. Same with inserts, updates, deletes, etc. This makes security much simpler.
Oh, and stored procedures don't have to be compiled, either. They're interpreted.
That should cause some thoughtful faces...
In short, if you want developers to fall in with stored procedures you have to overcome their ignorance. You have to appeal to their laziness. You have to remind them of how good they're going to look when delivering a high performance, high security application on time. One that's easily extensible too.
Because 3NF is certainly going to enhance the database's extensibility while enhancing performance. SPs are just a convenient way to package the database code required. 😉
March 6, 2020 at 3:14 pm
I am loving these excellent comments.
I will point out that developers have a very short and predictable list of persistence requirements: load a grid, populate a form, save on click.
If DBAs take care to lay down an architecture that facilitates standard functions, developers will use it.
So I guess the upshot is: If you are cracking the whip on developers to implement standard procs, you are cracking on the wrong team.
I take on board the point about DBAs laying down an architecture.... but we often aren't involved early.. the PM goes to a developer, they play with things (often unsupported) and come up with a prototype that makes us all bang our head on the desk 🙂
MVDBA
March 6, 2020 at 3:17 pm
I always heard it was a good ides to embed the business processes into stored procedures so the same logic was applied, regardless of the application.
there's a fine line where you put your business logic - mine is "I own auditing and foreign key related updates" the rest is yours
MVDBA
March 6, 2020 at 3:25 pm
First, what David Poole said. 🙂
Don't sing the praises of stored procedures and writing T/SQL without being upfront about the disadvantages too. For every benefit stored procedures grant they exact a price. I'm not saying SPs aren't worth the cost, but they definitely aren't free. Being upfront about the pain will go a long way to convince skeptics you aren't peddling snake oil.
First, they'll have to learn T/SQL to write stored procedures. They're going to hate T/SQL with a passion! It requires learning to think in sets, it's primitive, it's clunky, it violates DRY with vicious glee. T/SQL sucks as a general purpose language which is the only kind most developers know. So, in T/SQL while you can use loops, call functions, and so on it will cripple performance. So not only does T/SQL kill any hope of DRY, you actually need to forget just about every advanced programming concept from the last 50 years.
That should cause some thoughtful faces...
for me , I can quickly modify a SP on live in the middle of the night if there is an issue.. then backfill it into the build when I get to work.
ask a c# developer to be on call and run a build and re-deploy at 3am … not a single one of them will take on that role. .. net result, application is down until 10 am the next day.
this gets even worse if you are providing an international service
MVDBA
March 6, 2020 at 3:37 pm
Any Microsoft-stack business app developer who can't write decent T-SQL procs is incompetent.
March 6, 2020 at 4:08 pm
One good reason for an application to only use stored procedures to access data is that you don't have to give the application-user permissions on the tables to select/update/insert/delete. The only permissions an application-user needs are execute permissions to the stored procedures the application uses.
March 6, 2020 at 4:12 pm
I am a developer at heart, but have grown into more of a manager and DBA role over the last few years. Back in the classic ASP days, I wrote dynamic SQL and embedded it into my code because that's how I learned. I didn't know how to do it any other way. About the time SQL Server 2005 was released, I started hearing more and more about how and why I should be using stored procedures to prevent SQL injection attacks. Around that same time, I also started to transition my code over to ASP.NET.
Making both transitions at once was painful and made me feel less productive. There was a very strong temptation to revert back to my old ways. But after a while, I was able to make the switch and become productive again. What I didn't realize until some time later is that writing SQL stored procedures changed my mindset and the way that I approached development. Now, I always start with the database objects first, and the SQL stored procedures are among the last things I write. They are the "methods" I call to SELECT/UPDATE/DELETE my data. Then I create a data layer inside my application; related classes whose job is to directly interface with the database and call those stored procedures. Only then do I begin writing my actual application code.
Someone who is used to writing and embedding dynamic SQL will argue that this approach is redundant and takes much longer... and they are correct. But the immediate benefits that come to mind are:
March 6, 2020 at 4:14 pm
...developers were putting business logic in stored procedures, and they advised against this practice...
I've heard that argument before, that the database shouldn't have "business logic" in it, and I believe that isn't a well thought out argument. The way I look at it, there are 3 types of "business logic"
expresses rules about the storage and retrieval of the company's data
expresses rules about how people interact with the company's data
expresses rules about when people will interact with the company's data
A database with no business logic is just one table with two columns, AttributeName, AttributeValue. 😉
March 6, 2020 at 4:22 pm
I seem to be in the minority here as when I was a jobbing developer I insisted that all of the necessary SQL code was encapsulated in Stored Procs. Admittedly we were a small team of 6-8 people and we each tended to work on our own projects and were responsible for both the application and database code. I just got fed up with having a minor change to the database force me to edit, recompile, and deploy the application again. By forcing all of the SQL into Stored Procs I could just edit the procedure instead, and as long as the returned dataset was the same everything kept working. This reduced downtime and made fixes fast.
They also allow you to push out a version 2 with extra functionality while still supporting version 1, as version 2 can use new stored procedures where necessary. If you want to look like the Great Big Hero(tm) stored procs make you look good. I didn't want to be a hero, I'm just lazy and would rather trade a little more effort now for an easier life in the future.
And LINQ->SQL is devil spawn.
ALEX GAY FOR PRESIDENT! 😀 Well said!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply