我有两个表Sites和ScannedItems。站点大约有15000行,ScannedItems大约有6000万行。下面的查询大约需要6分钟,其中包含CountUniqueRoleAssignments,Modified和siteid上的索引。可以通过任何方式对此进行优化吗?某种联接比子查询或任何其他提示快吗?
select
*,
(select COUNT(*) from ScannedItems where ScannedItems.siteid=sites.siteid and ScannedItems.CountUniqueRoleAssignments>0) as CountUniquePermissions,
(select COUNT(*) from ScannedItems where ScannedItems.siteid=sites.siteid and ScannedItems.Modified<DATEADD (day, -30 ,GETDATE())) as CountNotModified30Days
from sites
我可能会使用联接编写此查询:
SELECT
s.siteid,
COALESCE(si.CountUniquePermissions, 0) AS CountUniquePermissions,
COALESCE(si.CountNotModified30Days, 0) AS CountNotModified30Days
FROM sites s
LEFT JOIN
(
SELECT siteid,
COUNT(CASE WHEN CountUniqueRoleAssignments > 0 THEN 1 END)
AS CountUniquePermissions,
COUNT(CASE WHEN Modified < DATEADD (day, -30, GETDATE()) THEN 1 END)
AS CountNotModified30Days
FROM ScannedItems
GROUP BY siteid
) si
ON si.siteid = s.siteid
ORDER BY
s.siteid;
上面的查询没有noWHERE
或HAVING
子句,因此我看不到任何使用索引进一步调整它的明显方法。但这至少比您当前的查询具有潜在的优势,即它不涉及N^2
select子句中具有相关子查询的行为。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句