Why is there no predicate applied to the Index Scans?

Question

We're having problems with some queries which are similar to this:

SELECT COUNT('A')  FROM  [dbo].[OINV] T0  
INNER  JOIN [dbo].[OCRD] T2  ON  T2.[CardCode] = T0.[CardCode]   
WHERE T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

Query Plan

The indexes that it's hitting are defined as:

NONCLUSTERED INDEX [OCRD_FATHER] ON [dbo].[OCRD]
(
    [FatherCard] ASC
) INCLUDE (CardCode)

NONCLUSTERED INDEX [OINV_CUSTOMER] ON [dbo].[OINV]
(
    [CardCode] ASC
)

They're currently taking 1-2 seconds to run, and returns a count of 0 (which is what we're expecting).

I'm incredibly surprised that it's not filtering the NonClustered Indexes before it feeds into the Hash Match - it's feeding every single row. These are vendor-software queries, so unfortunately there's no way for us to rewrite them.

Why is this, and is there any way to change it to filter before it does the Hash Match without rewriting the query?

Example Data Setup

CREATE TABLE [dbo].[OCRD]
  (
     [FatherCard] NVARCHAR(50),
     [CardCode]   NVARCHAR(50)
  );

INSERT INTO [dbo].[OCRD]
SELECT TOP (2076000) NEWID(),
                     NEWID()
FROM   master..spt_values v1,
       master..spt_values v2,
       master..spt_values v3


CREATE TABLE [dbo].[OINV]
  (
     [CardCode] NVARCHAR(50)
  )


INSERT INTO [dbo].[OINV]
SELECT TOP (5175460) NEWID()
FROM   master..spt_values v1,
       master..spt_values v2,
       master..spt_values v3


CREATE NONCLUSTERED INDEX [OCRD_FATHER]
  ON [dbo].[OCRD] ( [FatherCard] ASC )
  INCLUDE (CardCode)

CREATE NONCLUSTERED INDEX [OINV_CUSTOMER]
  ON [dbo].[OINV] ( [CardCode] ASC ) 

Answer 1

is there any way to change it to filter before it does the Hash Match without rewriting the query?

In Enterprise Edition, an indexed view can be used. For example:

CREATE VIEW dbo.OINV_OCRD
WITH SCHEMABINDING
AS
SELECT
    INV.CardCode,
    CRD.FatherCard,
    COUNT_BIG(*) AS cnt
FROM dbo.OINV AS INV
JOIN dbo.OCRD AS CRD
    ON CRD.CardCode = INV.CardCode
GROUP BY
    INV.CardCode,
    CRD.FatherCard;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.OINV_OCRD (CardCode, FatherCard);
CREATE NONCLUSTERED INDEX i ON dbo.OINV_OCRD (FatherCard) INCLUDE (cnt);

Then a query like:

DECLARE 
    @P2 nvarchar(50) = N'D20B5DD1-C729-4B7A-A276-950CE7DCF128',
    @P3 nvarchar(50) = N'6A0DBEAB-FECB-40DB-86C5-AAFA612DA691';

SELECT COUNT_BIG(*)
FROM [dbo].[OINV] T0
JOIN [dbo].[OCRD] T2
    ON T2.[CardCode] = T0.[CardCode]
WHERE 
    T0.[CardCode] = (@P2)  
    OR T2.[FatherCard] = (@P3);

Produces an execution plan using index intersection on the indexed view:

Table 'OINV_OCRD'. 
    Scan count 2, 
    logical reads 8, physical reads 0, read-ahead reads 0, 
    lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

All the usual caveats around indexed view usage apply. Automatic indexed view matching is only available in Enterprise Edition (or equivalent). Using COUNT('A') does not affect the core mechanism, though a Compute Scalar is added to convert bigint to integer.

For more details around the indexed view matching feature see my answer to a related question.

If you add tables to the query, the indexed view matching is likely to continue to work (assuming the match is valid) since the optimizer is capable of matching parts of a query to one or more indexed views. This answer can only address the details actually presented in the question.

If the vendor queries follow a set pattern, you could force the indexed view plan to be used via a Plan Guide.

Answer 2

Why is this

This looks like a limitation to SQL Server's ability to imply predicates.

If you change

WHERE T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

to

WHERE T2.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

then the predicate is pushed into the scan on T2 and performance is much better. It is guaranteed by the join condition on T2.[CardCode] = T0.[CardCode] that the two are equal so this does not change the semantics.

With the example data I added to your question and an option (hash join) hint the original version had

CPU time = 12434 ms,  elapsed time = 3597 ms.

