Allow predicate pushing on a view that uses group by

join;predicatequery-performancesql server

We have a table Ecom.McProductToVendorProductCodeMap that has a multi-field PK as shown:

Then a view wraps that table to compute a metric and is grouped by the first two fields of that PK:

ALTER view ECom.McProductToVendorProductMd5SourceView
as
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    -- sense any MaterialNumber/VendorProductCode/Status changes
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap 
 group by ClientAppPrivateLabelId, BrandId

Now if we select against the view using those 2 fields as predicates directly, then an index seek occurs using those 2 fields (19k rows and tool tip shows "Seek Predicate" on the 2 fields):

select * from ECom.McProductToVendorProductMd5SourceView
where ClientAppPrivateLabelId = 101 and BRandId = 3

However when trying to join against that same view using the same 2 predicates it only seeks on ClientAppPrivateLabelId and not BrandId . The loop join hint did not help and replacing the join with a cross apply also did not help.

select IsNull(convert(smallint, Value), 0) as BrandId 
  into #Brands 
  from string_split('2,3', ',');    
    
select ClientAppPrivateLabelId, b.BrandId, Md5, Count, ModifiedUtc
  from #Brands b
 inner loop join ECom.McProductToVendorProductMd5SourceView m
    on m.BrandId = b.BrandId
   and m.ClientAppPrivateLabelId = 101;

The view is simple except for windowing calc:

ALTER view ECom.McProductToVendorProductMd5SourceView
as
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    -- sense any MaterialNumber/VendorProductCode/Status changes
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap 
 group by ClientAppPrivateLabelId, BrandId

Why won't it use BrandId? The original table defines BrandId as a smallint non-nullable.

Paste-The-Plan:
https://www.brentozar.com/pastetheplan/?id=ryZWp86Hp

Update #1 (12/5/2023)

Converted the view to a Table Valued Function (TVF):

alter function ECom.McProductToVendorProductMd5(
   @pBrandId smallint,
   @pClientAppPrivateLabelId smallint
)
returns table as 
return
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  -- Sense any MaterialNumber/VendorProductCode/Status changes
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap m
 where m.BrandId = @pBrandId
   and m.ClientAppPrivateLabelId = @pClientAppPrivateLabelId
 group by ClientAppPrivateLabelId, BrandId

and Adjusted the query to use it via cross apply:

select ClientAppPrivateLabelId, b.BrandId, Md5, Count, ModifiedUtc
  from #Brands b
 cross apply ECom.McProductToVendorProductMd5(b.BrandId, @pCaplId) m;

Same problem: https://www.brentozar.com/pastetheplan/?id=SJnRODaBT

It is using a merge join and not seeking on BrandId

Best Answer

SQL Server is very keen to rewrite apply as a join before optimization begins. It is quite good at it too. It is less good at transforming a join to an apply, which is what you want here.

As a result, when you write a join, it stays as a join. When you write an apply, it gets transformed to a join.

There is no hint to avoid the initial rewrite from apply to join, though undocumented trace flag 9114 performs this function. Previous behaviours enabled by undocumented trace flags like this have eventually surfaced as USE HINT options, so perhaps this situation will change one day.

To work around this in the meantime, write the join as an apply and use an OUTER APPLY or redundant OFFSET to prevent the optimizer transforming the apply to a join.

SQL Server is capable of rewriting outer apply and OFFSET/TOP to a join in principle. It does not do this with OFFSET/TOP specifically because people have used this to avoid transformation to a join so often in the past. Outer apply is less amenable to transformation, but it can happen.

Outer apply

SELECT
    MA.ClientAppPrivateLabelId,
    B.BrandId,
    MA.Md5,
    MA.[Count],
    MA.ModifiedUtc
FROM #Brands AS B
OUTER APPLY
(
    SELECT
        M.* 
    FROM ECom.McProductToVendorProductMd5SourceView AS M
    WHERE
        M.ClientAppPrivateLabelId = 101
        AND M.BrandId = B.BrandId
) AS MA;

Redundant offset

