sql语句,如果直接用查询分析器执行的话,是用1秒钟,
但是用PreparedStatement执行,用了8秒钟;请问,如何优化?sql语句;
select b.id, b.bill_title, b.accept_type, b.service_type, b.threeType, b.branchCode,b.client_code, b.flow_state, b.bill_Content,bf.call_sys_code,u.code from bill b inner join bill_flow bf on bf.bill_id = b.id inner join sys_user u on b.createId = u.id where 1=2 or ( bf.call_sys_code = '$06KP9SJA4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$64VIJUNA4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$QNMUBDOA4BN3JV7L0078SMTNQ' and u.code = '12' ) or ( bf.call_sys_code = '$QANR6OPA4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$Q8RU9FQA4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$IUURUSRA4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$QHESM1VA4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$A7637PVA4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$A7637PVA4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$ADN6O81B4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$S0C34O2B4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$0R47B33B4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$8R7GP65B4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$2FG13J5B4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$IBR60TEB4BN3JV7L0078SMTNQ' and u.code = '12' ) or ( bf.call_sys_code = '$CT1E55GB4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$CQTF13HB4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$6AK125LB4BN3JV7L0078SMTNQ' and u.code = '12' ) or ( bf.call_sys_code = '$K6U18MMB4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$C5GHMQQB4BN3JV7L0078SMTNQ' and u.code = '19' )
但是用PreparedStatement执行,用了8秒钟;请问,如何优化?sql语句;
select b.id, b.bill_title, b.accept_type, b.service_type, b.threeType, b.branchCode,b.client_code, b.flow_state, b.bill_Content,bf.call_sys_code,u.code from bill b inner join bill_flow bf on bf.bill_id = b.id inner join sys_user u on b.createId = u.id where 1=2 or ( bf.call_sys_code = '$06KP9SJA4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$64VIJUNA4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$QNMUBDOA4BN3JV7L0078SMTNQ' and u.code = '12' ) or ( bf.call_sys_code = '$QANR6OPA4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$Q8RU9FQA4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$IUURUSRA4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$QHESM1VA4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$A7637PVA4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$A7637PVA4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$ADN6O81B4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$S0C34O2B4BN3JV7L0078SMTNQ' and u.code = '19' ) or ( bf.call_sys_code = '$0R47B33B4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$8R7GP65B4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$2FG13J5B4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$IBR60TEB4BN3JV7L0078SMTNQ' and u.code = '12' ) or ( bf.call_sys_code = '$CT1E55GB4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$CQTF13HB4BN3JV7L0078SMTNQ' and u.code = '16' ) or ( bf.call_sys_code = '$6AK125LB4BN3JV7L0078SMTNQ' and u.code = '12' ) or ( bf.call_sys_code = '$K6U18MMB4BN3JV7L0078SMTNQ' and u.code = '1' ) or ( bf.call_sys_code = '$C5GHMQQB4BN3JV7L0078SMTNQ' and u.code = '19' )
select b.id, b.bill_title, b.accept_type, b.service_type, b.threeType,
b.branchCode,b.client_code, b.flow_state, b.bill_Content,bf.call_sys_code,u.code
from bill b
inner join bill_flow bf
on bf.bill_id = b.id
inner join sys_user u
on b.createId = u.id
where 1=2 --这个1=2没有意思
or ( bf.call_sys_code = '$06KP9SJA4BN3JV7L0078SMTNQ' and u.code = '16' )
or ( bf.call_sys_code = '$64VIJUNA4BN3JV7L0078SMTNQ' and u.code = '19' )
or ( bf.call_sys_code = '$QNMUBDOA4BN3JV7L0078SMTNQ' and u.code = '12' )
or ( bf.call_sys_code = '$QANR6OPA4BN3JV7L0078SMTNQ' and u.code = '19' )
or ( bf.call_sys_code = '$Q8RU9FQA4BN3JV7L0078SMTNQ' and u.code = '1' )
or ( bf.call_sys_code = '$IUURUSRA4BN3JV7L0078SMTNQ' and u.code = '19' )
or ( bf.call_sys_code = '$QHESM1VA4BN3JV7L0078SMTNQ' and u.code = '16' )
or ( bf.call_sys_code = '$A7637PVA4BN3JV7L0078SMTNQ' and u.code = '19' )
or ( bf.call_sys_code = '$A7637PVA4BN3JV7L0078SMTNQ' and u.code = '16' )
or ( bf.call_sys_code = '$ADN6O81B4BN3JV7L0078SMTNQ' and u.code = '1' )
or ( bf.call_sys_code = '$S0C34O2B4BN3JV7L0078SMTNQ' and u.code = '19' )
or ( bf.call_sys_code = '$0R47B33B4BN3JV7L0078SMTNQ' and u.code = '16' )
or ( bf.call_sys_code = '$8R7GP65B4BN3JV7L0078SMTNQ' and u.code = '1' )
or ( bf.call_sys_code = '$2FG13J5B4BN3JV7L0078SMTNQ' and u.code = '1' )
or ( bf.call_sys_code = '$IBR60TEB4BN3JV7L0078SMTNQ' and u.code = '12' )
or ( bf.call_sys_code = '$CT1E55GB4BN3JV7L0078SMTNQ' and u.code = '16' )
or ( bf.call_sys_code = '$CQTF13HB4BN3JV7L0078SMTNQ' and u.code = '16' )
or ( bf.call_sys_code = '$6AK125LB4BN3JV7L0078SMTNQ' and u.code = '12' )
or ( bf.call_sys_code = '$K6U18MMB4BN3JV7L0078SMTNQ' and u.code = '1' )
or ( bf.call_sys_code = '$C5GHMQQB4BN3JV7L0078SMTNQ' and u.code = '19' )
--给call_sys_code和code加上索引
select * from view_name
LZ不妨调试一下看看是在哪一步慢,如果不是在EXCUTEQUERY()那步慢就不是SQL的问题了
具体什么原因,还是不知道。
1=2 确实是拼SQL时(谢谢guguda2008)
解决方法: 自己拼接sql语句,别用 PreparedStatement的setXXX()方法设置查询参数.