解决方法是情愿多几次访问数据库,增加 Where 条件 缩小检索范围,不要用连接
原代码:
List l = new ArrayList();
StringBuffer sql = new StringBuffer();
sql.append("select log.LOGID,log.LASID,customerinfo.REALNAME,customer.IDTYPE,
char(date(log.OPERATEDATE))||' '||char(time(log.OPERATEDATE)),log.OPERATETYPE,");
sql.append("log.OPERATECODE,log.PRINTSTATE,log.FIRSTPRINTDATE,log.LASTPRINTDATE,log.LASTPRINTCODE,");
sql.append("log.EMAILSTATE,log.FIRSTEMAILDATE,log.LASTEMAILDATE,log.LASTEMAILCODE,log.PRINTID,");
sql.append("log.EMAILID,log.FLAG,customer.IDNO,customerinfo.SEX,customer.email,customer.mobile
from 日志表 as log, 客户表 as customer, 客户详情表 as customerinfo
where log.LASID = char(customer.CUSTOMERID)
and char(customer.CUSTOMERID)= char(customerinfo.CUSTOMERID)");
中 Where 条件 用到了 char() 函数,原来 DB2 中 where 条件中 引用函数会引起全表扫描,逻辑没有什么错,就是线程卡在这里。
修改: 分两部分 先将 顾客ID搜出来,再去搜日志
1.先将 顾客ID搜出来
select customer.CUSTOMERID from 客户表 as customer, 客户详情表 as customerinfo
where customer.CUSTOMERID= customerinfo.CUSTOMERID for read only with ur
2.再去搜日志
StringBuffer sql = new StringBuffer();
sql.append("select log.LOGID,log.LASID,customerinfo.REALNAME,customer.IDTYPE,
char(date(log.OPERATEDATE))||' '||char(time(log.OPERATEDATE)),log.OPERATETYPE,");
sql.append("log.OPERATECODE,log.PRINTSTATE,log.FIRSTPRINTDATE,log.LASTPRINTDATE,log.LASTPRINTCODE,");
sql.append("log.EMAILSTATE,log.FIRSTEMAILDATE,log.LASTEMAILDATE,log.LASTEMAILCODE,log.PRINTID,");
sql.append("log.EMAILID,log.FLAG,customer.IDNO,customerinfo.SEX,customer.email,customer.mobile
from 日志表 as log, 客户表 as customer,客户详情表 as customerinfo
where log.LASID in (");
for(Object CustomerId : CustomerIds){
sql.append("'"+CustomerId.toString()+"',");
}
sql.deleteCharAt(sql.lastIndexOf(","));
sql.append(") AND for read only with ur");
记得加上 " for
read only with ur
"