sql stored procedure vs code, which one is better for tremendous amount of data?

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.

Answer 2

I worked writing SQL stored procedures as part of my job for 8 years. If you are working for a company and not working on your own, DO NOT use stored procedures if there is an option to implement the feature in software.

Stored procedures are great for speed if you are quickly iterating over DB rows, as it saves the travel-time and costs of shipping the data from the DB to the software over the wire; however there are some truly horrific disadvantages.

1) Difficult/Expensive to code. Stored procedures are a powerful language unto themselves. I'd equate their difficulty and obtuseness to writing complex REGEX queries. A 100-line stored procedure is probably equivalent in difficulty to writing a 500-character REGEX query. You will work very hard making sure you are doing the query efficiently/correctly, and if you are responsible for hiring someone to perform the work, it will be expensive, as this individual needs to have specialized skills.

2) Slightly related, but don't underestimate how difficult the code will be to support. If you ever want to be able to hand this code off to another human being, you should choose at the onset to use a language that is common, and well-known. I ended up supporting the same piece of software I had written from my first year at a company, straight to the 8th when I left their employ. I moved up the ranks to a director position and had dozens of employees; however I couldn't, and no one else in the company could trust another individual to take over the code I had written nearly 10 years prior - it was a very bad rookie mistake on my part.

Avoid stored procedures if your problem can be solved well using other methods. Don't be scared of them, but don't ever put them at the top of the list of tools to attack a new problem.

Answer 3

In general, I would work with the tools and mental models provided by your development framework - in this case, ASP.Net. This gives you lots of benefits, especially when it comes to maintainability (any developer who has worked on ASP.Net can pick up the application), security (you benefit from all the framework security features), and speed of development (you're almost certainly not going to run into unique problems).

For ASP.Net, the most common data access pattern is to use the Entity Framework. EF is based on the premise you'll use ORM features for database access; this has many benefits:

• you write less code - the CRUD operations are out of the box, and you don't have to manually map application variables to data fields
• your mental model is at the application model level, not the specific implementation level
• you can create unit tests so you can be sure your application behaves they way you want it to
• you keep your application code and database schema in sync automatically

There are also some drawbacks:

• performance problems can be hard to figure out
• for operations that are highly data intensive, you may get poor performance

So, my recommendation would be to go with your first option, using the Entity Framework, and to use stored procedures only when you have highly data-intensive operations (e.g. calculations that span many records in many tables).

The reasons I would recommend against stored procedures as the default:

• they are hard to unit test
• they are hard to keep in structured build/deploy scripts with source control
• they are hard to debug
• it's not super common for a single developer to be good at both ASP.Net and SQL - they are very different skills.
• you find yourself writing a lot of repetitive CRUD procedures

Answer 4

1. Which approach is best for the client?

Here is the list of Pros and cons of the Linq vs sprocs. It totally depends on the client's requirements.

Some advantages of LINQ or Entity Data Model(EDM)over sprocs:

1. Type safety: I think we all understand this.
2. Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this support. It would be MUCH harder to do this data access code that simply calls sprocs.
3. Debugging support: I can use any .NET debugger to debug the queries. With sprocs, you cannot easily debug the SQL and that experience is largely tied to your database vendor (MS SQL Server provides a query analyzer, but often that isn't enough).
4. Vendor agnostic: LINQ works with lots of databases and the number of supported databases will only increase. Sprocs are not always portable between databases, either because of varying syntax or feature support (if the database supports sprocs at all).
5. Deployment: Others have mentioned this already, but it's easier to deploy a single assembly than to deploy a set of sprocs. This also ties in with #4.
6. Easier: You don't have to learn T-SQL to do data access, nor do you have to learn the data access API (e.g. ADO.NET) necessary for calling the sprocs. This is related to #3 and #4.

Some disadvantages of LINQ or Entity Data Model (EDM) vs sprocs:

1. Network traffic: sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex. However, LINQ's abstraction allows Microsoft to improve this over time.
2. Less flexible: Sprocs can take full advantage of a database's feature set. LINQ tends to be more generic in its support. This is common in any kind of language abstraction (e.g. C# vs assembler).
3. Recompiling: If you need to make changes to the way you do data access, you need to recompile, version, and redeploy your assembly. Sprocs can sometimes allow a DBA to tune the data access routine without a need to redeploy anything.

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

There are some things you can do to optimize your query. Here on MSDN you can find a nice overview.

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

ORMs are not designed because they perform so 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 your queries behind a repository pattern you can develop real quick and then, when performance becomes an issue, measure your application to detect the bottleneck. Then maybe some of your queries need optimization and can be moved to stored procedures and manual mapping.

3. Which approach will be implemented faster?

I guess that depends on the developer.

4. Which approach is safer?

Security and manageability are something that people argue about too.

1. Security: For example, you can protect your sensitive data by restricting access to the tables directly, and put ACLs on the sprocs. With LINQ, however, you can still restrict direct access to tables and instead put ACLs on updatable table views to achieve a similar end (assuming your database supports updatable views).
2. Manageability: Using views also gives you the advantage of shielding your application non-breaking from schema changes (like table normalization). You can update the view without requiring your data access code to change.

I used to be a big sproc guy, but I'm starting to lean towards LINQ as a better alternative in general. If there are some areas where sprocs are clearly better, then I'll probably still write a sproc but access it using LINQ. :)

Answer 5

While unit testing can be done on procs (usually with add-ons to the DB) I've yet to work at a place that does unit testing on procs. Most DBA's I know hate add-ons to the database. This also brings up the point of source control. While add-ons for source control exist, again most don't do it. So source control becomes slightly more painful than "normal" code. It's not bad but it's usually not as integrated either.

I think ORM's have really made life easier for coders to bring sql out of the proc and into (hopefully) a web service. One benefit with using a web service for getting your data is the abstraction away from your DB (can now support multiple DB's for your product) and caching data that doesn't change much. This results in a HUGE performance improvement and more predictable DB accessing which DBA's love!

