当非规范化是必不可少的 [英] When denormalizing is essential
问题描述
我有一个包含超过450个表的数据库，直到很久以前
一直是严格的3NF。如果系统的某些部分需要快速复杂的b / b $ b数据排列，有时候由于复杂性的原因，不可能足够快地编写查询。 br />
导出许多嵌套级别的数据。
因此，对于需要这种情况的少数情况，我要小心创建
命名摘要表，与真实表（相同的主键）具有一对一的
关系，并且仅包含
必须计算的值。
这很好用。现在的问题是何时计算它们。
由于我非常不喜欢触发器，我已经为所讨论的
表创建了触发器，它们自己重新计算了汇总值，
并填写汇总表。
好消息，加载速度要快得多。
坏消息，现在我正在点击保存，保存很远
太慢了。
唯一可以看到我的保存可以加速的方法起来，或许是为了b $ b扩展工作，这样可能如果我在表中添加了50条记录，那么触发器在某行之前被禁用了
添加，然后重新启用，计算该批次为基于集合的
操作（而不是调用触发器50次），但我没看到
a这样做的方式。
任何性质的帮助都会非常感谢
干杯
Brian McGee
" Brian McGee" <峰; br ********* @ Sentrio.com>在消息中写道
news：83 ************************** @ posting.google.c om ...我有一个包含450多个表的数据库，直到很久以前
已严格为3NF。如果系统的某些部分需要快速复杂的数据排列，有时候由于导出许多嵌套级别的复杂性，不可能足够快地编写查询。因此，对于需要这种情况的少数情况，我已经创建了精心命名的汇总表，与真实表有一对一的关系（相同）主键），并且只包含必须计算的值。
这很好用。现在的问题是何时计算它们。
您是否考虑将这些视图创建为
视图（甚至是内联函数）某些情况）
因为我非常不喜欢触发器，我已经为有问题的表创建了触发器，它们自己重新计算汇总值，并填充汇总表。好消息，加载速度要快得多。
坏消息，既然我正在保存，那么保存太慢了。
我可以看到我的保存可以加速的唯一方法是，或许可以稍微分散工作，这样可能如果我要将五十条记录添加到一张桌子上，那就是在添加行之前以某种方式禁用触发器，并在之后重新启用，以将该批次计算为基于集合的操作（而不是将触发器调用50次），但我不是请参阅
一种方法。
任何性质的帮助都可以非常感谢
您的触发器如何动作
糟糕 - 这次我会尝试完成我的电子邮件，并添加一个大写''S'，
而不是（S）结束消息
" Steven Wilmot" < ST ********* @ wilmot.me.uk>在消息中写道
news：40 *********************** @ news.aaisp.net.uk ..。
Brian McGee <峰; br ********* @ Sentrio.com>在消息中写道
新闻：83 ************************** @ posting.google.c om ...我有一个包含450多个表的数据库，直到很久以前
已严格遵守3NF。如果系统的某些部分需要快速复杂的数据排列，有时候由于导出许多嵌套级别的复杂性，不可能足够快地编写查询。数据的。
啊 - 对无法快速写入查询的误解并且
无法让查询快速执行...哎呀
因此对于需要这个的少数情况，我已经创建了
精心命名的汇总表，与真实表（相同的主键）具有一对一的关系，并且只包含必须计算的值。
这很好用。现在的问题是何时计算它们。
而不是将它们创建为表格，您是否考虑将这些视图创建为
视图（甚至在某些情况下创建内联函数）
忽略我上面的评论 - 我误读了你的第一篇文章
就像我一样非常不喜欢触发器，我已经为有问题的表创建了触发器，它们自己重新计算汇总值，
并填充汇总表。
好消息，加载速度要快得多。
坏消息，既然我正在拯救，节省的速度太慢了。
我唯一可以看到我的保存可以加快速度，或许可以稍微扩展一下工作，这样可能如果我在表中添加50条记录，那么在添加行之前触发器会以某种方式被禁用，并在之后重新启用，将该批次计算为基于集合的操作（而不是将触发器调用50次），但我没有看到
这样做的方法。
任何关于此事的任何性质的帮助都将非常感谢
你的触发器如何行动
如何编写触发器？它们是针对整个
插入/删除的虚拟表而设计的，还是它们在每一行中都有光标...
更糟糕的是，它们是否仅适用于单个行行。
您在插页上注意到了多少性能影响？有一个
的可能性，简化触发器的操作可能会使事情变得更容易。
此外，您的摘要信息有多重要分钟。
一个optiouns是一个简单的触发器，它将所有已更改的记录放在
" queue-table"中，然后有一个每分钟处理一次这个
队列表中的任何内容的工作。
希望你能考虑一些想法。
Steven
Brian McGee（br*********@Sentrio.com）写道：因此在需要这种情况的少数情况下，我已经创建了精心命名的汇总表，与真实表（相同的主键）具有一对一的关系，并且仅包含
值必须计算。
Steven Wilmot首先提出了意见，然后撤回了它，当他实现了你的范围时。
但您考虑*索引*观点吗？也就是说，实现的视图，
然后SQL Server将维护它们。当然，这需要一个
的更新开销，但没有触发器那么大。
另一方面，远非一切都可以实现为索引视图，
所以这可能不是解决方案。但它值得调查。
更多详情请见联机丛书中的CREATE INDEX主题。
-
Erland Sommarskog，SQL Server MVP， so****@algonet.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
I have a database with over 450 tables, that until a short while ago
has been strictly 3NF. In cases where complicated permutations of
data are required quickly for certain parts of the system, sometimes
it is not possible to write a query fast enough, due to the complexity
of deriving many nested levels of data.
Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.
This works fine. The question now is when to compute them.
Much as I strongly dislike triggers, I have created triggers for the
tables in question, which themselves recalculate the summary values,
and populate the summary tables.
Good news, loading is much faster.
Bad news, now that I''m taking the hit on the save, the saves are far
too slow.
The only way I can see that my saves could be sped up, is to perhaps
spread the work a bit, such that maybe if I am adding fifty records to
a table, that the trigger is somehow disabled before the rows are
added, and re-enabled afterwards, to calculate the lot as a set-based
operation (rather than calling the trigger 50 times), but I don''t see
a way to do that.
Any help of any nature on this matter would be greatly appreciated
Cheers
Brian McGee
"Brian McGee" <br*********@Sentrio.com> wrote in message
news:83**************************@posting.google.c om...I have a database with over 450 tables, that until a short while ago
has been strictly 3NF. In cases where complicated permutations of
data are required quickly for certain parts of the system, sometimes
it is not possible to write a query fast enough, due to the complexity
of deriving many nested levels of data.
Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.
This works fine. The question now is when to compute them.
Instead of creating these as tables, have you considered creating these as
views (or even inline-functions in certain cases)
Much as I strongly dislike triggers, I have created triggers for the
tables in question, which themselves recalculate the summary values,
and populate the summary tables.
Good news, loading is much faster.
Bad news, now that I''m taking the hit on the save, the saves are far
too slow.
The only way I can see that my saves could be sped up, is to perhaps
spread the work a bit, such that maybe if I am adding fifty records to
a table, that the trigger is somehow disabled before the rows are
added, and re-enabled afterwards, to calculate the lot as a set-based
operation (rather than calling the trigger 50 times), but I don''t see
a way to do that.
Any help of any nature on this matter would be greatly appreciated
How are your triggers act
Oops - I shall try and complete my email this time, and add a capital ''S'',
rather than "(S)end message"
"Steven Wilmot" <st*********@wilmot.me.uk> wrote in message
news:40***********************@news.aaisp.net.uk.. .
"Brian McGee" <br*********@Sentrio.com> wrote in message
news:83**************************@posting.google.c om...I have a database with over 450 tables, that until a short while ago
has been strictly 3NF. In cases where complicated permutations of
data are required quickly for certain parts of the system, sometimes
it is not possible to write a query fast enough, due to the complexity
of deriving many nested levels of data.
Ah - misunderstanding of "not possible to write the the query quickly" and
not possible to get the query to execute quickly-enough ... Oops
Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.
This works fine. The question now is when to compute them.
Instead of creating these as tables, have you considered creating these as
views (or even inline-functions in certain cases)
Ignore my comment above - I mis-read your first post
Much as I strongly dislike triggers, I have created triggers for the
tables in question, which themselves recalculate the summary values,
and populate the summary tables.
Good news, loading is much faster.
Bad news, now that I''m taking the hit on the save, the saves are far
too slow.
The only way I can see that my saves could be sped up, is to perhaps
spread the work a bit, such that maybe if I am adding fifty records to
a table, that the trigger is somehow disabled before the rows are
added, and re-enabled afterwards, to calculate the lot as a set-based
operation (rather than calling the trigger 50 times), but I don''t see
a way to do that.
Any help of any nature on this matter would be greatly appreciated
How are your triggers act
How are the triggers written ? Are they designed to work against the entire
inserted/deleted virtual tables, or do they cursor through each row ...
Worse still, do they only work on a single row .
How much of a performance hit have you noticed on the inserts ? There''s a
possibility that simplifying the trigger''s operation may make things easier.
Also, how important is it that your summary information is up-to-the minute.
One optiouns is a simple trigger that puts all changed records in a
"queue-table", and then have a job that processes anything in this
queue-table once a minute.
Hopefully a few thoughts for you to consider.
Steven
Brian McGee (br*********@Sentrio.com) writes:Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.
Steven Wilmot first suggested views and then retracted it, when he
realized your scope.
But did you consider *indexed* views? That is, views that are materialized,
and then SQL Server will maintain them. Of course this comes with an
overhead for updates, but not as big as for triggers.
On the other hand, far from everything can be implemented as indexed views,
so this may not be a solution. But it''s worth investigating.
More details are in the CREATE INDEX topic in Books Online.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于当非规范化是必不可少的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
