什么时候在 SQL 中存储聚合数据有用 [英] When is it useful to store aggregated data in SQL

问题描述

我在一个项目中工作，我需要根据用户在网站上的交互来计算一些平均值.

I am working in a project where i need to calculate some avg values based on the users interaction on a site.

现在，需要计算总平均数的记录数量可以从几条到几千条不等.

Now, the amount of records that needs to have their total avg calculated can range from a few to thousands.

我的问题是，在哪个阈值下将聚合数据存储在单独的表中并通过存储过程在每次生成新记录时更新该值而不是每次需要时才计算它是明智的?

My question is, at which threshold would it be wise to store the aggregated data in a seperate table and through a store procedure update that value everytime a new record is generated instead of just calculate it everytime it is neede?

提前致谢.

推荐答案

不要这样做，直到您开始遇到由于聚合数据所需的时间而导致的性能问题.

Dont do it, until you start having performance problems caused by the time it takes to aggregate your data.

那就去做吧.

如果在生产中发现这个瓶颈是不可接受的，那么在与您的生产环境准确匹配的测试环境中运行系统并加载与生产数据准确匹配的测试数据.如果您在该环境中遇到由聚合时间引起的性能瓶颈，请执行此操作.

If discovering this bottleneck in production is unacceptable, then run the system in a test environment that accurately matches your production environment and load in test data that accurately matches production data. If you hit a performance bottleneck in that environment that is caused by aggregation time, then do it.

这篇关于什么时候在 SQL 中存储聚合数据有用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！