Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK

Question

See first query below.
Can NOT combine table hint Index and forceseek with two joins and the joins are not on the PK.
How to make the first query compile?

Interesting
- if just one join or the other then can combine index and forceseek hints
- if the index is the PK then can combine 2 joins and have both hints

-- compiler fails 
-- Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.  
Select Count(Distinct([docSVsys].[sID])) 
From [docSVsys] with (nolock) 
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek ) 
       On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext] with (nolock, Index(ix_docMVtext_value_sID), forceseek) 
       On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'  or
      [docMVtext].[value] = 'doug' 

--  can do one join
Select Count(Distinct([docSVsys].[sID])) 
From [docSVsys] with (nolock) 
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek ) 
       On [docSVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'

--  can do the other join
Select Count(Distinct([docSVsys].[sID])) 
From [docSVsys] with (nolock) 
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek ) 
       On [docSVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'


-- if on the PK then can do forceseek on two join   
Select Count(Distinct([docSVsys].[sID])) 
From [docSVsys] with (nolock, INDEX(PK_docSVsys)) 
Left Join [docSVtext] with (nolock, Index(PK_docSVtext), forceseek ) 
    On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext]  with (nolock, Index(PK_docMVtext), forceseek) 
    On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'
   or [docMVtext].[value] = 'doug'

This does what I want and runs 100 times faster.
This has almost an identical query plan to the answer from ypercube.
But I cannot combine this with some other conditions I need.

Select count(distinct([docSVsys].[sID]))
From [docSVsys] with (nolock) 
LEFT HASH JOIN [docSVtext] with (nolock) 
  on [docSVtext].[sID] = [docSVsys].[sID] 
 and [docSVtext].[value] = 'doug'
LEFT HASH JOIN [docMVtext] with (nolock)
  on [docMVtext].[sID] = [docSVsys].[sID] 
 and [docMVtext].[value] = 'doug'
where [docSVtext].[sID] is not null 
   or [docMVtext].[sID] is not null 

This also uses the proper indexes and is the fastest.
For now I don't have the option to rewrite the format.
The program builds up queries to other tables.
We incorrectly wanted the same format for where as an order by and that got us where we are.

SELECT  count(distinct(c.sID))  
from                
(   SELECT sv.[sID] 
    FROM [docSVtext] AS sv
    WHERE sv.[value] = 'doug'
    UNION ALL 
    SELECT mv.[sID] 
    FROM [docMVtext] AS mv
    WHERE mv.[value] = 'doug'
) as c

docSVsys
sID Int PK Identity

docMVtext
sID Int PK FK to docSVsys
fieldID Int PK
value string PK
ix value, sID

docSVtext
sID Int PK FK to docSVsys
fieldID Int PK
value string
ix value, sID

MV is a table for multi-value text
SV is a table for single-value text
fieldID is for unique fields
This is a search of all SV and MV fields

Answer 1

I would also test this rewriting (aasuming that sID is the primary key of docSVsys):

SELECT COUNT(*)              
FROM [docSVsys] AS d
WHERE EXISTS
      ( SELECT * 
        FROM [docSVtext] AS sv
        WHERE sv.[sID] = d.[sID] 
          AND sv.[value] = 'doug'
      )
   OR EXISTS
      ( SELECT * 
        FROM [docMVtext] AS mv
        WHERE mv.[sID] = d.[sID] 
          AND mv.[value] = 'doug'
      ) ;

Answer 2

How to make the first query compile?

The bad news is that this cannot be done today due to limitations in the SQL Server query optimizer.

SELECT
    COUNT_BIG(DISTINCT(dsv.[sid]))
FROM dbo.docSVsys AS dsv
LEFT JOIN dbo.docSVtext AS dst ON
       dst.[sid] = dsv.[sid]
LEFT JOIN dbo.docMVtext AS dmt ON
       dmt.[sid] = dsv.[sid]
WHERE 
    dst.value = 'doug'
    OR dmt.value = 'doug';

The logical query specification above is semantically the same as the EXISTS formulation proposed in ypercube's answer and the LEFT JOIN modification made in wBob's answer. The logical formulation says nothing about how SQL Server ought to physically execute the query. The results are correct (as Richard agrees) due to the DISTINCT, but that does not mean the query is 'wrong'. Given a query optimizer with every possible transformation and infinite time and resources, the text above could produce exactly the same optimal execution plan as the texts suggested by ypercube and wBob would.

