在 sql 中与在您的应用程序中执行计算的优缺点是什么 [英] What are the pros and cons of performing calculations in sql vs. in your application

问题描述

shopkeeper 表有以下字段:

id (bigint),amount (numeric(19,2)),createddate (timestamp)

比方说，我有上面的表格.我想得到昨天的记录通过将金额打印为美分来生成报告.

Let's say, I have the above table. I want to get the records for yesterday and generate a report by having the amount printed to cents.

一种方法是在我的 java 应用程序中执行计算并执行一个简单的查询

Date previousDate ;// $1 calculate in application

Date todayDate;// $2 calculate in application

select amount where createddate between $1 and $2 

然后遍历记录并将金额转换为我的java应用程序中的美分并生成报告

and then loop through the records and convert amount to cents in my java application and generate the report

另一种方式就像在 sql 查询本身中执行计算:

select cast(amount * 100 as int) as "Cents"
from shopkeeper  where createddate  between date_trunc('day', now()) - interval '1 day'  and  date_trunc('day', now())

然后遍历记录并生成报告

and then loop through the records and generate the report

以一种方式，我所有的处理都是在 Java 应用程序中完成的，并且会触发一个简单的查询.在其他情况下，所有的转换和计算都在 Sql 查询中完成.

In one way , all my processing is done in java application and a simple query is fired. In other case all the conversions and calculations is done in Sql query.

上面的用例只是一个例子，在实际场景中，一个表可以有许多需要类似处理的列.

The above use case is just an example, in a real scenario a table can have many columns that require processing of the similar kind.

你能告诉我哪种方法在性能和其他方面更好吗?为什么?

推荐答案

这取决于很多因素 - 但最重要的是:

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

• 计算的复杂性(更喜欢在应用服务器上进行复杂的运算，因为这样可以向外扩展；而不是数据库服务器，它可以向上扩展)
• 数据量(如果您需要访问/聚合大量数据，在数据库服务器上执行此操作将节省带宽，如果聚合可以在索引内完成，则可以节省磁盘 io)
• 方便(sql 不是处理复杂工作的最佳语言 - 尤其不适合程序工作，但非常适合基于集合的工作；不过，糟糕的错误处理)

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

请注意:

然后遍历记录

通过记录循环在 sql 中几乎总是错误的做法 - 首选编写基于集合的操作.

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?

如果你想要一个准确哪个更好"；以两种方式对其进行编码并进行比较(请注意，其中任何一个的初稿都可能不是 100% 调整的).但是考虑到典型用法:如果在现实中，它一次被调用 5 次(单独)，然后模拟:不要只比较单个这些中的 1 个与其中的 1 个".

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

这篇关于在 sql 中与在您的应用程序中执行计算的优缺点是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！