Sql-server – Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK

hintssql serversql-server-2008

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

Best Answer

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'
      ) ;
Related Question