Using stored procedures

Question

I have a task which receives an input and performs a series of tasks on the SQL server, which some depend on the content of the server and on the input. For that I use a lot of selects and then logic based on input and then insert or update. The network activity between the server and the client is high while performing those tasks because they depend on the content of the server.

Will I gain any significant boost or degrade in performance if I move this to a stored procedure while the sql server and client are on the same machine?

Answer 1

Yes you will get a performance boost if you do this entirely on the server as the IO will be contained entirely inside the database server. However, at the same time you will introduce a performance hit on the server potentially.

It's a trade-off but I'd say it's worth it if you have spare capacity.

Answer 2

Keep database logic in the database. I'd say your best bet is to utilize a stored procedure. That way you keep your data logic in SQL Server where it is more efficient (plan reuse, etc.).

The database engine is made soley for this sort of thing. Your application is still making calls to the database even if you keep your data logic in your application code.