基于Web应用程序中的事务数据以可伸缩的方式计算结果？(Calculating results in a scalable way based on transaction data in web a

基于Web应用程序中的事务数据以可伸缩的方式计算结果？(Calculating results in a scalable way based on transaction data in web app?)

可能重复： 数据库设计：计算账户余额

我使用存储交易数据的网络应用程序（例如“日期y上的金额x”，但更复杂），并根据所有相关交易的详细信息提供计算结果[1]。 我们投入大量时间来确保这些计算有效执行，因为它们是应用程序的交互式部分：即用户单击按钮并等待查看结果。 我们有信心，对于当前的数据级别，我们可以在可接受的时间内优化数据库提取和计算。 但是，我担心随着交易数量的增加，所用的时间仍将呈线性增长[2]。 我希望能够说我们可以处理一个数量级的更多事务而不会导致性能过度下降。

我正在寻找有效的技术，技术，模式或算法，可以提高基于事务数据的计算的可伸缩性。

但是，对于任何建议都有实际和重要的限制：

我们目前必须支持两个高度不兼容的数据库实现，MySQL和Oracle。 因此，例如，使用数据库特定的存储过程大约是维护成本的两倍。 实际的事务比给出的示例事务更复杂，并且计算中涉及的业务逻辑很复杂，并且经常变化。 因此，将计算直接存储在SQL中并不是我们可以轻松维护的。 之前保存的任何交易都可以随时修改（例如，交易日期可以向前或向后移动一年），并且预计计算会立即更新。 这对缓存策略具有连锁效应。 用户可以在多个维度上跨越大空间进行查询。 为了解释，考虑能够计算任何特定交易类型在任何给定日期的结果，其中交易被几个任意条件过滤。 这使得难以预先计算用户想要看到的结果。 我们的应用程序的一个实例托管在客户的企业网络上，在其硬件上。 因此，我们不能轻易地在CPU和内存方面投入资金（即使这些实际上是瓶颈）。

我意识到这是非常开放的和一般的，但是......

是否有任何关于实现可扩展解决方案的建议？

[1]“相关”可以是：查询的日期; 交易类型; 用户类型; 配方选择; 等等 [2]不可否认，这是对先前性能的改进，其中ORM的n + 1问题使得时间呈指数增长，或者至少更陡峭的梯度。

Possible duplicate: Database design: Calculating the Account Balance

I work with a web app which stores transaction data (e.g. like "amount x on date y", but more complicated) and provides calculation results based on details of all relevant transactions[1]. We are investing a lot of time into ensuring that these calculations perform efficiently, as they are an interactive part of the application: i.e. a user clicks a button and waits to see the result. We are confident, that for the current levels of data, we can optimise the database fetching and calculation to complete in an acceptable amount of time. However, I am concerned that the time taken will still grow linearly as the number of transactions grow[2]. I'd like to be able to say that we could handle an order of magnitude more transactions without excessive performance degradation.

I am looking for effective techniques, technologies, patterns or algorithms which can improve the scalability of calculations based on transaction data.

There are however, real and significant constraints for any suggestion:

We currently have to support two highly incompatible database implementations, MySQL and Oracle. Thus, for example, using database specific stored procedures have roughly twice the maintenance cost. The actual transactions are more complex than the example transaction given, and the business logic involved in the calculation is complicated, and regularly changing. Thus having the calculations stored directly in SQL are not something we can easily maintain. Any of the transactions previously saved can be modified at any time (e.g. the date of a transaction can be moved a year forward or back) and calculations are expected to be updated instantly. This has a knock-on effect for caching strategies. Users can query across a large space, in several dimensions. To explain, consider being able to calculate a result as it would stand at any given date, for any particular transaction type, where transactions are filtered by several arbitrary conditions. This makes it difficult to pre-calculate the results a user would want to see. One instance of our application is hosted on a client's corporate network, on their hardware. Thus we can't easily throw money at the problem in terms of CPUs and memory (even if those are actually the bottleneck).

I realise this is very open ended and general, however...

Are there any suggestions for achieving a scalable solution?

[1] Where 'relevant' can be: the date queried for; the type of transaction; the type of user; formula selection; etc. [2] Admittedly, this is an improvement over the previous performance, where an ORM's n+1 problems saw time taken grow either exponentially, or at least a much steeper gradient.