--数据量在130W左右

select projectid,username,completestate,count(1) as amount from projectlink201209 where starttime between 
'2012-10-29 00:00:00' and '2012-10-29 23:59:59'  and len(username)>0 and len(username)<14 and 
isnull(completestate,'') in ('C','S','Q') group by projectid,completestate,username

select projectid,username,completestate,count(1) as amount from projectlink201209 where starttime between 
'2012-10-29 00:00:00' and '2012-10-29 23:59:59'  and len(username)>0 and len(username)<14 and completestate in ('C','S','Q') group by projectid,completestate,username

------解决方案--------------------
starttime 字段加索引试试  username  completestate字段加了索引应该也不会走索引 



------解决方案--------------------
username 这个字段感觉应该是 not null  
那么语句能修改的地方

      AND username <>''
      AND LEN(username) < 14
      AND completestate IN ('C', 'S', 'Q')
--修改的原则就是尽量不要再where 条件上面使用函数
使用了函数，这些条件就不可SARG了

然后就是补上缺失的索引

------解决方案--------------------
SQL code
SELECT   projectid,
         username,
         completestate,
         Count(1) AS amount
FROM     projectlink201209
WHERE    starttime BETWEEN '2012-10-29 00:00:00'
                           AND '2012-10-29 23:59:59'
         AND Len(username) > 0
         AND Len(username) < 14
         AND completestate IN ('C',
                               'S',
                               'Q')
GROUP BY projectid,
         completestate,
         username

------解决方案--------------------
好东西，学习下

------解决方案--------------------
探讨

SQL code

--数据量在130W左右

select projectid,username,completestate,count(1) as amount from projectlink201209 where starttime between 
'2012-10-29 00:00:00' and '2012-10-29 23:59:59'  and len(username)>0……


------解决方案--------------------
1、starttime加聚集索引。
2、projectid、username、completestate加一个联合非聚集索引
3、更新表的统计信息