By way of explanation, let's look at the way SQL Server finds an efficient physical plan for your first 'can do' example:

SELECT
    COUNT_BIG(DISTINCT(dsv.[sid])) 
FROM dbo.docSVsys AS dsv
LEFT JOIN dbo.docSVtext AS dst ON
       dst.[sid] = dsv.[sid]
WHERE
    dst.value = 'doug';

If the query optimizer performed only the most obvious implementation, the query plan would be:

As it is, the optimizer performs two important rewrites. First it sees that the WHERE clause predicate rejects any NULLs generated by the LEFT JOIN. A simplification rule called SimplifyLOJN turns the Filter + Left Outer Join into an Inner Join:

Second, the optimizer rewrites the (group by sid) Stream Aggregate + Inner Join as a Left Semi Join using a rule called GbAggJNtoLSJN:

The same sequence of events applies to your second example (which meant to reference the docMVtext table I am sure). As before, the LEFT JOIN is simplified to an INNER JOIN because of the NULL-rejecting WHERE predicate, then the resulting DISTINCT aggregate and INNER JOIN combination is simplified to a Left Semi Join.

If the optimizer contained logic to reason about the OR predicate in your original example more fully (considering also the DISTINCT), it could produce a query plan like:

A T-SQL formulation for that plan is close to the UNION rewrite you might have in mind:

SELECT COUNT_BIG(*) FROM
(
    SELECT dsv.[sid]
    FROM dbo.docSVsys AS dsv
    LEFT JOIN dbo.docSVtext AS dst ON
           dst.[sid] = dsv.[sid]
    WHERE
        dst.value = 'doug'
    UNION
    SELECT dsv.[sid]
    FROM dbo.docSVsys AS dsv
    LEFT JOIN dbo.docMVtext AS dmt ON
           dmt.[sid] = dsv.[sid]
    WHERE
        dmt.value = 'doug'
) AS SubQuery;

If the optimizer truly contained all possible rewrites, even that formulation could be transformed to a more efficient form:

This is just the query plan for ypercube's suggested rewrite (though the exact shape of the 'ideal' plan will depend on data volumes and distribution, but you see my general point, I hope).

Answer 3

Your query has significant problems. The only reason the answer is still correct is the use of DISTINCT. Otherwise you'd see a gigantic result set being generated if the tables were large due to the cartesian product between the 3 tables.

DDL

create table docSVsys(
    sid int identity primary key clustered);
create table docSVtext(
    id int identity primary key clustered,
    sid int not null references docSVsys(sid),
    value varchar(10));
create index IX_docSVtext_value_sID on docSVtext(value,sid);
create table docMVtext(
    id int identity primary key clustered,
    sid int not null references docSVsys(sid),
    value varchar(10));
create index ix_docMVtext_value_sID on docMVtext(value,sid);

Some sample data

insert docSVsys default values; -- 1
insert docSVsys default values; -- 2
insert docSVtext values (1, 'doug');
insert docSVtext values (1, 'jim');
insert docSVtext values (2, 'jane');
insert docSVtext values (2, 'piglet');
insert docSVtext values (2, 'troy');
insert docMVtext values (1, 'pooh');
insert docMVtext values (1, 'eenie');
insert docMVtext values (1, 'meenie');
insert docMVtext values (1, 'minie');
insert docMVtext values (1, 'moe');
insert docMVtext values (1, 'earl');
insert docMVtext values (1, 'tigger');
insert docMVtext values (2, 'doug');

Your query - commented out hints, added columns and removed COUNT(DISTINCT)

Select [docSVsys].[sID], docSVtext.value, docMVtext.value
From [docSVsys] with (nolock) 
Left Join [docSVtext] --with (nolock, Index(IX_docSVtext_value_sID), forceseek ) 
       On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext] --with (nolock, Index(ix_docMVtext_value_sID), forceseek) 
       On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'  or
      [docMVtext].[value] = 'doug';

The result set

sID value   value
1   doug    pooh
1   doug    eenie
1   doug    meenie
1   doug    minie
1   doug    moe
1   doug    earl
1   doug    tigger
2   jane    doug
2   piglet  doug
2   troy    doug

