SQL Calculated field on details

[Hamster]

Right, the reason you said ORM. I hate bloody ORMs that allow a dev to do whatever the hell he wants with the data. But to each their own.

/moving along

PS. Wrt the above I'd prefer doing it server side and lessen the network traffic. I'm assuming of course that doing it application side/in memory means bring back the entire unfiltered dataset.

 

[[)roi(]]

A question for dev's here that use an ORM.

Is it better to have the query execute on the SQL server or in memory in the application.

StackOverflow does it in memory.

My comment is unrelated to that. Rather attacking any general "rule" that you "don't store what you can compute"; an assumption that all computation is equal; the rule should instead be something like: "don't store anything that has a low compute cost"

 

[Hamster]

My comment is unrelated to that. Rather attacking any general "rule" that you "don't store what you can compute"; an assumption that all computation is equal; the rule should instead be something like: "don't store anything that has a low compute cost"

How do you decide that something with a low compute cost will remain low cost when other factors change - like load?

 

[[)roi(]]

How do you decide that something with a low compute cost will remain low cost when other factors change - like load?

That should be taken into consideration with system / process design; costly to compute: whether due to load, formulation or some other factor; these should form part of the reasoning behind how an application and its internal processes are architected:

• just as you wouldn't post a ton of records across to the client for client-side processing,
• you similarly wouldn't unnecessarily reprocess large datasets, simply to avoid storing values that could theorectically be recomputed.

In the broader sense: there are many scenarios that call for the storage of computed values, for example:

• accounting
• statistics
• encryption

 

[Oppiekoffie]

How do you decide that something with a low compute cost will remain low cost when other factors change - like load?

Look at the usage of that value...eg like calculating interest. You start of with one rule and its oh, calculate it, its quick, further down the line, theres 10 more rules. Store it. Actually, think of it, storage has become so cheap that you can actually store the values to avoid unnecessary calcs. Back in the day, 15-20 years ago storage was expensive. Now you have to do a thorough analysis is it better to store it than to calculate it every time.

 

[gkm]

An example.

Executes on SQL Server
var result = db.Table.Where(x=> x.Subject == subject).ToList();

In memory
var result = db.Table.ToList();
var matches = result.Where(x=> x.Subject == subject).ToList();

This kind of thing you should almost always do server side. Pulling all the data over the network puts a lot of extra stress on the DB server, the network and the client application's memory. It is almost always a bad idea.

Only exception is if you are going to do a lot of "matches" where you are going to access pretty much all the data multiple times, then one extract from the database is nicer to the DB. This mostly applies in ETL or MapReduce situations. Otherwise, always be super nice to your database and network, since they are the hardest components in any system to scale up.

 

[P00HB33R]

Thanks for everyone's input. I decided to do calculations on the GUI side of my app, so not storing calculated values in DB.
Then calculating totals with query when running/printing the report (invoice)

Again, thanks for all the input