当带有not in子句的子查询没有返回任何值时，查询将花费大量时间

Question

我有一个具有select查询的存储过程，该查询具有子查询和not in子句。

当子查询返回某个值时，则选择查询在1秒内返回结果，但当子查询不返回任何值时，则查询执行时间为2分钟。

我已经搜索和建议要么使用not exists子句或使用连接。但两者似乎不适合我的情况下，因为我使用的是同一张表。我可能错了。需要你的指导

这是我的查询

select
-- @vNewParentId,
-- @pNewEntityId,
AppEntityAttribute.AppEntityAttributeName,
AppEntityAttribute.AttributeDataTypeId,
AppEntityAttribute.IsProductionReady
from 
AppEntityAttribute
where 
AppEntityId = 'DAFC0508-A899-408D-8650-543BFA6909B4'
and AppEntityAttribute.ParentAppEntityAttributeId = 1353321
and AppEntityAttribute.OrganizationId is null
and AppEntityAttribute.ProcessflowId is null
and AppEntityAttribute.NodeId is null
and AppEntityAttribute.AppEntityAttributeId 
not in (select AppEntityAttribute.ParentId 
from AppEntityAttribute
where AppEntityAttribute.AppEntityId = '79A5E185-6EF1-4BCF-802F-E2BF1962876B'
and AppEntityAttribute.OrganizationId is null
and AppEntityAttribute.ProcessflowId is null
and AppEntityAttribute.NodeId is null)
order by 
AppEntityAttribute.AppEntityAttributeId

Answer 1

尝试使用NOT EXISTS:

select   
AppEntityAttributeName,
AttributeDataTypeId,
IsProductionReady   
from AppEntityAttribute A
where AppEntityId = 'DAFC0508-A899-408D-8650-543BFA6909B4' 
and ParentAppEntityAttributeId=1353321
and OrganizationId  is null 
and ProcessflowId  is null 
and NodeId  is null
and not exists (
select 1 
from AppEntityAttribute B
where B.AppEntityId ='79A5E185-6EF1-4BCF-802F-E2BF1962876B'
and B.OrganizationId  is null
and B.ProcessflowId  is null 
and B.NodeId  is null
and A.AppEntityAttributeId=B.ParentId
)
order by AppEntityAttributeId

Answer 2

以下索引可以使@SomendraKanaujia的查询速度更快:

create index ix1 on AppEntityAttribute (
AppEntityId, ParentAppEntityAttributeId, OrganizationId,
ProcessflowId, NodeId, AppEntityAttributeId
);
create index ix2 on AppEntityAttribute (
AppEntityId, AppEntityAttributeId, 
OrganizationId, ProcessflowId, NodeId
);