On my machine and the second version

CPU time = 405 ms,  elapsed time = 580 ms.

I'm not sure of the details of why the implied predicate fails here - the OR appears to play a part however. For the AND-ed predicate

T0.[CardCode] = (@P2)  AND T2.[FatherCard] = (@P3)

It is able to be applied to the scan on T2 without problems.

Answer 3

Although there's no reason why it wouldn't work in this question's particular case, in the general case SQL would need to perform a join on all rows from both data sets in order to ensure that the results are correct.

In this case, because we're just getting the CardCode from both tables and joining on too, we could easily union the results, but for different result sets it wouldn't work.

E.g. a more general case:

SELECT T0.CustomerName, T1.OrderID
FROM Customer T0
LEFT JOIN Order T1 ON T1.CustomerID = T0.CustomerID
WHERE T0.IsActive = 'Y' OR T1.IsCancelled = 'N'

In this example, there could be Orders that fulfill the IsCancelled predicate, but the matching Customer may not fulfill the IsActive predicate - in order to correctly union the results we need anything that could match from both tables, which is why it performs a full scan and doesn't do any filtering before the Join occurs.

Answer 4

SELECT COUNT('A')  FROM  [dbo].[OINV] T0  
INNER  JOIN [dbo].[OCRD] T2  ON  T2.[CardCode] = T0.[CardCode]   
WHERE T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

you should remove include.Covering index is not require from this query point of view.

I know you can't change query but throwing table structure along with data type and their null-ability help.

Why COUNT('A') is not clear to me ?It should be Count(*) or count(CardCode) or which table count is require ?

one of index scan OCRD_FATHER estimated rows is 2076000

another index scan is OINV_CUSTOMER] is 5175460

As we know in short when large number of rows are return or there is high cardianility estimate then optimizer decide to scan than use index.

So you are getting index scan.

Suppose if any one of join was returning less number of rows,then Hash Match Join was very desirable and obvious.

How much rows Bitmap filter return is not clear from your query plan .You can see from the tool tip on the arrow just after Bitmap filter. It must be returning far less rows than 2076000

In your current situation both are showing very very high Estimated rows,so Hash Match is not desirable.

This may be due to Outdated Statistics.

May be Bitmap filter is returning far less number of rows then Hash Match Join is obvious.

Bitmap filter : The primary role of the Bitmap is to speed up parallel plans by doing semijoin reduction early on in the query, before rows are passed through the Parallelism operator.

Since you can't rewrite query,you can't use Hint or any other way.only thing you can do is probe

XML plan reveal more thing than graphical plan in your case here.

XML plan reveal that there is CONVERT_IMPLICIT(int,[globalagg1006],0)

Due to part of predicate is under Residual Probe :Residual probe happen because of inequality operator or implicit conversion between data type.Residual probe predicate are filter much later.Residual predicate are executed after main predicate.Residual predicate may not be present in the tool tip,but it is present in xml plan or property window (hit F4).

This is also the reason that query is very slow.

T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

Here ensure that @P2 data type is similar to T0.[CardCode].And @P3 data type should be similar to T2.[FatherCard].

Similarly T2.[CardCode] = T0.[CardCode] data type should be similar.

Update 1

As per @Martin sample data,

query 1,

declare @P2 nvarchar(50)
declare @P3 nvarchar(50)

SELECT COUNT('A')  FROM  [dbo].[OINV] T0  
INNER  JOIN [dbo].[OCRD] T2  ON  T2.[CardCode] = T0.[CardCode]   
WHERE T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

It takes around 17 sec in.

If change to T2[CardCode] = (@P2) then it take less than 1 sec.Also Plan change from index scan to seek.

One of the reason for such drastic change is data type.

Now query 2, i change data type of parameter

declare @P2 varchar(50)
declare @P3 varchar(50)

SELECT COUNT('A')  FROM  [dbo].[OINV] T0  
INNER  JOIN [dbo].[OCRD] T2  ON  T2.[CardCode] = T0.[CardCode]   
WHERE T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

It take 6 second to execute.In real data it will take more time if it produce result.

My execution plan are almost identical to that of @Zac .I am getting table scan,3 parallelism,bitMap Create,Hash Join with predicate residual and Hash Probe residual.

If I change my where condition to T2.[CardCode] = (@P2)

It take 1 second and there some change in query plan.

Hash join change to Nested T2 is index seek.T0 is still table scan and produces parallelism.

Therefore my answer above "Update" about data type,residual etc are correct.

And this what happening in case of @Zac.