类似这样的查询
select * from (
SELECT TOP 500 IsNULL(j.project_id,-1) AS project_id, p.page_id, p.displayname,
ROW_NUMBER() OVER(ORDER BY p.displayname) AS RowNum,
SUM(IsNUll(s.avg_loadtime,0)*1.0*IsNULL(s.pageviews,0))/CASE WHEN SUM(IsNULL(s.pageviews,0))=0
OR SUM(IsNULL(s.pageviews,0)) IS NULL THEN 1 ELSE SUM(IsNULL(s.pageviews,0)) END AS avg_LoadTime,
SUM(IsNULL(s.pageviews,0)) AS PageViews, p.flag,p.pagetype
FROM rum_page p
LEFT JOIN ( select
top 500
RUM_page_stats.page_id,
IsNULL(CASE WHEN SUM(pageviews)=0 THEN 0 ELSE SUM(avg_loadtime*1.0*pageviews)/SUM(pageviews) END,0) AS avg_loadtime,
IsNULL(SUM(pageviews),0) AS pageviews
from RUM_page_stats(nolock)
where createtime >='2009-09-14 10:51:19' and RUM_page_stats.account_id=10571
group by RUM_page_stats.page_id
order by sum(pageviews) desc
) s ON p.page_id=s.page_id
LEFT JOIN (
select a.project_id,a.account_id,a.projectname,a.status,b.page_id
from rum_project(nolock) a INNER JOIN rum_project_page(nolock) b on a.project_id=b.project_id
where a.account_id=10571 and a.status='active' ) j ON p.page_id=j.page_id
WHERE p.account_id=10571 AND p.status='active' and (p.isHide=0 or p.isHide is null)
GROUP BY p.page_id,p.displayname,j.project_id,p.flag,p.pagetype
ORDER BY pageviews DESC, j.project_id DESC, p.page_id ) final
order by project_id desc, page_id
其中标为红色的部分,也就是嵌套内部的“top 500”会导致关联的每一条记录都与嵌套内的记录关联一次,从而大幅影响性能。
而从语法上看,此“top 500”加与不加对结果并无影响。