StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are few approaches:</p> <ul> <li>use sql query to calculate that on the fly - this way nothing is stored to the database</li> <li>use same or another table to perform calculation</li> <li>use calculated field</li> </ul> <p>If you have low database load (few queries per minute, few thousands of rows per fetch) then use first aproach. If calculation on the fly performs poorly (millions of records, x fetches per second...) try second or third aproach. Third one is ok if your db supports calculated and persisted fields, say MSSQL Server.</p> <p><strong>EDIT:</strong></p> <p>Typically, as others said, you will perform calculation in your query. That is, as long as your project is simple enough. </p> <p>First, when the table where you store all the items and their prices becomes attacked with insert/update/deletes from multiple clients, you don't want to block or be blocked by others. You have to understand that e.g. table X update will possibly block your select from table X until it is finished (look up page/row lock). This means that you are going to <em>love</em> parallel denormalized structure (table with product and the calculated stuff along with it). This is where e.g. reporting comes into play.</p> <p>Second, when calculation is simple enough (a*b) and done over not-so-many records, then it's ok. When you have e.g. 10M records and you have to correlate each row with several other rows and do some aggregation over some groups, there is a chance that calculated/persisted field will save your time - you can gain up to 10-100 times faster result using this approach.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload