Stored Procedure vs. LINQ for database calls

Development Web Development
1

Hey good people!
I’m updating a decent number of apps to newer .NET versions, per the majority suggestion. Thanks again btw :slight_smile:

This seems like a pretty divided topic online, but also one that can change drastically with situational context, so I was curious what others’ thoughts were on it: do you ere on the side of using Stored Procedures when calling the database, or do you prefer to use LINQ statements?

Generally, I find it easier to use SPs in most cases, given that it makes the Database team’s job less messy. Additionally, business logic can sometimes be fine tuned or altered without re-deploying the app (maybe having business logic in SQL isn’t the best thing?). SQL Server also appears to handle more complex comparisons much faster than LINQ.

Thoughts? Opinions? Feel free to grind your axe if you had some bad experience :stuck_out_tongue:

2

Linq translates to sql so its not really faster in that sence. Though, if you know how to optimize raw sql you can outperform the generated sql.

Linq gives you some abstraction from the db so its easier to swap dbs. Most projects dont need to do that so much. Though i have worked on projects where we used different dbs for cloud and on premise deployments.

If you do not need to swap db the ability to do so may still come in handy if only because 3rd party library devs are able to write paging for a grid and have it work with >insert your db< and things like that.

If you need to scrape every last bit of performance out of your db then you wont really get around to work with db specific features sometimes. Another reason to do it would be cost savings. Cloud providers usually charge you for usage, so you can sometimes save a good amount of money doing some data manipulation inside the db. Depends a lot on the use case.

But if linq is or you can optimize it to be fast/cheap enough id stick with it. I find having stuff be visible in code to be valuable, which a stored procedure won´t be unless you generate it with code. It´s also what C# devs are more used to seeing I´d say. You end up with less onboarding time the simpler your solution is. There are a bunch of dbs and sql isn´t exactly a clear cut standard, if you take a more complex sql query from one db and try to run it on another, chances are it probably won´t work, never mind all the other query languages nosql dbs have come up with.

1 Like
3

Avoid stored procedures at all cost. PL/SQL is database vendor dependent.
Linq is a good choice.
Personally, I prefer Dapper.

2 Likes
4

first of all, a database is a tank of data, and application logic should be isolated into the application. there’s nothing inherently wrong with getting data via stored procedures but most of the problems with that design whittles down to:

  1. stored procedures encourage lazy behaviour, and
  2. developers will be lazy if you give them the chance
  3. application logic is source code, and therefore should be version controlled. ever seen something like sp_getclient_by_id, sp_getclient_by_id1, sp_getclient_by_id2, sp_getclient_by_idNEW, sp_getclient_by_id_developername_TEMP sp_getclient_BEFORE_UPDATE_DO_NOT_DELETE in the same list right next to eachother?
  4. finally, different versions of the application should be able to talk to the database at once as it’s just a repository for information, and you should be able to rollback the application without affecting the data-tier.

now, if you can ensure strict separation of concerns i don’t see a problem with it, but the temptation to put dumb shit in stored procedures is just too great

–

linq queries just compile to SQL and those get run with sp_executesql last i checked. you can version control those because they are source code, and different versions of your services can do different things with the db because it’s just asking for, and replacing data if needed

personally, i don’t use ORMs as i hate them, but even that is better than app logic in stored procedures

2 Likes
5

So you’ve seen the darkness just like me… :yay:

3 Likes
6

Thanks for the feedback, the version control for SPs makes a lot of sense…but of course I’m the exception and would NEVER name a file like that :woozy_face: …shuffles files under desk. And I can also see how SPs couple the app too closely to the DB. That actually is a very relevant point to myself, given we may be trying to move from on-prem to cloud DB in the future. Your advice could save future me some major headache.

However, Could you expound on what you mean by this quoted point? Not sure I follow what kind of behavior it encourages that is lazy. Speaking from ignorance here.

1 Like
7

Coming from a shop that called all sorts of stored procedures so that they did not have to write the logic in the actual code, this is a nightmare.

From a T-SQL perspective, Microsoft made breaking changes between 2008 and 2012. Then they added features to 2014 that did not exist in the prior versions while deprecating others. If your customer ran their own infrastructure, then you could not guarantee that the stored procs will run on their systems. Then you are making patches to stored procs or patches to your software to then do some of the logic that you were doing in the stored procs. → You see where this is going?

I personally treat stored procs like scripts. If you are relying on scripts to make your application run, then you are kind of setting yourself up for failure. Basically you are using 300 mile an hour tape and bailing wire to hold possibly critical parts of your product together. Especially if those parts are OS/Patch version dependent.

