sql-server-2008 - 创建用于缓存记录的 View 或新表

Question

我在一个遗留应用程序中遇到了巨大的性能问题。

有一个搜索表单，用户可以在其中搜索具有给定值的记录。结果行包含 10 列。然后 SP 返回在任何列中包含该值的任何行。

这个 SP 使用 8 个表，其中一些表有大约百万条记录。每一分钟我都会获得一个新记录。该 SP 也进行寻呼。执行此 SP 有时需要大约 40 秒。

我所做的是，我创建了一个新表并使用来自该 SP 的查询将所有记录放在那里，但没有条件。当其中一个源表中有新的更新或更新时，我使用触发器并更新这个新的“缓存”表。现在等待这个新表的结果只需 1-3 秒。

有人遇到过这样的事情吗？

我的一位同事说我最好使用 View ，但每次我都会进行 JOINS。

你怎么看？还有别的办法吗？

Answer 1

临时表通常可以帮助您解决性能问题。一种方法可能是仅将您需要考虑的记录收集到临时表中，然后从连接到您未过滤的任何其他表的临时表中创建最终的选择语句。

例如，假设您要搜索的字段之一是 table1 中的 field1。首先向表 #table1 中插入您正在寻找的具有 field1 值的记录:

select PrimaryKeyTable1, Field1, Field2, Field3, etc...
into #table1
from table1
where Field1 = 'Whatever you are looking for'

即使对于大表，这也应该相当快，尤其是当您在 Field1 上有索引时。您对每个带有搜索字段的表都执行此操作，以收集具有您正在搜索的相关记录的所有记录。

然后您还需要确保将任何记录插入到您的临时表中，这些记录可能具有对任何其他临时表的外键引用。因此，假设您还使用上述方法构建了一个表 #table2，该表具有指向 table1 的外键，称为 PrimaryKeyTable1。您将像这样插入这些记录:

Insert into #table1
    (PrimaryKeyTable1, Field1, Field2, Field3, etc...)
select table1.PrimaryKeyTable1, table1.Field1, table1.Field2, table1.Field3, etc...
from table1
join #table2
on table1.PrimaryKeyTable1 = table2.PrimaryKeyTable1
where table1.PrimaryKeyTable1 not in
    (Select PrimaryKeyTable1 from #table1)

现在您还将在 #table1 中找到与 #table2 中的记录相匹配的任何记录，其中包含符合搜索条件的记录。您对所有具有相关外键的临时表执行此操作。您执行插入的顺序很重要；在收集外键引用记录时，确保在最后一个插入语句之后不引用任何临时表。

然后您可以简单地执行最后的选择语句，将实际表替换为您构建的临时表并消除所有搜索字段数据的过滤器。根据您的查询结构，可能会有其他优化，但这是一般的想法。

如果您已经探索了所有索引选项，但仍然没有帮助，MS SQL Server 具有“更改跟踪”功能，可能对您构建缓存表有用。您启用数据库以进行更改跟踪并配置您希望跟踪的表。 SQL Server 然后在表上的每次更新、插入、删除时创建更改记录，然后让您查询自上次检查以来对记录所做的更改。这对于同步更改非常有用，并且比使用触发器更有效。它也比制作自己的跟踪表更容易管理。这是自 SQL Server 2005 以来的一项功能。

How to: Use SQL Server Change Tracking

更改跟踪仅捕获表的主键，让您查询哪些字段可能已被修改。然后您可以查询这些键上的表连接以获取当前数据。如果您希望它捕获数据，您也可以使用 Change Capture，但它需要更多的开销并且至少需要 SQL Server 2008 企业版。

Change Data Capture