优化上SQL

优化下SQL

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 and len(username)<14 and isnull(completestate,'') in ('C','S','Q') group by projectid,completestate,username

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
[解决办法]
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 amountFROM     projectlink201209WHERE    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、更新表的统计信息