Best method of doing multiple calculation on a databse (.NET 2.0 VB)

Question

Hi

Is it better to create lots of SQL statements or to cache the data in asp .NET and manipulate the data from there?

cheers

Answer 1

You shouldn't store datareaders in cache, it can cause problems with garbage collection and hold open database connections. Working with data should be a get in/get out process each time.

Answer 2

It really depends on the application, the environment, the data you and the types of calculations. There is no great way to answer this without more info.

Answer 3

It depends on what the calculations might be ie if it can even be done easily in SQL or not, what the load is on each server, etc. If the database server isn't under much load and the webserver is then do it DB side and vice versa.

There is no one answer for every case.

Answer 4

Consider whether you will save anything on what will be sent over the network. For example, if you need to aggregate data (count, sum, etc) you are usually better of letting the sql server handle it. This way you only send a small bit of info. Try to join them in the same db call, so you don't end with multiple roundtrips (which would be actually worst because of the delays).

If you will still be pulling all the data for other reasons, then you might save some processing on the sql server by doing it at the application level. This lets you spread the load better.