什么是执行计算的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屋!