Resolvendo Problemas de Filtragem na Replicação de Mesclagem

Esta é uma situação complicada, mas acho que a melhor solução seria criar uma tabela adicional (que chamarei de UserFilters), apenas para armazenar os dados consolidados das tabelas Road, UserRegion e SyncSetting em um único formato de linha combinado e em seguida, junte seu filtro de replicação a essa tabela. Essa tabela UserFilters precisaria ser mantida por gatilhos nas tabelas constituintes para que, quando as linhas em Road, UserRegion ou SyncSetting fossem alteradas, as linhas em UserFilters fossem alteradas automaticamente para corresponder. Vou demonstrar como fazer tudo isso abaixo.

Primeiro uma nota; Esta tabela (UserFilters) é uma tabela desnormalizada que é adjunta ao seu esquema de dados de aplicativo normal. Ou seja, é um artefato operacional usado apenas para manipular a tecnologia de replicação para fazer o que você deseja e não faz parte do design/esquema de dados do seu aplicativo. Esse uso de desnormalização é considerado aceitável, pois é apenas para abordar limitações de tecnologia e não é usado diretamente por seus aplicativos.

OK, aqui está como fazer isso:

1. Escreva sua consulta de filtro desejada no formulário JOIN:

Aqui está sua consulta de filtro original (desenrolada, para facilitar a decomposição):

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'
    )

Queremos reescrever isso em um formato com apenas JOINs e WHEREs, mas sem subConsultas. (Isso sempre pode ser feito, mas não vou explicar como neste artigo.) Assim:

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. Crie uma exibição consolidando todas as condições de filtro

Usamos as cláusulas JOIN acima para fazer uma exibição que contém apenas as colunas relevantes para o filtro de replicação e que projeta todas as linhas necessárias para descrever completamente todas as condições do filtro. Isso é mais fácil do que parece, deve ficar assim:

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'

Observe que também generalizamos a expressão da View para abranger todos os usuários. Além disso, comentamos o SyncSetting "[table] = 'StreetLight'", a fim de generalizar isso para todas as tabelas cobertas por SyncSetting e Road (que podem ou não ser válidos, você terá que decidir isso).

3. Crie a tabela UserFilters

Crie a tabela UserFilters usando a exibição como guia para as colunas. No entanto, queremos adicionar uma coluna IDENTITY por motivos de desempenho de chave/índice:

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
)

Em seguida, preencha-o usando a Visualização:

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

Por motivos de desempenho e bloqueio, você desejará muitos caminhos de índice. Não posso dizer com certeza quais deveriam ser, mas aqui está o que eu começaria:

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. Adicionar acionadores de manutenção às tabelas constituintes

Você precisará adicionar gatilhos às tabelas Road, UserRegion e SyncSetting para manter o conteúdo de [adjUserFilters] sincronizado com essas tabelas quando elas forem modificadas. Eles devem ficar assim: Primeiro, a tabela 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

A tabela UserRegion:

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

E, finalmente, a tabela 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. Crie sua consulta de filtro de replicação

Agora você pode criar seu filtro de consulta de replicação para "user1" da seguinte forma:

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

Conforme solicitado, este é o script que você usaria para sincronizar os adjUserFilters periodicamente, em vez de Triggers:

DELETE FROM adjUserFilters;

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

De imediato, não tenho certeza de como você garantiria que isso fosse executado logo antes ou como parte do trabalho de sincronização de mesclagem, mas provavelmente pode ser feito.

Devo lançar um aviso. Agora nos afastamos da replicação de mesclagem e devo sugerir que o esquema acima pode ser um grande problema de desempenho.

A exceção a isso exigiria que você tivesse uma pequena publicação com pequenas quantidades de filtragem e/ou filtros que não tivessem vários níveis de profundidade. Por exemplo, 10 a 100 artigos podem funcionar bem. Se você forçar demais o esquema acima, poderá ter problemas de desempenho/travamento.

Toda vez que você insere um registro na tabela de filtragem superior, o gatilho de replicação de mesclagem deve processar todas as tabelas filhas. Ele também adiciona registros a MSMerge_contents e MSMerge_genhistory para todas as tabelas filhas. Quanto mais tabelas filhas você tiver, e se elas tiverem uma grande quantidade de registros nelas, mais poder de processamento será necessário.

Tivemos um problema com sp_MSsetupbelongs sendo muito lento e expirando. No final, chegamos à conclusão de que estávamos pressionando demais a replicação de mesclagem e que essa tecnologia não funcionaria para nós.

Isso me leva a sugerir que, se o esquema de filtragem na replicação de mesclagem pronta para uso não for flexível o suficiente para sua situação, não filtre ou não use a replicação de mesclagem. Teste Teste Teste embora, é claro, cada situação seja diferente.