Conclusion

Your OR is basically saying that if the left join on docSVtext on SID+'doug' worked, give me all the records from docMVtext based on SID alone. Although the DISTINCT will render this a no-op, SQL Server cannot optimize this path out because on another SID value, it could be docMVtext that successfully matches on 'doug', so SQL Server needs to descend down both join paths.

When SQL Server says your hints don't make sense, in this case it's because they don't. It cannot use the 'doug' value against either of the LEFT JOIN-ed tables as a lead into the index, so it has to basically dig up each and every index key on the first column (value) to get to the 2nd column (sid) to perform the seek. However, because of the or condition, there isn't a legal master between the 2 LEFT JOIN-ed tables, so neither can be used to seek against the other.

Answer 4

Interesting, I got the hints to work by adding the WHERE clause criteria additionally at the JOIN level.

I think if you're having to alter the query in this way, then you may as well rewrite the query ( either using UNION or EXISTS as per examples given above ).

Select Count(Distinct([docSVsys].[sID]))  
From [docSVsys] 
Left Join [docSVtext] WITH ( INDEX( IX_docSVtext_value_sID ), NOLOCK, FORCESEEK )
       On [docSVtext].[sID] = [docSVsys].[sID]
        AND [docSVtext].[value] = 'doug'
Left Join [docMVtext]  WITH ( INDEX( ix_docMVtext_value_sID ), NOLOCK, FORCESEEK )
       On [docMVtext].[sID] = [docSVsys].[sID]
        AND  [docMVtext].[value] = 'doug'
where [docSVtext].[value] = 'doug' OR
      [docMVtext].[value] = 'doug'

Answer 5

This is the repro script I used to test the reversing of the indexes before posting. When you say "did not work" do you mean you still got the error or the query wasn't very quick, or something else?

USE tempdb
GO
SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..[docSVsys]') IS NOT NULL DROP TABLE [docSVsys]
GO
CREATE TABLE [docSVsys] ( [sID] INT IDENTITY CONSTRAINT PK_docSVsys PRIMARY KEY, value VARCHAR(20) )
GO
IF OBJECT_ID('tempdb..docSVtext') IS NOT NULL DROP TABLE [docSVtext]
GO
CREATE TABLE [docSVtext] ( [sID] INT IDENTITY CONSTRAINT PK_docSVtext PRIMARY KEY, value VARCHAR(20) )
GO
IF OBJECT_ID('tempdb..docMVtext') IS NOT NULL DROP TABLE [docMVtext]
GO
CREATE TABLE [docMVtext] ( [sID] INT IDENTITY CONSTRAINT PK_docMVtext PRIMARY KEY, value VARCHAR(20) )
GO

-- Original indexes
--CREATE INDEX IX_docSVtext_value_sID ON [docSVtext] ( value, [sID] )
--GO
--CREATE INDEX ix_docMVtext_value_sID ON [docMVtext] ( value, [sID] )
--GO

-- v2 of indexes
CREATE INDEX IX_docSVtext_value_sID ON [docSVtext] ( [sID], value )
GO
CREATE INDEX ix_docMVtext_value_sID ON [docMVtext] ( [sID], value )
GO

-- Create some dummy data
INSERT INTO [docSVsys] DEFAULT VALUES
INSERT INTO [docSVtext] DEFAULT VALUES
INSERT INTO [docMVtext] DEFAULT VALUES
GO 100000

-- Update one random value to doug
UPDATE [docSVtext] SET value = 'doug'  OUTPUT inserted.sID WHERE sID = CAST( RAND() * 100000 AS INT )
UPDATE [docMVtext] SET value = 'doug'  OUTPUT inserted.sID WHERE sID = CAST( RAND() * 100000 AS INT )
GO


-- compiler fails  
-- Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.   
Select Count(Distinct([docSVsys].[sID]))  
From [docSVsys] with (nolock)  
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek )  
       On [docSVtext].[sID] = [docSVsys].[sID] 
Left Join [docMVtext] with (nolock, Index(ix_docMVtext_value_sID), forceseek)  
       On [docMVtext].[sID] = [docSVsys].[sID] 
where [docSVtext].[value] = 'doug'  or 
      [docMVtext].[value] = 'doug'