关联查询中使用TOP引起的性能问题分析

类似这样的查询

 

 

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”加与不加对结果并无影响。