我正在尝试从非常大的Audits表(数百万行)中检索数据。因此,我需要使查询尽可能高效地运行。首先,我在玩一个子查询以返回ObjectTypeId并使用它来限制对Audit表的查询
该查询需要4分钟才能运行:
select distinct Audits.ObjectTypeID, COUNT(*) as Count
from Audits as Audits
where Audits.ObjectTypeID =
(select distinct ObjectType.ObjectTypeID from ObjectType where ObjectName = 'Data')
group by Audits.ObjectTypeID
如果我默认使用ObjectTypeID,查询将在42秒内运行
select distinct(Audits.ObjectTypeID), COUNT(*) as Count
from Audits
where Audits.ObjectTypeID = 1
group by Audits.ObjectTypeID
但是子查询在单独运行时只需要一秒钟即可运行。那么,为什么第一个查询要花这么长时间?
我可以看到三件事可能会有所帮助:
ObjectTypeID
入一个变量:因为应该只有一个值DISTINCT
两个查询中的on,因为它们应该是不必要的(子查询应该只有一个值,并且您在外部查询中按该值分组ObjectTypeID
因此,最终查询将是:
DECLARE @ObjectTypeID INT
SELECT @ObjectTypeID = (select ObjectType.ObjectTypeID
from ObjectType
where ObjectName = 'Data')
select Audits.ObjectTypeID, COUNT(*) as Count
from Audits as Audits
where Audits.ObjectTypeID = @ObjectTypeID
如果您将其作为单个语句而不是作为批处理或存储过程(意味着您不能使用变量)执行,则可以保留子查询:
select Audits.ObjectTypeID, COUNT(*) as Count
from Audits as Audits
where Audits.ObjectTypeID =
(select ObjectType.ObjectTypeID
from ObjectType
where ObjectName = 'Data')
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句