想最快就用INDEXED VIEW :CREATE VIEW V1 WITH SCHEMABINDING AS select usercity,count(case when usersex='男' then 1 end) as boyAmount,count(case when usersex='女' then 1 end) as girlAmount, count(1) as amount from projectlink201205 where completestate='C' and username<>'' and starttime>='2012-05-5'and userage>=20 and usercity in ('北京市','上海市','天津市','重庆市','南京','徐州','济南','青岛','合肥') group by usercity GO CREATE UNIQUE CLUSTERED INDEX VUC ON V1 (usercity) GO SELECT * FROM V1 GO当然INDEXED INDEX也是需要维护开销的,自己衡量吧。
个人觉得先从语句上改写一下: select usercity, sum(case when usersex='男' then 1 end) as boyAmount, sum(case when usersex='女' then 1 end) as girlAmount, count(1) as amount from projectlink201205 where completestate='C' and username<>'' and starttime>='2012-05-5' and userage>=20 and usercity in ('北京市','上海市','天津市','重庆市','南京','徐州','济南','青岛','合肥') group by usercity
create nonclustered index usercity_completestate_starttime_userage_username_idx on projectlink201205(usercity asc,completestate asc,starttime asc,userage asc,username asc)include(usersex)
option(maxdop 1)
先看看不并行的话能不能出结果。并行排序偶尔会出问题。
select usercity,count(case when usersex='男' then 1 end) as boyAmount,count(case when usersex='女' then 1 end) as girlAmount, count(1) as amount from projectlink201205 where completestate='C' and username<>'' and starttime>='2012-05-5'and userage>=20 and usercity in ('北京市','上海市','天津市','重庆市','南京','徐州','济南','青岛','合肥') group by usercity
GO
CREATE UNIQUE CLUSTERED INDEX VUC ON V1 (usercity)
GO
SELECT * FROM V1
GO当然INDEXED INDEX也是需要维护开销的,自己衡量吧。
select
usercity,
sum(case when usersex='男' then 1 end) as boyAmount,
sum(case when usersex='女' then 1 end) as girlAmount,
count(1) as amount
from
projectlink201205
where
completestate='C'
and username<>''
and starttime>='2012-05-5'
and userage>=20
and usercity in ('北京市','上海市','天津市','重庆市','南京','徐州','济南','青岛','合肥')
group by
usercity
create nonclustered index usercity_completestate_starttime_userage_username_idx
on projectlink201205(usercity asc,completestate asc,starttime asc,userage asc,username asc)include(usersex)
查看一下索引碎片,看看是不是需要重建或者重新组织索引。
查询一下是否存在未创建的索引。http://www.cnblogs.com/worfdream/articles/2840582.html