DataSet v/s Database

Question

While designing applications it is a very good practice to have all the business logic in one place. So why then we sometimes have the business logic in stored procs? Can we fetch all data from the DB and store it in a DataSet and then process it? What would be the performance of the app in this scenario?

Answer 1

So why then we sometimes have the business logic in stored procs?

because sometime you need to do some processing before returning the data to the client, it would be wasteful to return a bunch of rows when you only need a subset. Some complex things are also easier to do in a stored proc when you have to involve temp tables or linked servers for example

Answer 2

Think of this basic example and it should answer your question:

If you had a database with GB's of data across a bunch of tables and you wanted to just get a simple customer record and join their orders, would it make sense to bring back to the client app or web server a GB of data to just get the 1KB you are actually looking for?

You want to minimize the amount of data being passed across to your application layer. You also want to let the processing of that data be done in the fastest possible place. Storing it in a dataset will not give you indexes and full text search options etc. There is a reason we use databases for storing and retrieving data or else simple flat file that we load at startup into memory would be all that is needed. If your app and data is small then this could be an alternative but in most cases it's not.

Answer 3

So why then we sometimes have the business logic in stored procs?

I guess the processing should be done, where it is more meaningful.

For example, if your application has some process for which it needs less input from application & more from database, it is better done at database level.

It also depends on things you are trying to do & support for such things at database level.
example: usage of regular expressions or mathematical functions.

Can we fetch all data from the DB and store it in a DataSet and then process it? What would be the performance of the app in this scenario?

I don't think it makes sense to have all the data from DB into application memory.
The answer depends on how much is the data? does it change often? how does your application behave, if the data changes in the database?

In general, if you have some kind of static data, it is ok to have it in application memory & not otherwise.

Answer 4

So why then we sometimes have the business logic in stored procs?

Another important reason is security. You might want to monitor access (especially write access) to data by implementing code in stored procedures. This way all access to data is tunneled through a stored procedure and suspicious access can be prevented

Answer 5

Both patterns exist.

If you need to do serious math and string manipulation or matrix math, you might end up pulling a good deal of the db into memory.

If you doing work that has a solution that can be solved with join and where clauses, then SQL is the appropriate place for that, even if one could make a philosophical argument that it could be categorized as business logic. Moving that set based logic into a middle tier would likely lead to the re-inventing of the relational database in the middle tier and it wouldn't be done well!

When the number of rows is very small (and always will be), then it doesn't really matter. SQL is a rather impoverished programming language compared to C# or java when it comes to unit testing, tooling support, etc, so if you have 100 rows in your database (and never will have much more!), then by all means, feel free to put those into some in memory data structure-- although even there, a database will still be more efficient and less buggy in areas like sorting.