Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

select
    method = 
        case methoddescription 
            when 'blah' then 'Ethylene Oxide'
            when NULL then 'N/A'
            else methoddescription
        end,
    testmethod = 
        case methoddescription 
            when 'blah' then 'Biological Indicators'
            when NULL then 'N/A'
            else 'Dosimeter Reports'
        end,
    result = 
        case when l.res is null or l.res <> 1 then 'Failed'
        else 'Passed'
        end,
    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:

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.

share|improve this question
add comment

1 Answer

up vote 12 down vote accepted

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

i.e.

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'  )
   CROSS JOIN 
  ( /*...*/  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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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