What Are the Pros and Cons of Performing Calculations in SQL VS. in Your Application

What are the pros and cons of performing calculations in sql vs. in your application

It depends on a lot of factors - but most crucially:

• complexity of calculations (prefer doing complex crunching on an app-server, since that scales out; rather than a db server, which scales up)
• volume of data (if you need to access/aggregate a lot of data, doing it at the db server will save bandwidth, and disk io if the aggregates can be done inside indexes)
• convenience (sql is not the best language for complex work - especially not great for procedural work, but very good for set-based work; lousy error-handling, though)

As always, if you do bring the data back to the app-server, minimising the columns and rows will be to your advantage. Making sure the query is tuned and appropriately indexed will help either scenario.

Re your note:

and then loop through the records

Looping through records is almost always the wrong thing to do in sql - writing a set-based operation is preferred.

As a general rule, I prefer to keep the database's job to a minimum "store this data, fetch this data" - however, there are always examples of scenarios where an elegant query at the server can save a lot of bandwidth.

Also consider: if this is computationally expensive, can it be cached somewhere?

If you want an accurate "which is better"; code it both ways and compare it (noting that a first draft of either is likely not 100% tuned). But factor in typical usage to that: if, in reality, it is being called 5 times (separately) at once, then simulate that: don't compare just a single "1 of these vs 1 of those".

which is better? to do calculations using the programming language or using the database server

Depending on the data and complexity of the calculation, you might try performing a mix of the two. Use a simpler, less precise, but more expedient calculation (capable of taking advantage of indices) to reduce the results sent back for final processing.

For example, if the criteria is "5 miles away", instead of performing the equation involving geometric functions; you could calculate a bounding box. Retrieve the rows in that range, and then use the more complex function to exclude the "corners".

Alternately, if your server is powerful and expected clients relatively weak computationally speaking; it might be better to off load those calculations to the server anyway.

Edit: Another possibility is to put the more complex calculation in a HAVING clause, keeping the simpler one in the WHERE, that way it will only need performed on the ones that pass the simpler, more index friendly, filter.

Edit2: To give a generic example (since I am unacquainted with using long and lat; your equations probably need to account for the globe "wrap").

SELECT * 
FROM theTable
WHERE x BETWEEN [minX] AND [maxX] 
AND y BETWEEN [minY] AND [maxY]
HAVING POW(x-[originX], 2) + POW(y-[originY], 2) <= POW([distance], 2)
;

pros and cons of calculations on the db vs on the front end

This is a generic question and it is hard to give a generic answer. It really depends on what type of data you have, what database engine you use or what computations you do.

If you only do the standard stats like you show in your example (min, max, count, avg, sum), it is definitely preferred to use database. The main reasons are:

1. Less data transferred between db and your app (only a summary numbers vs the whole table(s) content).
2. Performance - db, if structured properly, has indexes and thus operations like count, min or max will be much faster than doing the same in the code that has no information about the structure of the data.
3. Caching - if you have many queries of the same type, db engine will most likely have implemented some type of caching and thus doesn't have to do the calculation every time, many requests can be served directly from the cache. However, as I've said at the top, this depends on the type of data and performed operations. Will work well with mostly static data, not so well with datasets that change often.

Hope it helps, I would suggest you to read more about how db works in general, what type optimisations are utilised and so on. Will give you more understanding what's happening under the hood and better idea of the implementation that suits your problem the best.

What are the pros and cons for using a database to store configuration information compared to a File System

Here is a summary of pros and cons

Pros for file:

1. Fast access to configuration data. (assuming no caching)
2. Each server can be configured differently (in a load balancing situation)
3. You already need a file for the database credentials so everything can be stored in one place.

Cons for file:

1. Configuration in a load balanced environment is difficult.
2. When adding settings under development, one most remember to move them into the files on every server in production.
3. Configuration must be writable by the webserver if you want to write a control panel to change the settings at runtime. Manipulating files in a control panel is a hassle due to timing issues and or locking.

Pros for database:

1. Load balancer can share configuration across the cluster
2. It's very easy to check on settings remotely or change them such as in phpMyAdmin or a straight sql client.
3. Control panel development becomes simple.
4. Performance impact can be mitigated by caching configuration in memcached or in a hash in memory.
5. Programmers are more likely to control settings rather than IT people or at least can be controlled through control panel.

Cons for database:

1. Performance maybe slow if you're fetching the settings continuously.
2. If you don't provide a tool, it might be more difficult for sysadmins to administer the product rather than a file. They might not be sql gurus.
3. Clustering is more of a pain.

This comes down to personal preference and any current or possible future requirements for providing easy configuration.

---

Related Topics