FOR XML PATH性能改进挑战

Question

我正在改进视图性能，该视图使用了带有FOR XML PATH('')函数的20列。该视图还使用非聚集视图CTE、子查询和CAST函数调用其他字段，但目前我并不关心它们。

该视图是非聚集视图，每5分钟由作业选择一次，以向客户端应用程序显示新数据。因此，底层源表每5分钟更新一次并插入一次。

我已经在适当的地方创建了聚集索引和非聚集索引。在创建相应索引之前和之后，对源表上的各个视图组件进行了测试，并选择了最佳路由。所以，我在索引方面做得很好。填充因子值几乎在所有索引上都是100。

我的假设是，由于我在视图中的20列上使用FOR XML PATH(")，所以查询速度慢了很多。。。

CREATE VIEW MyView
AS 
col1,
Col2,
(SELECT CAST(Mytbl.[EmpId] AS NVARCHAR(50)) + '|' FROM MyDB.dbo.Mytbl_Optimized AS t1 (NOLOCK) 
LEFT OUTER JOIN AnotherDB.dbo.Another-tbl AS t2 WITH (NOLOCK)
ON t1.EmpId = t2.EmpId 
WHERE AnotherDB.dbo.Table3.MyId = t1.MyId
FOR XML PATH('')) AS MyConcatenatedID
FROM AnotherDB.dbo.Table3 

我尝试使用CASE语句来确定具有FOR XML PATH(")的每一列是否有任何要逐行连接的内容，然后仅当is有2个或多个值要连接到字符串时才使用FOR XML PATH。但正如我所期望的那样，表现很糟糕。。。

,CASE
WHEN 
(SELECT
LEN(EmpId) - LEN(REPLACE(EmpId, '|', '')) AS [CountOfConcatinated_EmpId] 
FROM ISSearch..SearchBid WITH (NOLOCK)
) > 1  -- this determis if values are concatenated or not. 
THEN 
(SELECT CAST(Mytbl.[EmpId] AS NVARCHAR(50)) + '|' FROM MyDB.dbo.Mytbl_Optimized AS t1 (NOLOCK) 
LEFT OUTER JOIN AnotherDB.dbo.Another-tbl AS t2 WITH (NOLOCK)
ON t1.EmpId = t2.EmpId 
WHERE AnotherDB.dbo.Table3.MyId = t1.MyId
FOR XML PATH(''))
ELSE
(SELECT CAST(Mytbl.[EmpId] AS NVARCHAR(50)) + '|' FROM MyDB.dbo.Mytbl_Optimized AS t1 (NOLOCK) 
LEFT OUTER JOIN AnotherDB.dbo.Another-tbl AS t2 WITH (NOLOCK)
ON t1.EmpId = t2.EmpId 
WHERE AnotherDB.dbo.Table3.MyId = t1.MyId)
END AS EmpId 
FROM AnotherDB.dbo.MyView;

我现在正在考虑将所有20列的FOR XML PATH(")函数字符串作为视图上的独立列进行缓存，但它需要不断更新(每5分钟更新一次)。

有什么想法或替代方案吗？

Answer 1

模糊的问题得到模糊的答案，但这里是。

我同意，似乎所有的FOR XML连接都可能起到了作用(当然，在没有看到执行计划或完整的数据库模式的情况下，这几乎是我唯一要做的事情)。这里有很多未知因素可能会改变建议。如果您可以包括查询计划和IO统计信息，这将有所帮助。

我也不知道为什么或者是否需要在运行时使用视图或连接值，但这里有一些探索性的问题：

1. dbo.Table3(连接值的基表)中有多少行
2. 每个MyConcatenatedId值中连接了多少行
3. 它现在的表现有多慢，需要有多快
4. 这个视图大约每分钟/秒读取多少次
5. dbo.another-tbl在提供的子查询中似乎没有做任何事情(可能在实际版本中做了)。你需要这个吗？它需要是左联接吗

我最初的想法是，如果这需要表演性的，而不是，就不要使用视图。如果你必须使用视图，也许可以使用NOEXPAND之类的索引提示。你有什么替代方案？

不要序列化：我很难想象在运行时需要序列化高容量数据的场景。如果可能的话，返回数据集或在数据放入时序列化数据

• 优点
  • 数据库端轻松
• Cons
  • 对于你需要做的事情来说，这可能是不可能的

持久化表/提前完成工作：可能也需要触发器，但当底层数据发生变化时，将其写入持久化表，然后从中读取。或者在变更数据捕获中排队更改，然后异步处理这些更改

• 优点
  • 阅读速度快
• Cons
  • 维护和触发的痛苦可能会增加繁重的开销

过程：除非每次调用都需要所有数据，否则请考虑创建一个可以参数化的过程，以便在需要时只提取所需的数据。视图仅限于一个查询，这种复杂性的查询的统计信息可能很快就会变成垃圾。一个过程可以将语句单独或小块地分离出来，这样你就可以更频繁地获得一致、更快的计划。

• 优点
  • 比视图更具可定制性，您可以做更多优化
• Cons
  • 无法直接查询它们(尽管我认为这不是不使用它们的好理由)