sql stored procedure vs code, which one is better? [closed]

Question

I am creating an architecture of a web application in MVC, web application will be maintaining data for E-Commerce kind of stuff.A little confusion arises between two approaches,

1.

Should I keep all code of calculation/Data access in class file(Model) which will be using EDM for accessing DATA. This accessed data thru EDM will be refined by code available in MODEL file and access by controller. In this approach I will have to create only few models, rest models will be created by EDM and can be bound directly to Views.

Should I keep all code of calculation/Data access in Stored procedure which will be using SQL queries for accessing DATA. This accessed data thru SQL queries in stored procedure will be used directly by code available in MODEL file and access by controller. In this approach I will have to create many models as i will have to set parameters of STORED procedure.

Here question arises,

• which approach is best for client , First or second ? and Why ?

• Will there be any performance difference between these 2 approaches ? How ?

• Which approach will be implemented faster ?

• Which approach is safer ?

Thank you, in advance.

Answer 1

Which is best for the client? Let's answer that last.

Performance:

The short answer is that it will not have a performance difference. In past versions of SQL, the query engine would optimize stored procedures, but not ad-hoc queries. Current versions of SQL do not make a distinction. SQL stores every execution plan as long as it can (stored proc or not) and will reuse it if it can. Pro tip - try not to make your where clauses too crazy and SQL can reuse it easier. Sometimes it better to write a handful of simpler queries than write one big mega query. But that's a BIG topic with many many factors!

Faster Implementation:

That's easy. I've done both methods for the past 12 years and without a doubt writing stored procedures is MUCH more labor intensive. It also can lead to many errors as it is one more layer to babysit. I'd shave 10% off a quote if the client would let me avoid stored procedures. But many clients insist I use them.

which leads us to... Safer:

Stored procedures are safer and here is why. With stored procedures you need only grant one single role to the SQL log in - Execution Rights on Stored Procedures. Granted you can do a lot of damage with that but it's still limited. With ad-hoc queries, like something like Entity framework, you must grant full read and write access to the tables directly.

That distinction used to have weight with me, buy after dozens of projects at dozens of companies I just really don't care to make this nuanced distinction anymore. If you are hacked and they have direct access to your SQL and also your username and password then this level of rights difference is not the problem.

My opinion is that avoiding stored procedures will be just as speedy, enable you to code faster, and isn't significantly less safe.