SQL Server 不相关子查询非常慢

发布于 2024-10-08 17:47:10 字数 583 浏览 4 评论 0原文

我有一个简单的、不相关的子查询，它在 SQL Server 上的性能非常差。我在阅读执行计划方面不太有经验，但看起来内部查询对于外部查询中的每一行都执行一次，即使每次结果都是相同的。我该怎么做才能告诉 SQL Server 仅执行一次内部查询？

查询如下所示：

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 '~')
    )
)

原文

I have a simple, uncorrelated subquery that performs very 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 '~')
    )
)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

够运 2024-10-15 17:47:10

分析您的 SQL 语句并将其放入数据库引擎优化顾问 (2005+) 中，然后查看它建议的索引。

我认为您在确定运行查询的最佳方式方面没有给予 SQL Server 足够的信任。确保连接和 where 子句中的字段有索引。

这可能是您的查询的替代方案，但可能会运行相同的：

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'

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'

回复收藏 0 原文

败给现实 2024-10-15 17:47:10

您应该能够将其更改为直接连接，这有帮助吗：

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'

这看起来更明智..（但查询几乎相同）

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'

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'

回复收藏 0 原文

~没有更多了~