Geek Answers Handbook

2 calls or one call to sql server

I have an action in my .net application that creates an entry in two server2 differnet sql tables. What is the best practice here?

Should one stored procedure create both records? OR If another stored procedure creates each record in the applicable database and transfers both calls to the transaction, so if one of them fails, both fail?

Which one is more effective?

+6
source share
2 answers

The stored procedure will be more effective, definitely. This will also reduce the amount of code you have to write on the server side. The disadvantage is that now you need to either know how to write SQL yourself, or have a database administrator with whom you work, who can write it for you.

As for your other question, this will be 1 saved proc that inserts into both tables.

Another thing to keep in mind: IF you have to do a lot of things like this throughout your code, it might be worth learning a good ORM and doing it all in code (option 2). But if this is a kind of one-time thing that you only do in a few places, the stored process will be more reasonable.

+1
source

It is better to have the logic in the stored procedure on SQL Server and determine the scope of the transaction, and when this action, initiated in the .net application, performs your operation in the stored procedure and if these two actions are completed successfully, send the transaction, otherwise it is a rollback transaction. Because this way you have the best performance. Using a stored procedure in accordance with Microsoft docs has some of the benefits listed below:

  • Saving and reusing the execution plan
  • Automatic request parameterization
  • Encapsulating Business Rules and Policies
  • Application Modification
  • Exchange application logic between applications
  • Access database objects that are safe and uniform
  • Consistent, secure data modification.
  • Saving network bandwidth.
  • Support auto start at system startup
  • Advanced hardware and software features.
  • Improved security
  • Lower development costs and increased reliability
  • Centralized security, administration and maintenance for common procedures
0
source

More articles:


All Articles