Sitecore环境下跨分片表Slow Outer Apply SQL查询优化

问题背景 #

在Sitecore环境中，联系人数据分散存储在两个分片表（Shard0/Shard1）中，现有查询需从FormEntries表关联对应分片的ContactIdentifiers和ContactFacets表获取数据。当前查询可正常执行，但耗时超过30秒。经排查：

• FormEntries表通过过滤条件仅返回约10行数据，单表查询耗时不足1秒；
• 单独查询单个分片的ContactIdentifiers或ContactFacets表耗时均不足1秒；
• 每个联系人标识符仅存在于其中一个分片，原查询同时关联了两个分片的所有ContactFacets表，导致加载大量不必要的冗余数据。

原查询代码 #

SELECT fe.Id as SubmitionId, 
       fe.Created, 
       case when ci1.contactId is null then ci0.contactId else ci1.contactId end as [ExperienceProfileContactId], 
       case when ci1.contactId is null then cfCustom0.FacetData else cfCustom1.FacetData end as CustomFacetData,
       case when ci1.contactId is null then cf0.FacetData else cf1.FacetData end as PersonalFacetData
from [sitecore_forms_storage].[FormEntries] fe
     outer apply (select top 1 contactId from [Sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactIdentifiers] cii0 where replace(fe.contactid,'-', '') = cii0.[Identifier]) as ci0
     outer apply (select top 1 contactId from [Sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactIdentifiers] cii1 where replace(fe.contactid,'-', '') = cii1.[Identifier]) as ci1
     left outer join [Sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets] cf0 on ci0.ContactId = cf0.ContactId and cf0.FacetKey = 'Personal'
     left outer join [Sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets] cf1 on ci1.ContactId = cf1.ContactId and cf1.FacetKey = 'Personal'
     left outer join [Sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets] cfCustom0 on ci0.ContactId = cfCustom0.ContactId and cfCustom0.FacetKey = 'CustomData'
     left outer join [Sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets] cfCustom1 on ci1.ContactId = cfCustom1.ContactId and cfCustom1.FacetKey = 'CustomData'
WHERE FormDefinitionId = '19847d65-971d-4aa9-bc11-7c0c97fc4e0f'

优化方案 #

核心思路：先定位每个联系人所在的分片，仅关联对应分片的ContactFacets表，避免跨分片的无效关联操作。

优化后的查询代码 #

SELECT 
    fe.Id as SubmitionId, 
    fe.Created, 
    ci.ContactId as [ExperienceProfileContactId],
    cfPersonal.FacetData as PersonalFacetData,
    cfCustom.FacetData as CustomFacetData
FROM [sitecore_forms_storage].[FormEntries] fe
-- 优先从Shard1查询联系人，不存在则查Shard0，仅返回0或1行数据
OUTER APPLY (
    SELECT TOP 1 ContactId, 1 as ShardId
    FROM [Sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactIdentifiers]
    WHERE REPLACE(fe.contactid, '-', '') = [Identifier]
    UNION ALL
    SELECT TOP 1 ContactId, 0 as ShardId
    FROM [Sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactIdentifiers]
    WHERE REPLACE(fe.contactid, '-', '') = [Identifier]
) ci
-- 根据分片ID关联对应分片的Personal类型Facet
OUTER APPLY (
    SELECT FacetData
    FROM [Sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
    WHERE ci.ShardId = 0 AND ci.ContactId = ContactId AND FacetKey = 'Personal'
    UNION ALL
    SELECT FacetData
    FROM [Sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]
    WHERE ci.ShardId = 1 AND ci.ContactId = ContactId AND FacetKey = 'Personal'
) cfPersonal
-- 根据分片ID关联对应分片的CustomData类型Facet
OUTER APPLY (
    SELECT FacetData
    FROM [Sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
    WHERE ci.ShardId = 0 AND ci.ContactId = ContactId AND FacetKey = 'CustomData'
    UNION ALL
    SELECT FacetData
    FROM [Sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]
    WHERE ci.ShardId = 1 AND ci.ContactId = ContactId AND FacetKey = 'CustomData'
) cfCustom
WHERE fe.FormDefinitionId = '19847d65-971d-4aa9-bc11-7c0c97fc4e0f'

优化说明 #

1. 分片定位优化：通过UNION ALL合并两个分片的ContactIdentifiers查询，一次性获取联系人ID和所在分片标识，避免原查询中两次OUTER APPLY后的复杂CASE判断。
2. 精准关联Facet表：后续的Facet查询仅根据分片ID关联对应分片的表，完全避免了对另一分片表的无效扫描和关联，大幅减少冗余数据加载。
3. 空值处理兼容：若联系人在两个分片都不存在，ci会返回NULL，对应的Facet数据也会自动为NULL，符合需求。

额外性能提升建议 #

• 为ContactIdentifiers表的[Identifier]字段创建非聚集索引，加速分片定位查询；
• 为ContactFacets表创建复合非聚集索引(ContactId, FacetKey)，提升Facet数据的查询速度。

内容的提问来源于stack exchange，提问作者Brian Heward