SQL Server uncorrelated subquery very slow

I have a simple, uncorrelated subquery that performs ve开发者_如何学编程ry poorly on SQL Server. I'm not very experienced at reading execution plans, but it looks like the inner query is being executed once for every row in the outer query, even though the results are the same each time. What can I do to tell SQL Server to execute the inner query only once?

The query looks like this:

select *
from Record record0_ 
where record0_.RecordTypeFK='c2a0ffa5-d23b-11db-9ea3-000e7f30d6a2' 
and (
    record0_.EntityFK in (
        select record1_.EntityFK 
        from Record record1_
        join RecordTextValue textvalues2_ on record1_.PK=textvalues2_.RecordFK 
        and textvalues2_.FieldFK = '0d323c22-0ec2-11e0-a148-0018f3dde540' 
        and (textvalues2_.Value like 'O%' escape '~')
    )
)

Analyze your SQL Statement and put it in the Database Engine Tuning Advisor (2005+) and see what indexes it suggests.

I don't think you're giving SQL Server enough credit on determining the best way to run a query. Make sure you have indexes on the fields in your joins and where clauses.

This may be an alternative to your query, but will probably run the same:

select record0_.*
from Record record0_
inner join Record record1_
on record0_.EntityFK = record1_.EntityFK
inner join RecordTextValue textvalues2_ 
on record1_.PK=textvalues2_.RecordFK 
    and textvalues2_.FieldFK = '0d323c22-0ec2-11e0-a148-0018f3dde540' 
    and (textvalues2_.Value like 'O%' escape '~')
where record0_.RecordTypeFK='c2a0ffa5-d23b-11db-9ea3-000e7f30d6a2' 

You should be able to change this to a straight forward join, does that help:

select r.*
from Record r
join (  select record1_.EntityFK 
        from Record record1_
        join RecordTextValue textvalues2_ on record1_.PK=textvalues2_.RecordFK 
        and textvalues2_.FieldFK = '0d323c22-0ec2-11e0-a148-0018f3dde540' 
        and (textvalues2_.Value like 'O%' escape '~')
     ) s on s.EntityFK = r.EntityFK
where r.RecordTypeFK='c2a0ffa5-d23b-11db-9ea3-000e7f30d6a2'

This looks a lot more sensible.. (but pretty much the same query)

select r.*
from Record r
join (  select ri.EntityFK 
        from Record ri
        join RecordTextValue t on ri.PK=t.RecordFK
        where 
            t.FieldFK = '0d323c22-0ec2-11e0-a148-0018f3dde540' 
        and t.Value like 'O%' 
     ) s on s.EntityFK = r.EntityFK
where r.RecordTypeFK='c2a0ffa5-d23b-11db-9ea3-000e7f30d6a2'