交易和报告数据库 - 如何？ [英] Transactional And Reporting Databases - How?

问题描述

当建立一个事务处理系统，其具有高度规格化DB中，运行报告样式的查询，或者甚至查询上显示的UI数据可以包括若干个连接，它在数据重场景可以并且通常确实，抗冲击性能。联接是昂贵的。

When building a transactional system that has a highly normalized DB, running reporting style queries, or even queries to display data on a UI can involve several joins, which in a data heavy scenario can and usually does, impact performance. Joins are expensive.

通常情况下，所支持的指导，你不应该运行这些查询过​​您的交易DB模式，而应该使用的是专为特定的UI观点或报告，其中省去了许多加入了一个非规范化的扁平化模式。数据复制没有在这种情况下的一个问题。

Often, the guidance espoused is that you should never run these queries off your transactional DB model, rather you should use a denormalized flattened model that is tailored for specific UI views or reports which eliminates the need for many joins. Data duplication is not an issue in this scenario.

这个概念是非常合情合理的，但我很少看到当专家们做出这些声明是究竟如何实现这一点。例如，（很坦率地说我倒是AP preciate一个例子使用任何平台）在上一个SQL Server后端运行的中型系统，您有一个标准化的交易模式。你也有一些报告和需要查询的网站。所以，创建一个报告数据库变平了标准化的数据。你如何同步保持这个？事务日志传送？如果是这样，你怎么变换，以适应在报告模型中的数据？

This concept makes perfect sense, but what I rarely see when experts make these statements is exactly HOW to implement this. For example, (and quite frankly I'd appreciate an example using any platform) in a mid sized system running on a sql server back-end you have a normalized transactional model. You also have some reports and a website that require queries. So, you create a "reporting" database that flattens up the normalized data. How do you keep this in sync? Transaction log shipping? If so, how do you transform the data to fit in the reporting model?

推荐答案

在我们的商店，我们建立了一个连续的自OLTP系统，以用于报告的另一个数据库服务器的事务复制。你不会希望使用日志传送用于此目的，因为它需要在每次恢复日志的时间在数据库上的排他锁，这将prevent用户运行报告。

In our shop, we set up a continuous transactional replication from the OLTP system to another DB server used for reporting. You wouldn't want to use log shipping for this purpose as it requires an exclusive lock on the database every time it restores a log, which would prevent your users from running reports.

随着今天在SQL Server中的优化，我觉得加入一个规范化的数据库的概念是太贵的报告是一个有点过时。我们的设计完全是第三范式，在我们的主桌几百万行，我们在运行我们的任何报告没有什么问题。说了这么多，如果推来推，你可以看看创造了一些索引视图您的报告服务器上提供帮助。

With the optimizer in SQL Server today, I think the notion that the joins on a normalized database are "too expensive" for reporting is a bit outdated. Our design is fully 3rd normal form, several million rows in our main tables, and we have no problems running any of our reports. Having said that, if push came to shove, you could look into creating some indexed views on your reporting server to help out.

这篇关于交易和报告数据库 - 如何？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！