sqlserver 效率效率， 请指导
其中有部分的表的数据到了100多万 600多万的，请问该sql语句还有没有可以优化的地方，请高手帮忙

with t1 as(
select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table1 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table2 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table3 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table4 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table5 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table6 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table7 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'

union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table8 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table9 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table10 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table11 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table12 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'
union select id,agent_id,group_id,inputtime,status_flag,r_status,stressflag,callid,stre_reason1,isQC,stre_reason2,stre_reason3,stre_reason4,stre_reason5,stre_reason6,stre_reason7,stre_reason8,stre_reason9,cust_id,calltime from table13 with(nolock) where (isQC is null or isQC =0 ) and inputtime like '2013%'

)

select * from t1
SQL?Server SQL

[解决办法]
建议将union改为union all..
[解决办法]

引用:

Quote: 引用:

1.创建一个视图，把table1到table13union到一起。
2.然后在这视图上创建索引。
3.在这视图上查询WHERE ( isQC IS NULL
OR isQC = 0 )
AND inputtime LIKE '2013%')
的数据

不行吧， 因为如果是table1 到table13 全部union的话， 数据量肯定会超过1000万多的， 因为我现在只是查看的2013年的， 就有出现那么多的了

如果能走索引的话，1000万不算多。
如果不需要去重，建议把union改成union all
[解决办法]
要改善效率，要从设计上着手。。这分表设计又要联合查询，其本身的问题就很多
[解决办法]
100万条数据获取处理意义不大，
看看你的业务逻辑是不是要分页哦
[解决办法]

引用:

Quote: 引用:

100万条数据获取处理意义不大，
看看你的业务逻辑是不是要分页哦

是有，

那就测试你分页时候的时间，这个比较有意义
[解决办法]
那把每个表的inputtime 做索引，
同时where (isQC is null or isQC =0 ) and inputtime like '2013%'
写成where inputtime like '2013%' and (isQC is null or isQC =0 )
试试