I could go into specific details but essentially, it is hard to maintain and is not polished. It also leaves you open to many security risks down the road.

8

It’s been a long time since I had to deal with this stuff, but at least a few years back the ORM produced bad performing SQL.

Stored procedures are good to hide the implementation detail of the data schema from the applications that access the data, and provides an API to them. Database specialist can then optimize queries/schemas without the applications knowledge. Or it’s usefull if not all application could be changed the same time you implement a new feature in the database.

Another way to do it, is to create a service that talks to the database and provides an API to all the other services/applications that need access to the database.

Off course all the code in the database should be also under version control, and changes need both an implementation and a rollback part and needs to be repeatable. I had many fights with java developers regarding this. Nobody has time to do a multi hour database restore to retry the db update script just because you have had an error in the first version of the db change script.

1 Like
9

That is one thing about ORMs which is kind of annoying sometimes. It can be awkward at times to figure out exactly what SQL it generated, so you might spend an embarrassingly long time debugging something just to find you used the wrong foreign key or something.

But overall, like others have said it’s worth it not to rely on stored procs too much. It’s often one of those things people start doing when someone tells them “don’t spend too much time on it”, and then a few years later they’ve got a tangled ball of crazy where simple things take forever to get done.

1 Like
10

This is a generalization coming from a sample of how many systems?

If your devs are writing code poorly, and your architects don’t know their ass from their elbow, then you will have code that is unmaintainable and poorly performing, independently of where it is written.

If we’re talking raw performance on a relational model, then a stored procedure runs on the same infrastructure as the database, so it will be more efficient at managing large volumes of data, but it will add load to your database and possibly make performance worse overall for other service.
Still, for anything other than a single table indexed access a stored procedure will perform better than anything at the app level, especially in this age of TLS encrypted comms

If we’re talking best practices for managing your code, then managing well software release cycles using stored procedures adds complexity to an existing code base process, and it requires a different software skill set as devs will need to shift their standard programming paradigms to make the most of the code running in the database

If we’re talking in absolute terms I’d say the best patterns depends on the complexity of your business logic and on the composition of the team, and also on the technology used

If you are using postgres and your app framework creates inefficient SQL you can throw hardware at it and try to see if it improves, if you are using SQL server or oracle, for which you pay 15-100k USD per core, you have a lot of ‘incentive’ to write performing SQL …

1 Like
11

Yes, of course the real answer is “it depends”.

Let’s also remember though that for most of us software is not a purely academic exercise, so too many people trying to make things perfect is often just as bad as too many people who don’t know how to do anything. Unless there’s a lot of low hanging fruit still to be picked, then there may not be a clear cut winner based on some obvious technical advantage, so like you said there’s other things that should come into consideration. I think generalizations can be useful to help avoid taking that first step towards a bad habit, and generally speaking I think it’s easier to box yourself into a corner building logic into your database.

1 Like
12

sure, consider this scenario with bonus hyperbole:

you have a production issue, the type is irrelevant but a good subsection of the business can’t do their jobs. you’ve got executives breathing down your neck to get it fixed and the sooner the better. you’re a dev, you like your job but you don’t like it that much, and you are only ostensibly responsible for the product, and the most control you have over the situation is doing what your manager says to do.

do you:

A) open the sp_something, duplicate it to sp_something_temp_FIXED_MARCH and add some cooked “temporary” code, something along the lines of

IF (T.date_of_irrelevant_comparison BETWEEN '2023-01-23' AND '2023-03-23')
BEGIN
   EXEC sp_fix_broken_rows
END

in production, and everything goes back to normal. people can do their jobs again, you’re in and out in 15 minutes. you test it using a test call in the query console, proved that it works, renamed the sp to the production one and good to go.

or B), change it in code, go through the test cycle, spend time deploying it, asking everyone in the entire organization to please close the app and reopen it/log out and log back in again, taking hours to days.

A) is not maintanable, there has been no change management, no version control, and the dog shit you just wrote is now in there forever for someone else to trip over. it works, is quick, and is ad-hoc, full of risk to security and stability and is lazy

the reason you choose B) is to hopefully reduce the scope and severity of issues like this in the future by having proper separation of concerns

edit: in the future

2 Likes
13

My brethren! Bravo. That is much better than my point that turned into a ramble. This is what I was getting at. It happened all of the ti e at thr shop I was in. I eventually left for sanity reasons. Everyone wanted to take the easy route and the company was a, “Bug fixes do not get us new features and does not make us money!” Business

2 Likes