I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning

    method = 
        case methoddescription 
            when 'blah' then 'Ethylene Oxide'
            when NULL then 'N/A'
            else methoddescription
    testmethod = 
        case methoddescription 
            when 'blah' then 'Biological Indicators'
            when NULL then 'N/A'
            else 'Dosimeter Reports'
    result = 
        case when l.res is null or l.res <> 1 then 'Failed'
        else 'Passed'
    datecomplete = COALESCE(CONVERT(varchar(10), NULL, 101),'N/A')
from db2.dbo.view ls
    join db1.dbo.table l
        on ls.id = l.id
    where item = '19003'
        and l.id = '732820'

The view ([ls]) calls a remote server (the remote query %41 on the right of the plan).

Here is an image of the plan:


I only ask this question because of this blog post and I want to make sure this won't come back to bite me later.


1 Answer 1


Because we know that l.id = '732820' and ls.id = l.id then SQL Server derives that ls.id = '732820'


FROM   db2.dbo.VIEW ls
       JOIN db1.dbo.table l
         ON ls.id = l.id
WHERE  l.id = '732820' 

is the same as

  ( /*...*/ FROM   db2.dbo.VIEW ls WHERE id = '732820'  )
  ( /*...*/  FROM   db1.dbo.table l WHERE id = '732820'  )

This rewrite is not bad for performance .

This derivation is a good thing. It allows SQL Server to filter out rows ... earlier than would otherwise be possible.

Your Answer

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

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