SELECT
    MA.ClientAppPrivateLabelId,
    B.BrandId,
    MA.Md5,
    MA.[Count],
    MA.ModifiedUtc
FROM #Brands AS B
CROSS APPLY
(
    SELECT
        M.* 
    FROM ECom.McProductToVendorProductMd5SourceView AS M
    WHERE
        M.ClientAppPrivateLabelId = 101
        AND M.BrandId = B.BrandId
    ORDER BY
        M.ClientAppPrivateLabelId,
        M.BrandId
        OFFSET 0 ROWS
) AS MA;

If you want to encapsulate that inside your function, one possible implementation is:

CREATE OR ALTER FUNCTION ECom.McProductToVendorProductMd5
(
    @pBrandId integer,
    @pClientAppPrivateLabelId integer
)
RETURNS table
AS
RETURN 
    SELECT
        M.ClientAppPrivateLabelId,
        M.BrandId,
        Md5 = 
            CONVERT(char(32),
                HASHBYTES(N'MD5',
                    STRING_AGG(CSV.cols, ',')
                        WITHIN GROUP (ORDER BY M.MaterialNumber)), 
                2),
        [Count] = COUNT_BIG(*),
        ModifiedUtc = MAX(M.ModifiedUtc)
    FROM 
        ECom.McProductToVendorProductCodeMap AS M
    CROSS APPLY 
    (
        VALUES
        (
            CONVERT(varchar(max),
                CONCAT_WS(',', M.MaterialNumber, M.VendorProductCode, M.VendorProductStatusId))
        )
    ) AS CSV (cols)
    WHERE
        M.BrandId = @pBrandId
        AND M.ClientAppPrivateLabelId = @pClientAppPrivateLabelId
    GROUP BY
        M.ClientAppPrivateLabelId,
        M.BrandId
    ORDER BY
        M.ClientAppPrivateLabelId
        OFFSET 0 ROWS;

Related Solutions

Sql-server – Most efficient way to generate a diff

Here is another approach:

SELECT
  di.name,
  di.date,
  x.field,
  x.oldValue,
  x.newValue
FROM
  @diffInput AS di
  LEFT JOIN dbo.myTable AS mt ON
    mt.version = @version
    AND mt.name = di.name
    AND mt.date = di.date
  CROSS APPLY
  (
    SELECT
      'fieldA',
      mt.fieldA,
      di.fieldA
    WHERE
      NOT EXISTS (SELECT mt.fieldA INTERSECT SELECT di.fieldA)

    UNION ALL

    SELECT
      'fieldB',
      mt.fieldB,
      di.fieldB
    WHERE
      NOT EXISTS (SELECT mt.fieldB INTERSECT SELECT di.fieldB)

    UNION ALL

    SELECT
      'fieldC',
      mt.fieldC,
      di.fieldC
    WHERE
      NOT EXISTS (SELECT mt.fieldC INTERSECT SELECT di.fieldC)

    UNION ALL

    ...
  ) AS x (field, oldValue, newValue)
;

This is how it works:

The two tables are joined using an outer join, @diffInput being on the outer side to match your right join.
The result of the join is conditionally unpivoted using CROSS APPLY, where "conditionally" means that each pair of columns is tested individually and returned only if the columns differ.
The pattern of each test condition
```
NOT EXISTS (SELECT oldValue INTERSECT SELECT newValue)
```
is equivalent to your
```
oldValue != newValue
OR (oldValue IS NULL AND newValue IS NOT NULL)
OR (oldValue IS NOT NULL AND newValue IS NULL)
```
only more concise. You can read more about this use of INTERSECT in detail in Paul White's article Undocumented Query Plans: Equality Comparisons.

On a different note, since you are saying,

Both the input table and existing table are expected to be quite large over time

you may want to consider replacing the table variable you are using for the input table with a temporary table. There is a very comprehensive answer by Martin Smith that explores differences between the two:

What's the difference between a temp table and table variable in SQL Server?

In short, certain properties of table variables, like e.g. absence of column statistics, may make them less query optimiser-friendly for your scenario than temporary tables.

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.