SQL触发器与其他选项的性能比较

Question

我在SQL中有一张表，其中保存着来自iFix SCADA系统的告警数据，其设计如下：

CREATE TABLE [dbo].[FIXALARMS](
    [ALM_ID] [int] IDENTITY(1,1) NOT NULL,
    [ALM_NATIVETIMEIN] [datetime] NOT NULL,
    [ALM_PHYSLNODE] [char](8) NOT NULL,
    [ALM_TAGNAME] [varchar](32) NOT NULL,
    [ALM_VALUE] [varchar](16) NOT NULL,
    [ALM_MSGTYPE] [varchar](8) NOT NULL,
    [ALM_DESCR] [varchar](128) NOT NULL,
    [ALM_ALMSTATUS] [varchar](4) NOT NULL,
    [ALM_ALMPRIORITY] [varchar](8) NOT NULL,
    [ALM_ALMAREA] [varchar](32) NOT NULL,
)

SCADA是指定列名和数据类型，并且没有其他选项将数据分割成多个表。基本上，我将被迫以这种格式接收信息，这已经变得很痛苦，因为我的许多查询都在对数据进行多个like和字符串比较，而这些数据实际上应该是id的。

现在，我想将此表规范化并拆分为多个具有键关系的表，以节省数据库空间，提高查询性能并增加一点灵活性。

最好使用数据库触发器来实现这一目标吗？数据库每天以约40Mb/天（~300k行）的速度增长，我缺乏SQL经验，所以我担心在添加条目时会给服务器增加额外的负载。相反，我应该使用代理程序定期清理表格吗？还有其他我可能不知道的选择吗？

- cmwarre

---

日记工厂的iFix - cmwarre

你的数据库以那种速度增长并不能说明这个特定表格（或者你在谈论规范化时所考虑的表格）会增长得有多快。当你在考虑触发器的性能影响时，主要与相关的表格有关。 - G. Stoynev

一个选项是将FIXALARMS作为视图，并在视图上使用INSTEAD OF INSERT触发器将数据切分到适当的表中。 （我还要指出，这种方法会导致疯狂，但它说你可以在我得到的这本新书的第666,666页上正确地执行它，名为“Necronomicon For Dummies”...）。 - Bob Jarvis - Слава Україні

那么你的意思是说，数据库增长不是一个好的性能衡量标准，对吗？ - cmwarre

数据库增长并不是很重要。当你考虑触发器时，你需要考虑添加的CPU/IO周期会对该表的吞吐量产生多大的影响。 - G. Stoynev

Answer 1

触发器会增加一些处理开销，可能会引入争用和事务锁。如果复杂的查询没有针对最新的完整数据执行，则可以使用定期计划的ETL过程来提取数据并将其转换为更可用的形式。您可以根据需要每天或每隔几小时安排ETL运行。

- crowne

---

更改数据捕获？ - Erran Morad

如果我试图针对完整的最新信息运行查询，那么使用ETL过程将信息移动，然后在与最新原始数据联合的视图中运行复杂的查询是否是一个好的实践？ - cmwarre

如果你要ETL原始数据，那么希望你不需要再回到它。原始数据听起来是非结构化的，ETL过程可以将所需内容复制到更适合查询的结构中（3NF或星型模式）。你可能会保留对原始数据的引用以进行审计，但应使用3NF或星型模式中的数据副本进行性能良好且易于维护的查询。填充新的清洁结构的方法取决于输出要求、实时触发器或定期批处理。 - crowne

Answer 2

假设您选择使用触发器，最有可能的是将传入数据分解成多个表，对吗？ 这是一种可行的方法。 如果传入数据与该表的模式匹配，则执行“常规”触发器。如果传入数据模式与您的模式不完全匹配，请考虑使用INSTEAD OF触发器。

无论如何，在您的触发器中，您将不得不在某些INSERT中生成键并在其他INSERT中重新使用它们。一些良好的实践是保持您的触发器业务逻辑自由 - 只在其中执行RDBMS操作。此外，请仔细考虑如何生成和读回键 - 要注意范围污染。最后，考虑您的事务隔离级别，并使事情尽可能快速，以保持吞吐量的平衡。

- G. Stoynev

Answer 3

如果您不需要数据实时性，则可以使用SSIS设置ETL过程将数据转换为规范化形式。

如果您必须实时获取数据，则使用触发器，但要非常谨慎，并确保它们处理数据集。SQL Server触发器永远不能期望只处理一行数据。您需要确保您的触发器代码尽可能具有良好的性能，因为您在从中抓取数据的表上拥有高数据输入。这意味着您需要阅读一本关于性能调优技术的好书，以便了解要避免使用的查询形式，例如相关子查询，非可搜索where子句和游标。您还需要进行昂贵的负载测试以查找阻塞问题。

- HLGEM