Answer 6

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

Assuming that somebody will have to maintain the code, because code changes with time, the app code is definitely more maintainable.

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

It's not only about the processing capacity of a DB server VS app server. How scalable is a DB? DB tend to scale well vertically ($$$) but not so well horizontally. Software applications can be very powerful and scale well horizontally if designed properly. This is a bit of a "depends" question. We tend to think that every project should be database centric and we start always designing an Entity Relational model as if every single problem had to be resolved with a relational database or as if the business logic should depend on the way the information is stored. It doesn't. If you follow DDD and a clean or hexagonal architecture, your business layer (domain) should be completely independent from the persistance layer (or any infrastructure layer). There are techniques to allow different models for write and read operations because Databases tend to be very good for write with consistency but not so good for read operations when the data is de-normalized and have to be joined to be consumed.

So, the answer is, it depends on your architecture and your approach. In my opinion a database is something to store information, not to build a solution for a business around it.

Which approach will be implemented faster ?

It depends on who is implementing it. For a complex business domain I doubt you can suffice using business logic in the database without hitting some wall. Also, considering that the techniques for automated testing are waaay better for App code (c#, Java, Scala, Javascript, whatever, ..) you will probably develop faster and better code if you avoid using store procedures and have the business logic in the app layer (where it belongs)

Which approach is safer ?

Again, it depends. Are you exposing your DB in the network? Is the DB only accessible by a single application? How do users access data?

I have seen many companies bury thousands of dollars due to having legacy code in databases. I have seen companies where a specific E-R design is the main restriction for the new scenarios in a business, where they go down the road of micro-performance, adding indexes, views, cache, etc. just because it's too late (or expensive, they think) to modify the structure or because they think there is not a single E-R model that satisfies all the features a business needs, and this is true in most cases and the best reason not to architect business apps around a database.

I have seen companies where the database is used as an integration platform where every team touches something, where nothing gets deleted because people are scared of affecting somebody else, where a change by somebody affects many others due to a lack of versioning strategies, etc.

I have seen enough never to ever think of placing business logic in a database for many reasons. Databases are designed to store and query data and should be used only for that.

Answer 7

I've been on both sides of this fence for almost 20 years now and IMHO the only correct answer to this question should be "That depends".

But let me eloborate on this per sub-topic.

Performance

Working with stored procs in the early days of SQL Server did result in faster query execution because of the way stored procs pre-compiled queries. But then the .NET Framework was introduced and using parameterized queries with ADO.NET gained a lot of ground because it was faster than classic ADO (they also improved OLEDB/JDBC/ODBC etc). As an example, last year I gave an in-house workshop on this particular subject with a team of SQL Server DBAs and a team of Java developers and I asked both teams to build a solution to produce a report would meet certain business requirements. A prize was to be awarded to the team with the most performant solution, but there was no winner! So basically what I'm saying here is that if your logical (and physical) data model is OK, there should be very little difference between using stored procs and parameterized queries.

Implementation Time

If your IT department mainly consists of experienced application developers with little SQL Server (or any other database) experience, one would be foolish to endeavor on going for stored procs all the way. For the alternative, the same logic applies: if you only have but a few junior web developers but lots of experienced DBAs, why not put them to good use? For example, currently at our shop we've chosen to use mainly stored procs for data access because the user interfaces are reasonably mature and the business requirements haven't changed over time: input parameters and required result sets don't change. However we are working a lot with external data sources and need to remodel parts of our physical data model quite often, this is where the use of stored procedures gives us the flexibility to change the tables and fields we use while ensuring the same output. Another important part of implementation and maintenance process is source control and testing. Source-controlling databases and unit-test writing is without the help of additional tooling and plugins not really fun and time consuming. It can be done, but to level the playing field with things like built-in Git support, you had to have the budget to buy the necessary tools for your database platform and the experience to make this work. Another thing you should consider here is Time To Market. Say your boss wants you to have the new product live in no time and he doesn't give a crap about the maintenance costs. My initial thoughts would then go out to prototying and RAD, using as little database coding as possible. But again, be mindful of the skillset available to you and your co-workers.

Security

In my experience chosing stored procs or not depends on what kind of authorization model you need and (again) what your expertise is. Stored procs can be very secure in use as you can grant execute permissions on a very fine-grained level without the need for users to access the database tables directly. The chance of SQL injection is virtually zero but the same principles can be applied to proper parameterized queries. Again, take a look at your knowledge and experience in terms of database and/or Windows administration (or Azure/AWS/Google/...). Most important: check your designated user groups and determine your possible surface areas of attack.

To summarize

I can't advise you on what you should chose whether or not the topic concerns the client or the other areas of expertise I mentioned, as the answer really depends on a lot of things like resources, experience, budget, maintainability, flexibility and business requirements and so on. However I do hope you can make sound, well-thought decisions and wish you good luck and success with you projects!

Answer 8

Which approach is safer?

The quality of security is not defined by the approach but by

• developer skills - most of the MVC frameworks comes with a lot of build-in security enhancement, but this does not mean one is not able to write injection-risk SQL ad-hoc query and in stored procedure one can concatenate input parameters in dynamic T-SQL statement and the injection-risk code is online again;
• administrators / devops / DB - the security depends on others - are latest version of the involved software used, are security patches installed, are SSL certificates renewed (in a company I have worked for a deprecated driver to connect the APP with the DB was used, it was too difficult to migrate the app to use a new one; latter, the driver was un-deprecated and new driver was release and still a migration was not done - the point is some leaks can't be solved by you)
• business needs - sometimes the business goals are so freak that you need to expose data or functionalities more then you should;

Which approach will be implemented faster?

How faster is the approach is defined by the final goal:

• create a simple app - go for the MVC - use as many as you need of the hundreds plugins which comes with every MVC framework and build the app for minutes;
• create a complex app - well, if the MVC plugins can get some work done fast like authorization, login pages, etc. why not to use them - and if something is not so generic, write the code by yourself;

Will there be any performance difference?

It depends on various factors, like:

• if the calculations are simple - working with small subset of data, not constantly modifying data and a lot of static content - may be no performance differences will be seen;
• developers skills - I have seen stored procedure modifying one row to be called in loop in the application thousands of times instead passing all the data and modify at batches;
• how complex are the statements - I have seen ORM libraries to translate some models interactions to ineffective SQL statement, too;
• if you need to perform aggregations and a lot of filtering, writing SQL then working with the models is faster (especially for huge amount of data);

Which approach is best for client?

Most of the clients have no technical knowledge. So, clients want the fast (the cheaper) approach - the MVC. I found difficult to persuade a client to invest more time (pay more money) in order to be prepare to handle heavy load or more data in the future and in some cases, heavy load never happens.

What I do?

I ask myself - is the task generic? Is the task solved thousands of times using particular plugin/gem? If yes - MCV, if not - do it myself.

I like using MCV frameworks and how one can build the application scaffold faster. For simple models I don't see a point to write the code alone. I don't believe a ORM library can generate a complex SQL statements better than me. For complex queries, when a lot of filtering and aggregations are involved I like writing a stored procedure, debugging and tuning it.

Answer 9

If you can clean your data off of possible SQL injection attacks, having the models in your code as classes, will end up being far easier to understand, test and debug in the long run. Also, it answers a few other problems. What if you want to change your database, what if your old database completely dies for some reason.