更高性能的具有标准化和规格化表 [英] more performant to have normalized or denormalized tables

问题描述

我目前正在开发一个MVC应用程序从现有的SQL Server数据库中读取。该数据库非规范化 - 我一直在寻找在修改一些表把它归到一个程度

I am currently developing an mvc application to read from an existing sql server database. The database is denormalized - and I was looking at modifying some tables to normalize it to a degree.

这导致了与一个同伴显影剂作为最preformant方式读取数据，或者如果结构应该改变与否的讨论。该数据将通过ado.net与存储过程中读取。问题我已经是，就是它具有更好的性能，以表中有许多领域（非规格化）或者有几个表与内部连接（标准化）来检索数据？

This led to a discussion with a fellow developer as the most preformant way to read the data, or if the structure should change or not. The data will be read via ado.net with a stored procedure. Question I have is, is it more performant to have numerous fields in a table (denormalized) OR have several tables with inner joins (normalized) to retrieve the data?

我应该提到，表上的行动将是95％的读取，5％写入。

I should have mentioned, the actions on the tables will be 95% read, 5% write.

推荐答案

您应该使用非规范化的数据进行数据分析的目的，密集申报使用。对于具有非规格化所提到的目的，将有助于提高性能。

Your should use denormalized data for data analysis purposes, intensive reporting usage. For the mentioned purposes having a denormalized will contribute to improve performance.

而不必了解你要实现，一个标准的客户端 - 服务器应用程序，在这里你都应该用一个DOM大量的类并读取和放大器的应用还有很多知识;不断地写入数据，我会去投票的数据标准化，避免尽可能维护复制数据。尝试让他们'放心'与您的域模型，以简化设计的表格。

Without having much knowledge about the application you are going to implement, for a 'standard' client-server application, where you are supposed to work with a DOM plenty of classes and read & write data constantly, I would go vote for normalizing the data and avoid as much as possible maintaining duplicated data. Try to simplify the design the of the tables so that they 'feel comfortable' with your domain model.

所以，简单地说，归更好的性能进行密集数据的读取，非规范化的更好的性能（多COM prehensive）进行密集读取​​和放大器;写域对象模型类的。

So, briefly, normalized more performant for intensive data read, denormalized more performant (and more comprehensive) for intensive read & write of domain object model classes.

这篇关于更高性能的具有标准化和规格化表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！