解决合并复制中的过滤问题

这是一个复杂的情况，但我认为最好的解决方案是创建一个附加表（我将其称为 UserFilters），将来自 Road、UserRegion 和 SyncSetting 表的合并数据保存在单个组合行格式中，并且然后将您的复制过滤器加入该表。此 UserFilters 表需要由组成表上的触发器维护，以便当 Road、UserRegion 或 SyncSetting 中的行发生更改时，UserFilters 中的行将自动更改以匹配。我将在下面演示如何执行所有这些操作。

首先是注释；此表 (UserFilters) 是一个非规范化表，它是您正常应用程序数据模式的附属表。也就是说，它是一个操作工件，仅用于操作复制技术以执行您想要的操作，而不是应用程序数据设计/架构的一部分。这种非规范化的使用被认为是可以接受的，因为它只是为了解决技术限制，而不是由您的应用程序直接使用。

好的，这是如何做到的：

1. 以 JOIN 形式编写您想要的过滤器查询：

这是您的原始过滤器查询（展开，以便更容易分解）：

select  * 
from    StreetLight 
where   roadId in 
    (
    select  Id 
    from    Road 
    where   RegionId in 
        (
        select  regionId 
        from    UserRegion 
        where   userid = 1
        )
    )
  and   exists 
    (
    select  1 
    from    syncsetting 
    where   userid  = 1 
      and   [table] = 'StreetLight'
    )

我们想将其重写为只有 JOIN 和 WHERE，但没有子查询的格式。（这总是可以做到的，但我不会在本文中讨论如何做到这一点。）就像这样：

SELECT  StreetLight.* 
FROM    StreetLight 
JOIN    Road        ON  Road.Id             = Streetlight.roadId
JOIN    UserRegion  ON  UserRegion.regionid = Road.RegionId
JOIN    syncsetting ON  syncsetting.userid  = UserRegion.userid
                    And syncsetting.[table] = 'StreetLight'
Where   UserRegion.userid = 1

2. 创建一个合并所有过滤条件的视图

我们使用上面的 JOIN 子句创建一个视图，该视图仅包含与复制过滤器相关的列，并投射所有必要的行以完全描述所有过滤器条件。这比听起来容易，它应该是这样的：

CREATE VIEW vwUserFilters As
SELECT
        Road.Id             As RoadId,
        Road.RegionId       As RegionId,
        UserRegion.userid   As UserId,
        syncsetting.[table] As syncTable
FROM    Road
JOIN    UserRegion  ON  UserRegion.regionid = Road.RegionId
JOIN    syncsetting ON  syncsetting.userid  = UserRegion.userid
--  Comment the next line to generalize for all tables
--                  And syncsetting.[table] = 'StreetLight'

请注意，我们还泛化了 View 的表达式以涵盖所有用户。此外，我们已经注释掉了 SyncSetting "[table] = 'StreetLight'"，以便为 SyncSetting 和 Road 所涵盖的所有表格概括这一点（这可能有效也可能无效，您必须决定这一点）。

3. 创建 UserFilters 表

使用视图作为列的指南创建 UserFilters 表。但是，出于键/索引性能的原因，我们希望添加一个 IDENTITY 列：

CREATE TABLE adjUserFilters 
(
    filterId    INT Identity(1,1) PRIMARY KEY,
    RoadId      INT         NOT NULL,
    RegionID    INT         NOT NULL,
    userid      INT         NOT NULL,
    syncTable   VARCHAR(32) NOT NULL
)

然后使用视图填充它：

INSERT INTO adjUserFilters 
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters

出于性能和锁定原因，您需要很多索引路径。我不能确定它们应该是什么，但这是我要开始的：

CREATE UNIQUE INDEX IX_adjUserFilters 
    ON dbo.adjUserFilters(userid, syncTable, RoadId)
CREATE INDEX IX_adjUserFilters_1 
    ON dbo.adjUserFilters(RegionID, userid)
CREATE NONCLUSTERED INDEX IX_adjUserFilters_2 
    ON dbo.adjUserFilters(RoadId, userid)

4. 向成分表添加维护触发器

您需要向 Road、UserRegion 和 SyncSetting 表添加触发器，以在修改这些表时使 [adjUserFilters] 的内容与这些表保持同步。它们应该如下所示： 首先是 Road 表：

CREATE TRIGGER dbo.trRoad_MaintainUserFilters_IUD 
   ON  dbo.Road
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   RoadId IN(Select d.Id From deleted As d)

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable 
    FROM    vwUserFilters As v
    JOIN    inserted      As i  ON i.Id = v.RoadId
END

用户区域表：

CREATE TRIGGER dbo.trUserRegion_MaintainUserFilters_IUD 
   ON  dbo.UserRegion
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   EXISTS(
        Select * From deleted As d 
        Where   d.UserId    = adjUserFilters.userid
          And   d.RegionId  = adjUserFilters.RegionId
        )

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable 
    FROM    vwUserFilters As v
    JOIN    inserted      As i  
        ON  i.UserId    = v.userid
        And i.RegionId  = v.RegionId
END

最后是 SyncSetting 表：

CREATE TRIGGER dbo.trSyncsetting_MaintainUserFilters_IUD 
   ON  dbo.syncsetting
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   EXISTS(
        Select * From deleted As d 
        Where   d.UserId    = adjUserFilters.userid
          And   d.[table]   = adjUserFilters.syncTable
        )

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable 
    FROM    vwUserFilters As v
    JOIN    inserted      As i  
        ON  i.UserId    = v.userid
        And i.[table]   = v.syncTable
END

5. 创建您的复制过滤器查询

现在您可以为“user1”创建复制查询过滤器，如下所示：

select  StreetLight.* 
from    StreetLight     As t
JOIN    adjUserFilters  As f  
    ON  f.syncTable = 'StreetLight'
    And f.RoadId    = t.RoadId
Where   f.userid    = 1 
-- (corrected)

根据要求，这是您将用于定期同步 adjUserFilters 的脚本，而不是来自触发器：

DELETE FROM adjUserFilters;

INSERT INTO adjUserFilters 
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters

顺便说一句，我不确定您将如何确保它在合并同步作业之前或作为合并同步作业的一部分运行，但它可能可以完成。

我必须发出警告。我们现在已经远离了合并复制，我不得不建议上面的方案可能是一个主要的性能问题。

例外情况要求您拥有一个小型发布，其中包含少量过滤和/或不是多层深度的过滤器。例如 10 - 100 篇文章可能工作正常。如果您过多地推动上述方案，您可能会遇到性能/锁定问题。

每次将记录插入顶部过滤表时，合并复制触发器都必须处理所有子表。它还将记录添加到所有子表的 MSMerge_contents 和 MSMerge_genhistory。您拥有的子表越多，如果它们中有大量记录，则需要更多的处理能力。

我们遇到了 sp_MSsetupbelongs 太慢和超时的问题。最后我们得出的结论是，我们过度推动了合并复制，并且这项技术对我们不起作用。

这导致我建议，如果开箱即用的合并复制中的过滤方案对于您的情况不够灵活，那么要么不要过滤，要么不要使用合并复制。测试测试测试当然，每种情况都不同。