Sql Server Stored Procedures

Asked

Viewed 174 times

3

I am starting the development of an application, basically it is a managerial control of companies. The application will issue NFC-e, Nfe, control stock, finance and etc, the database will be hosted in Azure. I started modeling the database and creating stored procedures to keep the data in the database, but noticed that I will have to create a huge amount of stored procedures due to the fact that the database contains many objects. I am taking care not to insert anything from the business layer into the stored procedures.

As I have little experience I am confused, because some say that stored Procedure is a bad practice and should only be used in issues where performance is needed, example here. Others say it’s worth using, example here.

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

c# sql sql-server orm stored-procedures
  • 1

    I flagged your question as based on opinions. It depends. You can’t tell if it’s better or worse, good or bad practice to use procedures. I’ve done systems full of them as well as with ORM.

  • @Murilo, I think so too.

  • You can read this: http://answall.com/q/15739/101. This answer says that a SP is a lot: http://stackoverflow.com/a/135954/221800

1 answer

4


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

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

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

This can all influence your decision.

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

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

Orms are not designed because they perform much better than a hand written approach. We use them because the development is much easier!

If you write your application with the Entity Framework and hide all your queries behind a repository pattern you can develop very quickly and then when performance becomes an issue, 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.

Browser other questions tagged c# sql sql-server orm stored-procedures

You are not signed in. Login or sign up in order to post.