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

by

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”.

More Related Contents:

  1. Efficient way to do batch INSERTS with JDBC
  2. Postgres data type cast
  3. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  4. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  5. Does Java JIT compiler sacrifice performance to favor Collections?
  6. Showing the order of growth of the function
  7. Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
  8. What is the relative performance difference of if/else versus switch statement in Java?
  9. Variable column names using prepared statements
  10. Java 8: performance of Streams vs Collections
  11. Technique or utility to minimize Java “warm-up” time?
  12. Performance difference between Java 8 lambdas and anonymous inner classes
  13. Queries returning multiple result sets
  14. How do you query object collections in Java (Criteria/SQL-like)?
  15. Strange branching performance
  16. Get execution time of PostgreSQL query
  17. Iterating through a List Object in JSP
  18. How can I Insert JSON object into Postgres using Java preparedStatement?
  19. Large Objects may not be used in auto-commit mode
  20. Is it possible to force an existing Java application to use no more than x cores?
  21. convert string date to java.sql.Date [duplicate]
  22. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  23. Automapper for Java [closed]
  24. Java JDBC connection status
  25. How can I improve performance of Field.set (perhap using MethodHandles)?
  26. Why are immutable objects in hashmaps so effective?
  27. Is there a workaround for Java’s poor performance on walking huge directories?
  28. How expensive are Exceptions [duplicate]
  29. Why is the 64bit JVM faster than the 32bit one?
  30. How to retrieve a list of directories QUICKLY in Java?

Leave a Comment