What's more expensive on my server-side processing?

Question

So I want to be processing my security protocols server-side, mainly token checks and then role authorisations for certain actions.

I'm using c# .net core connecting to an MS SQL database and publishing to azure.

I really want to write stored procedures to handle the role-based authorisations and leave the c# code cleaner, but whenever I see code snippets from MS etc. that's all handled in c# code, but I'm not sure why? Is processing c# on my azure service less resource-intensive than doing the equivalent in a SQL procedure?

Is this question too open-ended?

Answer 1

Unless you have millions of users, this is not going to matter at all, performance-wise - and even then it will be irrelevant compared to other concerns. Perfect example of premature optimization of the worst kind: your C# code might be "cleaner" without that functionality, but now you have to maintain complex logic in a different language, one less suited for that kind of task.

Even from a (misguided) performance point of view, it's a bad idea, not so much because processing the code might be less resource-intensive in one place or the other, but because it's much easier to add more app servers to run C# code than to scale up the database. You really want the DB to do only the things it's specifically optimized for and excels at: process regular SQL queries.

Answer 2

In most cases, an architecture should strive to minimize processing that is done on the database and should minimize the amount of logic deployed there as well. There are several reasons for this:

Performance and scaleability. Your database tends to be a performance bottleneck. Application servers have performance limits as well, but they can be scaled out and load-balanced. Scaling out an OLTP database is not really possible as most OLTP business logic involving persistence also requires ACID properties. If you scale out the database, you pretty much have to give that up-- the best you can do is eventual consistency.

Concurrency. Database logic tends to affect concurrency because database transactions can block other threads of execution. Putting the logic on the application servers forces you to write code that works well in parallel.

Deployment. Your database requires special handling when deploying a new version-- it may even require an outage in order to be able to support a comprehensive backup and rollback plan. Application servers can be spun up or shut down at will, allowing you to deploy multiple versions at once and support blue/green deployment. This can be a huge boost for your uptime and Gomez rankings. But it requires that the database have no changes (difficult to do if all your business logic is there), or that you take great care to make them backward-compatible (in which case you have doubled the QA effort).