ResultSet rs1=stmt.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny+"' and yuebk.zhhs>90 and trim(yuebk.gt) in ('11','31','41')");while(rs1.next()) { String qq=rs1.getString("jh").trim(); float cmd=rs1.getFloat("cmd");ResultSet rsbq1=stmt1.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny1+"' and trim(jh)='"+qq+"' and trim(yuebk.gt) in ('11','31','41')");if(rsbq1.next()) { float cmd1=rsbq1.getFloat("cmd"); ResultSet rsb1=stmt1.executeQuery("select jh from csk where trim(csk.jh)='"+qq+"'");if (rsb1.next()) { is1=is1+1; iss1=is1; i1=i1+cmd; i2=i2+cmd1; }else{ ibs1=ibs1+1; ibss1=ibs1; ib1=ib1+cmd; ib2=ib2+cmd1; } } } if(is1==0) { is1=1; } if(ibs1==0) { ibs1=1; }
我也是个新手,意见仅供参考: ResultSet rs1=stmt.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny+"' and yuebk.zhhs>90 and trim(yuebk.gt) in ('11','31','41')"); String qq; // float cmd;// ResultSet rsbq1;// float cmd1;// ResultSet rsb1;// while(rs1.next()){ qq=rs1.getString("jh").trim(); cmd=rs1.getFloat("cmd"); rsbq1=stmt1.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny1+"' and trim(jh)='"+qq+"' and trim(yuebk.gt) in ('11','31','41')"); if(rsbq1.next()){ cmd1=rsbq1.getFloat("cmd"); rsb1=stmt1.executeQuery("select jh from csk where trim(csk.jh)='"+qq+"'");if (rsb1.next()){ // iss1=++is1; i1+=cmd; i2+=cmd1; }else{ ibss1=++ibs1; ib1+=cmd; ib2+=cmd1; } } } is1=is1==0?1:is1;// ibs1=ibs1==0?1:ibs1;还有就是从数据库入手,把相应的表或者数据行加约束之类的东西,使数据载保存到数据库中时,已经是消除空格的。这样,你就可以不必调用trim()。
这种情况难以避免,但也不用半个小时吧......你把这个拿到循环外面去,在外面声明: ResultSet rsbq1 = null;rsbq1=stmt1.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny1+"' and trim(jh)='"+qq+"' and trim(yuebk.gt) in ('11','31','41')");即使这样情况也不会有很大的改观,看来只能用连接池来解决了
我建议你拆表,把表的逻辑重组,你的jh字段是·第一与第二次查询的区别,可以考虑拆开,当然,我不了解你具体字段的逻辑,所以,你可以在维持逻辑一致性的情况下,拆成2-3张。 还有我有一个狠奇怪的想法不知道对不对,大家指正一下。 既然 ResultSet rs1=stmt.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny+"' and yuebk.zhhs>90 and trim(yuebk.gt) in ('11','31','41')"); 的rsl肯定满足:yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny+"' and yuebk.zhhs>90 and trim(yuebk.gt) in ('11','31','41')"); 那第二个查询: stmt1.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny1+"' and trim(jh)='"+qq+"' and trim(yuebk.gt) in ('11','31','41')"); 主要的区别就是and trim(jh)='"+qq+"'这句,逻辑就是你先找它符补符合第一个查询的条件然后再在符合的结果集中再查既符合条件1也符合条件二的结果集! 问题是,你这句and trim(jh)='"+qq+"'是在符合的结果集中获得,所以,第二句查询是做了匹配第一个条件的无用工,可以利用第一个一符合条件一的结果集(也是理论最小的),做第二个条件的查询,这就可以大大降低查询复杂度,大家觉得我说得对不对?请指正。
To z_jordon(mike jordon) :Mysql小数据量查询比较快。海量数据不要拿去和Oracle/Sqlserver比,特别是Oracle。如果他的数据量比较大的话,假设都为100000条(其实不算大)。循环两次试试??我建议他用Oracle等代替是“万一找不到其它更好的方法。可以利用Oracle的存储过程等手段解决”
{
String qq=rs1.getString("jh").trim();
float cmd=rs1.getFloat("cmd");ResultSet rsbq1=stmt1.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny1+"' and trim(jh)='"+qq+"' and trim(yuebk.gt) in ('11','31','41')");if(rsbq1.next())
{
float cmd1=rsbq1.getFloat("cmd");
ResultSet rsb1=stmt1.executeQuery("select jh from csk where trim(csk.jh)='"+qq+"'");if (rsb1.next())
{
is1=is1+1;
iss1=is1;
i1=i1+cmd;
i2=i2+cmd1;
}else{
ibs1=ibs1+1;
ibss1=ibs1;
ib1=ib1+cmd;
ib2=ib2+cmd1;
}
}
}
if(is1==0)
{
is1=1;
}
if(ibs1==0)
{
ibs1=1;
}
ResultSet rs1=stmt.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny+"' and yuebk.zhhs>90 and trim(yuebk.gt) in ('11','31','41')");
String qq; //
float cmd;//
ResultSet rsbq1;//
float cmd1;//
ResultSet rsb1;//
while(rs1.next()){
qq=rs1.getString("jh").trim();
cmd=rs1.getFloat("cmd");
rsbq1=stmt1.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny1+"' and trim(jh)='"+qq+"' and trim(yuebk.gt) in ('11','31','41')");
if(rsbq1.next()){
cmd1=rsbq1.getFloat("cmd");
rsb1=stmt1.executeQuery("select jh from csk where trim(csk.jh)='"+qq+"'");if (rsb1.next()){ //
iss1=++is1;
i1+=cmd;
i2+=cmd1;
}else{
ibss1=++ibs1;
ib1+=cmd;
ib2+=cmd1;
}
}
}
is1=is1==0?1:is1;//
ibs1=ibs1==0?1:ibs1;还有就是从数据库入手,把相应的表或者数据行加约束之类的东西,使数据载保存到数据库中时,已经是消除空格的。这样,你就可以不必调用trim()。
又一部分数据要从他以前得库中调。
那他以前得库中有些字段用得是
char类型得。
所以必须要用trim啊。
还有,你肯定要从数据库下功夫了,如果你的数据库不能改,就只好在SQL语句上下功夫了
1.用存储过程应该能解决一部分的问题
2.如果是循环的话,这种问题是不是能用PrepareStatement解决一些呢
3.用连接池试试看
ResultSet rsbq1 = null;rsbq1=stmt1.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny1+"' and trim(jh)='"+qq+"' and trim(yuebk.gt) in ('11','31','41')");即使这样情况也不会有很大的改观,看来只能用连接池来解决了
用MYSQL又怎么啦,MYSQL的查询速度甚至比ORACLE还快,动不动就叫人换数据庫,大哥你好牛逼啊。
然后ny='"ny''的记录中jh,cmd的值和ny='"ny1"'的记录中cmd的值在第三个查询中有用到?
如果是的话,程序还是可以改善的,但是第二次在for循环中的查询还是得用到。
还有我有一个狠奇怪的想法不知道对不对,大家指正一下。
既然
ResultSet rs1=stmt.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny+"' and yuebk.zhhs>90 and trim(yuebk.gt) in ('11','31','41')");
的rsl肯定满足:yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny+"' and yuebk.zhhs>90 and trim(yuebk.gt) in ('11','31','41')");
那第二个查询:
stmt1.executeQuery("select jh,cmd,ny,lcy,ym,zhhs,gt from yuebk where yuebk.lcy>=0 and yuebk.ym>=0 and trim(yuebk.km)='"+yk+"' and trim(yuebk.ny)='"+ny1+"' and trim(jh)='"+qq+"' and trim(yuebk.gt) in ('11','31','41')");
主要的区别就是and trim(jh)='"+qq+"'这句,逻辑就是你先找它符补符合第一个查询的条件然后再在符合的结果集中再查既符合条件1也符合条件二的结果集!
问题是,你这句and trim(jh)='"+qq+"'是在符合的结果集中获得,所以,第二句查询是做了匹配第一个条件的无用工,可以利用第一个一符合条件一的结果集(也是理论最小的),做第二个条件的查询,这就可以大大降低查询复杂度,大家觉得我说得对不对?请指正。