当前位置：首页 → 问答吧 → 关于查询性能优化的问题？

关于查询性能优化的问题？

时间：2011-11-08

来源：互联网

SQL code


JDBC

List<Person> list = new ArrayList<Person>();

select a,b,c,d,e,f,g,h from table_name


while(rs.next()){

Person p = new Person();
p.setA(rs.getInt(a));
.....
list.add(p);
}

请问上面的查询（查询的字段多，而且数据量大）
有没有好的办法
可以提高一下速度！！

作者: ask00000001 发布时间: 2011-11-08

select a,b,c,d,e,f,g,h from table_name

这个在PL/SQL查询的执行计划贴出来看看!

看表的索引是否有建立,组合索引等!

作者: cosio 发布时间: 2011-11-08

引用 1 楼 cosio 的回复:
select a,b,c,d,e,f,g,h from table_name

这个在PL/SQL查询的执行计划贴出来看看!

看表的索引是否有建立,组合索引等!

这是sql:
SQL code


StringBuilder sb = new StringBuilder();
        sb.append("SELECT C.*,CGA.AREA_PROVINCE AS AREA_PROVINCE,CGA.AREA_CITY AS AREA_CITY,CGA.AREA_COUNTY AS AREA_COUNTY FROM"); 
        sb.append("(");
        sb.append("SELECT B.*,CGG.FULLNAME AS GROUP_NAME,CGG.CONTACT AS CONTACT,CGG.PHONE_NUMBER AS PHONE_NUMBER,CGG.ADDRESS_INFO FROM");
        sb.append("(");
        sb.append("SELECT A.*,CGC.FULLNAME AS CINEMA_NAME FROM");
        sb.append("(");
        sb.append("SELECT MPI.LONGITUDE,MPI.LATITUDE,MPI.MOVIE_ID,MPI.PLAY_TIME_LENGTH,MPI.PLAY_RESULT,MPI.MOVIE_PLAYHIS_ID,MPI.EXISTPICFILE,MPI.MOVIE_NAME,MPI.DISTRICT,MPI.START_TIME,MPI.END_TIME,MPI.REMAINING_COUNT,MPI.WEIGHT,MPI.ISREACHPERCENT90,TML.TML_ID,TML.CINEMA_ID,TML.GROUP_ID,TML.DECARDNUMBER FROM MOVIE_PLAYHIS_INFO MPI INNER JOIN IXTERMINAL TML");
        sb.append(" ON MPI.TML_ID = TML.TML_ID");
        if(playHis.getStartTime() != null && playHis.getStartTime().trim().length() != 0){
            sb.append(" AND MPI.START_TIME >= '"+playHis.getStartTime()+"'"); 
        }
        if(playHis.getEndTime() != null && playHis.getEndTime().trim().length() != 0){
            sb.append(" AND MPI.START_TIME <= '"+playHis.getEndTime()+"'");
        }
        if(playHis.getPlayResult() != -1 && playHis.getPlayResult() != 0){
            if(playHis.getPlayResult() == 4){
                sb.append(" AND MPI.PLAY_RESULT = 0");
            }else{
                sb.append(" AND MPI.PLAY_RESULT = " + playHis.getPlayResult());
            }
        }
        if(playHis.getDistrict() != null && playHis.getDistrict().trim().length() != 0){
            sb.append(" AND MPI.DISTRICT LIKE '%"+playHis.getDistrict()+"%'");
        }
        if(playHis.getMovieName() != null && playHis.getMovieName().trim().length() != 0){
            sb.append(" AND MPI.MOVIE_NAME LIKE '%"+playHis.getFilmName()+"%'");
        }
        if(playHis.getCinemaId() != 0 && playHis.getCinemaId() != -1){
            sb.append(" AND TML.CINEMA_ID = "+playHis.getCinemaId());
        }
        if(playHis.getGroupId() != 0 && playHis.getGroupId() != -1){
            sb.append(" AND TML.GROUP_ID = "+playHis.getGroupId());
        }
        if(playHis.getDeCardNumber() != null && playHis.getDeCardNumber().trim().length() != 0){
            sb.append(" AND TML.DECARDNUMBER LIKE '%"+playHis.getDeCardNumber()+"%'");
        }
        sb.append(") A INNER JOIN CINEMA_GROUP CGC");
        sb.append(" ON A.CINEMA_ID = CGC.ID) B INNER JOIN CINEMA_GROUP CGG"); 
        sb.append(" ON B.GROUP_ID = CGG.ID) C INNER JOIN CINEMA_GROUP_AREA CGA");
        sb.append(" ON C.GROUP_ID = CGA.CINEMA_GROUP_ID");
        
        if(playHis.getAreaProvince() != null && playHis.getAreaProvince().trim().length() != 0){
            sb.append(" AND (INSTR(CGA.AREA_PROVINCE,'"+playHis.getAreaProvince()+"')>0 OR INSTR('"+playHis.getAreaProvince()+"',CGA.AREA_PROVINCE)>0)");
        }
        if(playHis.getAreaCity() != null && playHis.getAreaCity().trim().length() != 0){
            sb.append(" AND (INSTR(CGA.AREA_CITY,'"+playHis.getAreaCity()+"')>0 OR INSTR('"+playHis.getAreaCity()+"',CGA.AREA_CITY)>0)");
        }
        if(playHis.getAreaCounty() != null && playHis.getAreaCounty().trim().length() != 0){
            //sb.append(" AND (INSTR(CGA.AREA_COUNTY,'"+playHis.getAreaCounty()+"')>0 OR INSTR('"+playHis.getAreaCounty()+"',CGA.AREA_COUNTY)>0)");
            sb.append(" AND CGA.AREA_COUNTY = '"+playHis.getAreaCounty()+"'");
        }