作一个查询的性能优化。
先清缓存
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
原查询 前人遗留。
1 declare @total float,@total_person float,@times_person float,@date varchar(50) 2 select @date=CONVERT(char(10),GETDATE(),120) 3 4 select @total=sum(price*person)*1.0 from db_time t inner join db_movie m on t.movieid=m.id inner join db_cinema c on t.cinemaid=c.id 5 where CAST(showdate as date)='2014-02-27' and t.sta=1 6 7 select @times_person=sum(1) from db_time t inner join db_movie m on t.movieid=m.id inner join db_cinema c on t.cinemaid=c.id 8 where CAST(showdate as date)='2014-02-27' and t.sta=1 9 10 select @total_person=sum(person) from db_time t inner join db_movie m on t.movieid=m.id inner join db_cinema c on t.cinemaid=c.id 11 where CAST(showdate as date)='2014-02-27' and t.sta=1 ; 12 13 with sr as( 14 select 15 --ROW_NUMBER() over(order by SUM(price*person) desc) as 'index' 16 --, 17 movieid id 18 ,m.name name 19 ,m.enname 20 ,SUM(price*person)*1.0 as BoxOffice 21 ,SUM(price*person)*1.0/@total BoxPercent 22 ,sum(1) ShowCount 23 ,sum(1)*1.0/@times_person ShowPercent 24 ,sum(person) AudienceCount 25 ,sum(person)*1.0/@total_person AudiencePercent 26 ,cast(round(sum(price*person)*1.0/sum(person),0) as int) Price 27 --,sum(person)*1.0/sum(1) as test 28 --,sum(person)*1.0 as t1 29 --,sum(1) as t2 30 ,cast(round(sum(person)*1.0/sum(1),0) as int) as Renci 31 ,round(CAST(sum(person) as float)/sum(seat),4) as Shangzl 32 from db_time t inner join db_movie m on t.movieid=m.id inner join db_cinema c on t.cinemaid=c.id 33 where CAST(showdate as date)='2014-02-27' and t.sta=1 34 group by movieid,m.name,m.enname 35 ) 36 37 select * from sr order by BoxOffice desc
执行时间18S
最开始想着是把上面3条求总量的查询改成在一条里完成，where条件重复多次，想想开脆改成开窗函数好了
经开窗函数改造后的查询。
1 with sr as( 2 select 3 distinct 4 --ROW_NUMBER() over(order by SUM(price*person) desc) as 'index' 5 --, 6 movieid id 7 ,m.name name 8 ,m.enname 9 ,(SUM(price*person) OVER(PARTITION BY movieid))*1.0 as BoxOffice 10 ,(SUM(price*person) OVER(PARTITION BY movieid))*1.0/(SUM(price*person) OVER()) as BoxPercent 11 ,sum(1) OVER(PARTITION BY movieid) as ShowCount 12 ,(sum(1) OVER(PARTITION BY movieid))*1.0/(sum(1) OVER()) ShowPercent 13 ,sum(person) OVER(PARTITION BY movieid) AudienceCount 14 ,((sum(person) OVER(PARTITION BY movieid))*1.0)/((sum(person) OVER())) AudiencePercent 15 ,cast(round((sum(price*person) OVER(PARTITION BY movieid))*1.0/(sum(person) OVER(PARTITION BY movieid)),0) as int) Price 16 ,cast(round(((sum(person) OVER(PARTITION BY movieid))*1.0/(count(0) OVER(PARTITION BY movieid))),0) as int) as Renci 17 ,round(CAST((sum(person) OVER(PARTITION BY movieid)) as float)/(sum(seat) OVER(PARTITION BY movieid)),4) as Shangzl 18 from db_time t inner join db_movie m on t.movieid=m.id inner join db_cinema c on t.cinemaid=c.id 19 where CAST(showdate as date)='2014-02-27' and t.sta=1 20 ) 21 select * from sr order by BoxOffice desc
代码确实精简不少，同样的where子句消除了。（两个查询中的rownumber完全没用，注掉了）
执行时间17S，心里隐隐期待的是查询时间有明显减少，实际查询时间在误差范围内，基本未变化，看来开发开窗函数的目的不是为性能提升，而是为了方便开发人员编写查询代码，减少查询难度，提高查询可读性。
查询计划有很大变化，经开窗函数改造后的查询计划，多了很多“表假脱机”的执行步骤
但两种查询最耗时的依然是97%聚集索引扫描（表上只有id的自增聚集索引）