Stored procedures or ORM in web?

Question

I am working in a Peruvian company that develops desktop accounting software(.net C#).

We have many clients (companies) each customer can create several companies, in addition there is a new database for each year of each company. Example:

DBCOMPANY1_2045658512_2015
--------------------------
DBCOMPANY1_2045658512_2016

DBCOMPANY1_2045658512_2017

DBCOMPANY1_2008004100_2016
--------------------------
DBCOMPANY1_2008004100_2017

The software we want to implement in web so that it can be commercialized with access to several companies, hundreds or thousands of monthly records, electronic billing.

Existing client databases use store procedures but it is uncomfortable to update the triggers, store procedures on all existing databases.

If it is advisable to use stored procedures as would the update of these for all databases? If it is ORM how would it affect system performance?

Answer 1

The problem with Stored Procedures in your case are the multiple databases.

As you point out. if you have to change a sproc you will then have to deploy that change to every database. Where as if you keep the SQL in the code, you can get away with just deploying the code once.

So from a maintenance angle SQL in code is going to be eaiser

SProcs do add a level of abstraction to the datalayer, which helps if you have a separate database team. But its not uncommon to skip SProcs these days.

Separate databases per company have the bonus of adding extra assurance that the data is completely separated.

But you should consider designing your code to work with multiple companies and multiple company years in a single database. This will help with scaling and lower maintenance costs.

Answer 2

Stored procedures vs. ORM isn't a clear and cut decision. You will get answers on both sides of that question.

Stored procedures will always have an advantage over ORM in that they can be precompiled using statistics and index information that the database engine can extract from the data tables. So they will always perform better. But depending on what you are doing, it might not matter much-- if a call takes 0.3 seconds versus 0.5 seconds, for example.

Honestly the worst part of your design is the multiple database. You should try to move toward multitenancy in a single database, if at all possible. It will make your life much easier. If you're worried about it growing too big, you can look at clustering, availability groups, and distributed partitioned views.

If you insist on keeping multiple databases, you should look into schema replication, which will make deployment of your stored procedures and triggers easier and less prone to human error.

Answer 3

Interesting question and agreeing with John Wu there's no clear cut "right" answer it depends on priorities and the overall skill of the team. Keeping the teams' skill level in C# and the RDBMS has a lot of value in my opinion. I've seen a lot of coding horrors caused by junior developers not understanding how databases work so keeping the skill levels up mitigates that.

Choosing Stored Procedures ties you pretty tightly to one RDBMS. It's not impossible to support more than one (See SAP) but it's not desirable. If you have multiple customers who have a specific reason for wanting your system to run on their RDBMS it's probably best to abstract the database which means ORM.

Using an ORM simplifies database access, removes a percentage of the need to use direct SQL - but NOT 100%, I cannot stress this point enough. If you are writing applications that access an RDBMS there is no way you can entirely skip hand coded SQL and do everything via the ORM unless your app is so trivial it doesn't warrant using an RDBMS or you're willing to put up with horrendous performance. ORM abstracts the database at the cost of not understanding the way the data is stored in the database and what the implications of the database design mean. For most in-house corporate systems the "benefit" of being able to jump from one RDBMS to another easily is unlikely to be useful - big corps do not drop Oracle on a whim to go to MySQL if they choose that path the project will be immense and the cost of rewriting a bit of SQL and a few Stored Procss will be dwarfed by the rest of the costs. If you're a small company where getting the sale could hinge on your ability to support (insert obsolete RDBMS here) as well as the one you develop for then an ORM could be a valuable option.