其中有部分的表的数据到了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 ServerSQL
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 ServerSQL
2.然后在这视图上创建索引。
3.在这视图上查询WHERE ( isQC IS NULL
OR isQC = 0 )
AND inputtime LIKE '2013%')
的数据
然后对字段inputtime创建索引 (isQC is null or isQC =0 )
改为isnull(isQC,0)=0
对你说的更改, 我做了尝试,结果如下:
条件 用时 数据量
(isQC is null or isQC =0 ) 00:01:07s 2816514
isnull(isQC,0)<>1 00:01:33s 2816206
如果不需要去重,建议把union改成union all
如果不需要去重,建议把union改成union all改成union all 了,缩短了几秒了
同时where (isQC is null or isQC =0 ) and inputtime like '2013%'
写成where inputtime like '2013%' and (isQC is null or isQC =0 )
试试
你用执行规划看看有没有用到索引,有时候写在后面SQL SERVER很傻,没有用到索引
table1- table13如果区分业务的话,可以增加业务区分字段
相信这样查询也方便,效率也能提升,但是多个年份查询的话就比较麻烦。
以上只是拙见,学习!
如果每年的数据量很大,做成分区表
如果isQC具有高选择性,建立索引: isQC,inputtime;如果 inputtime具有高选择性,建立索引 inputtime,isQC
如果inputtime是日期类型,改成 inputtime between '2013-01-01' and '2013-12-31 23:59:59.999'
所有的变量都可以想办法动态,上面的只是概述优化方法。
select
convert(char(4),getdate(),120)+'-01-01',
convert(char(4),getdate(),120)+'-12-31 23:59:59.999',
convert(char(4),dateadd(year,1,getdate()),120)+'-01-01'
isQC is null 用不到索引,跑全表的,
这个表设计可以改下吧(把原来的NULL置为0,然后改下isQC 禁止NULl,默认值设置为0);
可能以前的此处代码(向表插入数据的代码)需要检查下,避免插入NULL