SQL慢子查询

Question

我试图从一个非常大的审计表(数百万行)中检索数据。因此，我需要使查询尽可能高效地运行。首先，我使用一个子查询来返回ObjectTypeId，并使用它限制审计表上的查询

该查询需要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

但是，在隔离运行时，子查询只需运行一秒钟。那么，为什么第一个查询要花这么长时间呢？

Answer 1

我能看到三件可能有帮助的事情：

1. 将ObjectTypeID拉到变量中:因为它应该只有一个值
2. 在这两个查询上取出DISTINCT，因为它们应该是不必要的(子查询应该只有一个值，并且在外部查询中按该值进行分组)。
3. 因为您只查询一个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')

Answer 2

最受性能影响的部分可能是这一行：

where Audits.ObjectTypeID = 
(select distinct ObjectType.ObjectTypeID from ObjectType where ObjectName = 'Data')

实际上，您正在对表的每一行调用相同的查询，它将搜索整个ObjectType表并返回该子查询的整个结果。如果您的ObjectType表很大，这将是一个很大的性能问题。您可以使用EXISTS加快查询的该部分，以便在找到结果后尽早返回。下面是一个示例：

SELECT a.ObjectTypeID,  COUNT(*) as Count
FROM Audits a
WHERE EXISTS 
(
    SELECT ot.ObjectTypeID
    FROM ObjectType ot 
    WHERE ot.ObjectName = 'Data' AND ot.ObjectTypeID = a.ObjectTypeID
)
GROUP BY a.ObjectTypeID

Answer 3

你能试试这个吗？

SELECT DISTINCT Audits.ObjectTypeID,  COUNT(*) as Count
FROM Audits as Audits
INNER JOIN 
(SELECT DISTINCT ObjectTypeId, ObjectName FROM ObjectType 
 WHERE ObjectName = 'Data') as ObjectType ON Audits.ObjectTypeID = ObjectType.ObjectTypeID
GROUP BY Audits.ObjectTypeID