Sql Server Stored Procedures [closed]

3

I'm starting to develop an application, basically it's a business management control. The application will issue NFC-e, NFe, will control inventory, financial and etc., the database will be hosted on Azure. I started by modeling the database and creating stored procedures to keep the data in the database, but I noticed that I have to create a huge amount of stored procedures due to the fact that the database contains many objects. I am careful not to insert anything from the business layer into stored procedures.

As I have a little experience, I'm confused because some say that stored procedure is a bad practice and should only be used on issues where performance is required, eg here . Others say it's worth using, example here .

In the opinion of the most experienced, what is the best way, to use an ORM or to create all the stored procedures that it is necessary? Is it a problem for a database to have a large number of stored procedures? In this answer you have one that claims to have 200 or more in a commercial system .

    
asked by anonymous 30.09.2016 / 15:34

1 answer

4

If the answer is from a DBA, of course it will say that there is no problem whatsoever and that procs should be created in the database, if it is a developer working with an ORM, of course he will tell you to use his methods in the application to make your CRUD.

The question here is; you can use an ORM, your application will support it without loss of performance?

Do you work with the database and application at the same time, or do you have a DBA that takes care of your bank?

This can all influence your decision.

There are several reasons to use Stores procedures , see the snippet below from here .

But to be honest, a stored procedure with manual mapping will always be faster in performance. But ask yourself, how important is performance? On most projects, development time is much more important than performance. What was more difficult to develop? The raw query with the analysis or query Entity Framework?

ORMs are not designed because they perform much better than a handwritten approach. We use them because development is so much easier!

If you write your application with the Entity Framework and hide all of your queries behind a repository pattern you can develop very fast, and then when performance becomes a problem, measure your application to detect the bottleneck So maybe some of your queries need optimization and can be moved to stored procedures and manual mapping.

    
30.09.2016 / 16:28