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