关于临时表处理大数据性能优化问题

1.由于SQL里面用到group by 较多，页面查询性能方面受到很大影响， 故用到临时表（同时建立索引），不足之处，希望大家给与好的意见和建议！

ALTER proc [dbo].[UserPersonCounts]
(
@platfrom int,
@beginTime varchar(50),
@endTime varchar(50),
@pageIndex int,
@pageSize int
)
as
begin
----判断临时表是否存在
if OBJECT_ID(N'tempdb.dbo.#temp') is not null
begin
drop table #temp
print 1
end
else if OBJECT_ID(N'tempdb.dbo.#temp1')is not null
begin
drop table #temp1
print 1
end
else if OBJECT_ID(N'tempdb.dbo.#temp2')is not null
begin
drop table #temp2
print 1
end
else if OBJECT_ID(N'tempdb.dbo.#temp3')is not null
begin
drop table #temp3
print 1
end
else
begin
print 2
end

if @platfrom <> -1
begin
select CONVERT(varchar(10),addtime,121) date,COUNT(distinct useridx) '总用户数'
into #temp
from Lm_Downuser
where addtime>=@beginTime and addtime<=@endTime
group by CONVERT(varchar(10),addtime,121)
select CONVERT(varchar(10),date,121) date, COUNT(1) '差体验用户',platfrom
into #temp1
from
(
select useridx,CONVERT(varchar(10),addtime,121) date,platfrom from Lm_Downuser
where (realdownlostrate>=0.05 or delay>=500)
and (platfrom<>-1 and platfrom=@platfrom)
and addtime>=@beginTime and addtime<=@endTime
group by useridx,CONVERT(varchar(10),addtime,121),platfrom
having COUNT(useridx)>=30
) b
group by date,platfrom

select * from (select ROW_NUMBER() over(order by date) as num ,* from (
select t1.date,总用户数,差体验用户,差体验用户*1.0 /总用户数 '占比','详情' ss,t2.platfrom
from #temp t1, #temp1 t2
where t1.date=t2.date) a ) b
where num between str((@pageIndex-1)*@PageSize + 1 ) and str(@pageIndex * @pageSize )

end
else
begin
select CONVERT(varchar(10),addtime,121) date,COUNT(distinct useridx) '总用户数'
into #temp2
from Lm_Downuser
where addtime>=@beginTime and addtime<=@endTime
group by CONVERT(varchar(10),addtime,121)
select CONVERT(varchar(10),date,121) date, COUNT(1) '差体验用户'
into #temp3
from
(
select useridx,CONVERT(varchar(10),addtime,121) date from Lm_Downuser
where (realdownlostrate>=0.05 or delay>=500)
and platfrom<>-1
and addtime>=@beginTime and addtime<=@endTime
group by useridx,CONVERT(varchar(10),addtime,121),platfrom
having COUNT(useridx)>=30
) b
group by date

select * from (select ROW_NUMBER() over(order by date) as num ,* from (
select t1.date,总用户数,差体验用户,差体验用户*1.0 /总用户数 '占比','详情' ss,-1 platId
from #temp2 t1, #temp3 t2
where t1.date=t2.date ) a ) b
where num between str((@pageIndex-1)*@PageSize + 1 ) and str(@pageIndex * @pageSize )
end
end