这种表设计如何查询更快

Question

/*
表<projectlink201210>结构： [表是动态按月创建]
  ID 主键
  ProjectId  --项目编号
  UserName   
  UserSex
  UserCity
  UserAge
  CompleteState
  StartTime
  EndTime当前表索引：
  ProjectId_Index      (非聚集索引)
  CompleteState_Index  (非聚集索引)
  StartTime_Index      (非聚集索引)
  P_U_C                (ProjectId,UserName,CompleteState 组合索引)需求：统计2012-10~2013-4月份各个城市男女人数，单表数据在100w~150w现在做法：根据项目编号分组，程序中循环项目编号（之前直接查询很容易导致超时，根据ProjectId查询会很快）
*/sql: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 
    projectlink201210 
where 
    projectid=9772
    and completestate='C' 
    and username<>'' 
    and starttime>='2013-03-5'
    and userage>=20 
    and usercity in ('北京市','上海市','天津市','重庆市','南京','徐州','济南','青岛','合肥') 
group by 
    usercity
问题：如果查询多个月份非常慢，程序要跑3分钟，求好的解决办法或思路
执行计划如下：索引SQL

Answer 1

UserSex，usercity 添加到P_U_C (ProjectId,UserName,CompleteState 组合索引)中

Answer 2

P_U_C索引字段顺序修改为: ProjectId,CompleteState,UserName,starttime,userage,usercity检查一下索引碎片情况,碎片程度太高需重整索引.

Answer 3

重复发帖，并且也早就回答过你了，为什么不去试试呢？
http://bbs.csdn.net/topics/390437106?page=1#post-394299119“想最快就用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”

Answer 4

我先不谈优化，直接指明楼主SQL 语句的问题   你要统计男女的数量  那么你用count(case when usersex='男' then 1 end) as boyAmount,
count(case when usersex='女' then 1 end) as girlAmount,
能实现么？我可以明确地告诉你   你这样统计的不是男女的数量   而是总行数    boyAmount girlAmount amount 三者的结果   在每一行比较   数据都是一样的把count改成sum至于索引的创建，昨天我已经给你写了   那是在我本机找的一个类似结构的表  做了测试的你可以参考
create nonclustered index usercity_completestate_starttime_userage_username_idx 
on  projectlink201205(usercity asc,completestate asc,starttime asc,userage asc,username asc)include(usersex)

Answer 5

P_U_C                (ProjectId,UserName,CompleteState 组合索引)
把这个改成 P_C_U 试试。

Answer 6

索引顺序根据你where中的字段顺序进行调整。昨天的清理索引和碎片的方法已发给你了。