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

问题描述

店主表有以下字段：

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:

• 在复杂的计算（preFER做复杂的一手交钱一手交货的应用程序服务器上，因为这秤的出的;而不是一个数据库服务器，该服务器扩展的了）
• 在数据量（如果您需要访问/聚合了大量的数据，做它在数据库服务器将节省带宽和磁盘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 - 编写一个基于集合的操作是preferred

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

作为一般规则的，我preFER保持数据库的工作到最低限度存储这些数据，读取这些数据 - 然而，总有一些场景例子，一个优雅的查询在服务器可以节省大量的带宽。

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?

如果你想要一个的准确的哪个更好; code这两种方法，并进行比较（请注意，无论是初稿可能不是100％调整）。但是因素的典型用法是：如果在现实中，它被立刻叫5次（另发），然后模拟的是：不比较只是一个单一的1对这些VS 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屋！