Sql-server – How to effectively filter between several tables in SQL

sql server

I have a main table with another 7 tables attached to it by a key.
I have 10 different filter types in the secondary tables
I will not always have all types of filters in one QUERY, some of them may be equal to NULL.
The problem is that the secondary tables are very large and if they do not need to be accessed by the filter then it is best not to include them in the INNER JOIN,
How to make it the most effective?

Best Answer

You can use dynamic SQL to generate a query based on varied requirements. This way, you can exclude joins if you don't need them, like so:

DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'SELECT * FROM myTable AS t ';
IF @param1 IS NOT NULL
    SET @sql = @sql + 'JOIN mySecondTable AS s ON s.Id = t.mySecondTableId ';
IF @param2 IS NOT NULL
    SET @sql = @sql + 'JOIN myThirdTable AS x ON x.Id = t.myThirdTableId';
-- repeat as required to build a valid query

DECLARE @filters NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX) = '@filterParam1 INT, @filterParam2 NVARCHAR(128);'

IF @filterParam1 IS NOT NULL
BEGIN
   SET @filters = '[column] = @filterParam1';
END

IF @filterParam2 IS NOT NULL
BEGIN
  SET @filters = CASE WHEN @filters IS NULL 
                      THEN '' 
                      ELSE ',' 
                  END + '[column2] = @filterParam2';
END

DECLARE @where NVARCHAR(MAX);
IF @filters IS NOT NULL
BEGIN
   SET @where = 'WHERE ' + @filters;
   SET @sql = @sql + @where;
END

EXEC sp_ExecuteSql @stmt = @sql
                 , @params = @params
                 , @filterParam1 = @filterParam1
                 , @filterParam2 = @filterParam2;

Assuming that @param1 is null and @filterParam1 is also null, it would generate the following query:

SELECT * 
  FROM myTable AS t
  JOIN myThirdTable AS x on x.Id = t.myThirdTableId
 WHERE [column2] = @filterParam2;

Notice how the join to mySecondTable has not been included and the unnecessary filter has not been included either. You can find more examples of how to use sp_executeSql on Microsoft Docs.

Related Solutions

Sql-server – SQL Server: join two tables with “valid-to” dates

DECLARE @t1 TABLE (T1_key int, T1_Data char(1), T1_ValidUntil datetime)
DECLARE @t2 TABLE (T2_key int, T2_Data char(1), T2_ValidUntil datetime)

INSERT @t1 VALUES (1, 'A', '2000-01-01')
INSERT @t1 VALUES (1, 'B', '2000-06-30')
INSERT @t1 VALUES (2, 'C', '2005-05-31')
INSERT @t1 VALUES (3, 'D', '2004-12-31')
INSERT @t1 VALUES (3, 'E', '2007-04-30')
INSERT @t1 VALUES (3, 'F', '2008-01-31')

INSERT @t2 VALUES (1, 'R', '2002-03-31')
INSERT @t2 VALUES (2, 'S', '2001-06-30')
INSERT @t2 VALUES (2, 'T', '2003-02-28')
INSERT @t2 VALUES (2, 'U', '2005-05-31')
INSERT @t2 VALUES (3, 'V', '2006-09-30')
INSERT @t2 VALUES (3, 'W', '2007-06-30')

SELECT
   T1.*, T2x.*
FROM
   @t1 T1
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t2 
   WHERE T1_key = T2_key AND T2_ValidUntil >= T1_ValidUntil
   ORDER BY T2_ValidUntil
   ) T2x
UNION
SELECT
   T1x.*, T2.*
FROM
   @t2 T2
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t1
   WHERE T1_key = T2_key AND T1_ValidUntil >= T2_ValidUntil
   ORDER BY T1_ValidUntil
   ) T1x

Sql-server – Working Around Filtering Problems in Merge Replication

This is a complicated situation, but I think that the best solution would be to make an additional table (which I'll call UserFilters), just to hold the consolidated data from the Road, UserRegion and SyncSetting tables in a single combined row format and then to Join your Replication Filter to that table instead. This UserFilters table would need to be maintained by triggers on the constituent tables so that when rows in Road, UserRegion or SyncSetting change, the rows in UserFilters would be automatically changed to match. I will demonstrate how to do all of this below.

First a note; This table (UserFilters) is a de-Normalized table that is adjunct to your normal application data schema. That is, it is an operational artifact that is only used to manipulate the Replication technology into doing what you want, and is not part of your application's data design/schema. This use of denormalization is considered acceptable as it is solely to address technology limitations and is not directly used by your applications.

OK, here's how to do it:

1. Write Your Desired Filter Query In JOIN Form:

Here's your original filter query (unrolled, to make it easier to decompose):

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

We want to re-write this into a format with only JOINs and WHEREs, but no subQueries. (This can always be done but I won't go into how in this article.) Like so:

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. Create A View Consolidating All of the Filter Conditions

We use the JOIN clauses above to make a view that contains only the columns relevant to the Replication Filter, and that projects all of the rows necessary to completely describe all of the filter conditions. This is easier than it sounds, it should look like this:

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'

Note that we have also generalized the View's expression to cover all users. Also, we have commented out the SyncSetting "[table] = 'StreetLight'", in order to generalize this for all tables covered by SyncSetting and Road (which may or may not be valid, you'll have to decide that).

3. Create The UserFilters Table

Create the UserFilters table using the view as a guide for the columns. However, we'll want to add an IDENTITY column for key/index performance reasons:

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
)

Then populate it using the View:

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

For performance and locking reasons you'll want a lot of index paths. I cannot say for sure what they should be, but here's what I would start with:

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. Add Maintenance Triggers to the Constituent Tables

You will need to add triggers to the Road, UserRegion and SyncSetting tables to keep the contents of [adjUserFilters] in synch with these tables when they are modified. They should look like this: First the Road table:

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

The UserRegion table:

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

And finally, the SyncSetting table:

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. Create Your Replication Filter Query

Now you can create your Replication Query Filter for "user1" like so:

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

As requested, this is the script you would use to synchronize the adjUserFilters on a periodic basis, instead of from Triggers:

DELETE FROM adjUserFilters;

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

Off hand, I am not sure how you would insure that this runs right before or as part of the Merge Synchronization Job, but it probably can be done.