4

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

1
  • What is interesting is if change the OR to an AND in the where then can specify both hints. On OR verses AND also results in a different join (OR HASH and the AND NESTED LOOP).
    – paparazzo
    Oct 15, 2012 at 16:17

5 Answers 5

9

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'
      ) ;
4
  • Yes that is fast and uses the desired indexes. For now the easy fix is the two table hints as that is how the queries are being built up. This is a program that build up queries to many tables. In the next version we are probably going to use the format in your answer or union / intersect.
    – Blam
    Oct 11, 2012 at 14:04
  • 4
    I don't think it's a good idea to use hints. Unless you have performance problems and you are sure that the execution plan is not the best and you have no other way of getting around it (rewriting, different indexes, etc). Oct 11, 2012 at 14:07
  • But I can't answer on how the double hint can be enforced (and not limit the optimizer's options so a plan is produced). Perhaps you can flag your question for migration to the (sister) DBA.SE site. There are several users there that could answer this far better. Oct 11, 2012 at 14:09
  • This a case of can't get it to use the correct index without reformatting the query. We are going to reformat the queries without hints but for now I am hoping for a patch to the current format. Thanks again.
    – Blam
    Oct 11, 2012 at 14:24
6
+200

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:

Basic plan

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:

After SimplifyLOJN

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

After 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:

Future optimizer plan

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:

Exists 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).

1
  • >The bad news is that this cannot be done today due to limitations in the SQL Server query optimizer. I was able to get the query to compile by swapping the order of the index columns round, as per my simple repro. I'm not saying this is a great solution because it isn't. Reads were sky-high in Profiler, however it did compile without changing the text of the query.
    – wBob
    Oct 19, 2012 at 9:31
5
+100

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.

3
  • I was still curious as to why that ugly query behaved that way. How is it that the PK resolves to a legal master - it is still an OR?
    – paparazzo
    Oct 16, 2012 at 17:48
  • Your OR is unrelated to either LEFT JOIN, therefore it cannot be used until after the joins are resolved. So consider the query without the WHERE. Now, you're asking it to SEEK on the (value)-leading index for the SID. This requires it to perform a bookmark-lookup back to the data page (along PK). Doing it twice is just too much work.
    – 孔夫子
    Oct 16, 2012 at 19:37
  • 4
    Richard I've edited your excellent and helpful post and the comments to make them a bit more 'community-friendly'. I'd be glad to discuss this with you in the site chat room if you have any objection at all to the changes I made. Thanks for contributing here and please keep up the good work :) Oct 17, 2012 at 16:51
2

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'
2
  • This was originally my question this is correct. But as stated in the question it also defaults to the proper index and seek with no hints in this format.
    – paparazzo
    Oct 15, 2012 at 16:15
  • This changes the semantics of the query, because each LEFT JOIN now has 2 conditions to work with (value,SID). The original query cannot perform the filter until after the JOINS have resolved.
    – 孔夫子
    Oct 17, 2012 at 4:10
1

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'  
2
  • I reversed and did not fix for me. As stated in the question everything fine with index on PK and your IX looks like my PK. Since it is no longer my question I cannot update the question. Both tables sID int, fieldID int, value string. SV has PK sID, fieldID. MV has PK sID, fieldID, value. Both ix (reverse lookup) value, sID. If there is something specific you would like me to test please post a comment.
    – paparazzo
    Oct 16, 2012 at 0:17
  • 2
    @Blam: You can edit and it is still your question. Oct 16, 2012 at 5:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.