March 6, 2020 at 12:00 am
Comments posted to this topic are about the item The Developer Arguments for Stored Procedures
March 6, 2020 at 6:20 am
When working with SP inside a .net project feels like Blackbox development. The Code you write looks like it assumes too much and needs a magic trust to what data an SP will return.
With Linq2sql you get compiler warnings when you cast types wrong or misspell fields etc. That is much faster to develop with Linq2Sql and less error prone.
March 6, 2020 at 6:38 am
In my understanding, around 2005 people started to notice that developers were putting business logic in stored procedures, and they advised against this practice. When this advice was picked up, it was verbalized as discouraging the use of stored procedures altogether.
When people speak in absolutes, they are often wrong. I still like using stored procedures to hide SQL implementation details and keep my nodejs code more readable.
March 6, 2020 at 8:38 am
I had a similar difficulty in explaining why I felt procs were preferable to EF. My initial reaction was very strongly in favor of procs over EF, although after calming down a little, I did actually spend quite a bit of time learning about EF and trying to bridge my knowledge gap between the two worlds.
The strongest argument I made was that procs and views are to sql as interfaces are for OO code. You should never be exposing class internals to another class because it tightly couples the to services, often in ways you never anticipated. That abstraction layer is critical in OO code because it frees you to make whatever changes you need to, as long as you can still satisfy the interface. Sql desparately needs such a construct, because changing the db is much harder than changing app code (in general).
As for the proc based approach, there's definetly some merit to saying procs can get overly cumbersome. Many orgs don't have a fleshed out data access layer *other* than using an ORM. In such cases, there are likely other growing pains associated to going deeper into sql programmability.
I will also sometimes point out that a a proc base solution I develop means if it breaks, ill be the one fielding the call at 11am, not them.
The other thing I find helpful (assuming you have receptive app devs) is to take the time to explain the problema I think will arise from an approach, and get them to convince me its the better choice. Often we both learn something. So convincing arguments are great, but pound for pound you can't beat a good rapport.
March 6, 2020 at 8:57 am
I was a developer long before I became a DBA. I went on the two original SQL 6.5 courses. I realised that the ability to write stored procedures made my life as a developer easier.
IMPORTANT POINT ONE: Developers have to be able to write stored procedures
I mean they need sufficient access to write the stored procs for themselves, they need to make mistakes for themselves they need help to learn, they need mentoring such as they would get in their normal role from a senior developer.
They won't be fans of stored procs if it is something that only the priesthood of DBAs, data team etc get to write.
IMPORTANT POINT TWO: Neither developers or Data people can afford to be a one trick pony, no matter how good that trick might be.
My stuff has to work well with other peoples stuff and vice verse. Producing the ultimate database is not the goal, producing the ultimate code is not the goal. Producing the overall system that fulfils the business need and is at worst cost neutral is the goal.
Learning a bit about each others world will help each be more sympathetic to the other.
IMPORTANT POINT THREE: Culture comes from the top and culture trumps strategy
Behaviour descends to the lowest level that management will tolerate. If the technical leaders have strong biases then those biases and behaviours will permeate downwards. If the boss believes that one discipline is lesser than another then if they are not careful to hide that bias it will spread through their reports downwards and will last long after they have left.
If the boss comes from a "Stored Procs are bad" background then you have a problem.
The legacy of poor or absent leadership lasts far longer than that leadership.
IMPORTANT POINT FOUR: Business logic is too vague a term to be useful
What happens if a business function is best expressed as a set based operation? Do you insist in doing it in some non-database tier of the code?
What happens if a business function is best expressed as complex iterative logic? Do you insist in embedding it in a stored procedure?
Can we not have some common sense rather than perpetuating the drivel about business logic? The genius of AND vs the tyranny of OR! Again, if developers are allowed to, are comfortable with and encouraged to write stored procs then it becomes a case of saying this function is best off expressed as set based logic, this function is better off done in whatever coding language is appropriate.
IMPORTANT POINT FIVE: Understand the difference between storage, presentation and interaction
Just because developers want to pass around data as JSON object doesn't mean that it has to be stored as JSON objects. Just because data might be stored in a relational form does not mean that it has to be presented in tabular format.
IMPORTANT POINT SIX: Sometimes stored procs are NOT the answer!
In a data warehouse/mart they are useful for data pipelines but for end users of the data mart probably not so much.
IMPORTANT POINT SEVEN: Sometimes stored procs ARE the answer
I think of them as either public methods for objects hiding private members or as encapsulations. This can be incredibly important for OLTP systems. They can abstract the complexity away from the app. Again, the procs have to be visible and writable by the developers.
March 6, 2020 at 9:00 am
I had this same conversation with an Nhibernate developer
he said it was just faster than writing stored procs
I asked him "would you write a c# class that exposed all of it's internal peoperties... you write let/get methods for each class"
reply "yes"
my reply "stored procedures are the equivalent of methods, they protect us"
he still won't wear that argument
MVDBA
March 6, 2020 at 10:01 am
I'm not biting.....
damnit OK. They're necessary sometimes but not always. I think that's probably enough said TBH.
March 6, 2020 at 12:09 pm
"...from a developer point of view..." Why is it "more difficult" to build with stored procedures:
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 6, 2020 at 1:22 pm
In today's cloud world vendor lock-in is an issue with most stored procedures being DB platform specific (MSSQL vs PLSQL vs MySQL). Having to deploy an additional server just to support your product's platform is a real expense that can drive customers to your competitor. Using DB abstraction tools like EF make your code more DB platform agnostic, but they typically do not directly support SPs. Like David Poole's Points 6 and 7, you have to know the requirements and if DB Platform Diversity is a requirement, that pretty much rules out SPs.
March 6, 2020 at 1:27 pm
I had a similar difficulty in explaining why I felt procs were preferable to EF.
By "EF" do you mean "Entity Framework" as in Microsoft? You do realize that you can use stored procedures with Entity Framework (though MS made it more difficult with EF Core)? Use of an ORM does not (generally) preclude the use of stored procedures, and using stored procedures (and views) with Entity Framework works well to isolate your code from direct table access.
I'm not necessarily advocating for or against stored procs (I prefer them, personally, and I use Entity Framework), it just bugs me when I hear people thinking that using an ORM precludes the use of stored procedures. 🙂
March 6, 2020 at 1:42 pm
Xedni wrote:I had a similar difficulty in explaining why I felt procs were preferable to EF.
By "EF" do you mean "Entity Framework" as in Microsoft? You do realize that you can use stored procedures with Entity Framework (though MS made it more difficult with EF Core)? Use of an ORM does not (generally) preclude the use of stored procedures, and using stored procedures (and views) with Entity Framework works well to isolate your code from direct table access.
Just curious, what database privileges belong to the login EF is using to execute those stored procedures? Is it db_reader and db_writer?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 6, 2020 at 1:48 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.
March 6, 2020 at 1:50 pm
dmbaker wrote:Xedni wrote:I had a similar difficulty in explaining why I felt procs were preferable to EF.
By "EF" do you mean "Entity Framework" as in Microsoft? You do realize that you can use stored procedures with Entity Framework (though MS made it more difficult with EF Core)? Use of an ORM does not (generally) preclude the use of stored procedures, and using stored procedures (and views) with Entity Framework works well to isolate your code from direct table access.
Just curious, what database privileges belong to the login EF is using to execute those stored procedures? Is it db_reader and db_writer?
I don't think I'd ever use a fixed database role for this purpose. I typically define a role specifically for use by the application, assign that role the select/execute privs on my views/procs, then add users to that database role.
So no, it's not db_reader/db_writer, it's a specific role I create, specifically for use by my application. So users have my database role and nothing else (well, I guess they have "public" but we don't give that access to anything).
March 6, 2020 at 1:51 pm
My standard design is that the R part of CRUD is done via views and the CUD part of CRUD is done through stored procedures. (in case CRUD is unfamiliar - Create Read Update Delete). This adheres to abstraction rules and principles quite well. There are many benefits to it, but three that keep me coming back to this as the goto design.
It's a pretty solid design and works for most solutions (I actually haven't found where it doesn't but I leave the possibility open). Still, I tend not to have any hard absolute rules except for around security and there are times and environments where this won't fly. Usually stemming from political arguments, time constraints (perceived or real) and change resistance (That's not how we're used to doing it.) so I try to remain flexible, unwilling to harm the working relationships if for some reason this design just isn't accepted. There are other (usually more involved) ways to provide the above three (especially 2 and 3 and 1 is subjective).
-
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply