consult 360,000 codes quickly java

Navigation
0

I have 1 main Query whose response time is optimal, the problem arises when after executing my query inside the while I execute a second query that reads the code field and queries 1 by one, here my code: 

$_Query ="select * from remesas";
ResultSet rs = st.executeQuery($_Query);
while(rs.next()){
 String SQLDevo ="SELECT DEVO.TIPO,CASE WHEN DEVO.TIPO='ER' THEN 'SF' WHEN DEVO.TIPO='EC' THEN 'BP'WHEN DEVO.TIPO='DR' THEN 'DV' end as clave,' ' as csituacion,COALESCE(DEVO.NOMBRE||' '||DEVO.APELLIDO_PATERNO||' '||DEVO.APELLIDO_MATERNO ,' ') AS NOMBRE, "
                        + " COALESCE(DEVO.VISITA,' ') AS NUM_VISITAS,COALESCE(DEVO.NUM_IDENTIFICADOR,' ') AS NUM_IDENTIFICADOR ,COALESCE(S1.TAG,' ') AS TAG,  "
                        + " TO_CHAR(DEVO.FECHA,'DD-MM-YYYY') AS FECHA,COALESCE(TO_CHAR(FECHA_ENTREGA,'DD-MM-YYYY'),' ') AS FECHA_ENTREGA,"
                        + " CASE WHEN TIPO ='EC' THEN "
                        + " DOMICILIO||'|PISOS'||'|'||COLOR_DOMICILIO||'|'||S1.DESCRIPTION||'|'|| "
                        + " CASE  "
                        + " WHEN REJA_BARDA = 'N' THEN 'SIN/REJA'  "
                        + " WHEN REJA_BARDA = 'S' THEN 'CON/REJA' "
                        + " END ||'|'||  "
                        + " CASE "
                        + " WHEN COCHERA_TEJADA = 'N' THEN 'SIN/COCHERA' "
                        + " WHEN COCHERA_TEJADA = 'S' THEN 'CON/COCHERA' "
                        + " END  "
                        + " WHEN S2.DESCRIPTION IS NOT NULL THEN S2.DESCRIPTION"
                        + " ELSE '|'|| S2.DESCRIPTION "
                        + "  END AS COMENTARIOS, "
                        + " COALESCE(S2.TAG,' ') AS TAG2,COALESCE(S3.DESCRIPTION,' ') AS DESCRIPTION,COALESCE(S4.DESCRIPTION,' ') AS DESCRIPTION2,COALESCE(S5.TAG,' ') as MOTIVO,COALESCE(S5.ATTRIBUTE3,' ') AS MOTIVOCLIENTE  "
                        + " FROM XXQP.XXQP_DEVOLUCIONES_LN_TBL DEVO  "
                        + " LEFT JOIN ( "
                        + " SELECT TAG,DESCRIPTION FROM APPLSYS.FND_LOOKUP_VALUES LV WHERE  LV.LOOKUP_TYPE  = 'XXQP_PARENTESCO' AND LV.LANGUAGE='ESA' "
                        + " ) S1 ON S1.DESCRIPTION = DEVO.PARENTESCO "
                        + " LEFT JOIN ( "
                        + " SELECT TAG,DESCRIPTION FROM  APPLSYS.FND_LOOKUP_VALUES LV WHERE  LV.LOOKUP_TYPE = 'XXQP_IDENTIFICACION' AND LV.LANGUAGE='ESA' "
                        + " ) S2 ON S2.DESCRIPTION = DEVO.IDENTIFICADOR  "
                        + " LEFT JOIN ( "
                        + " SELECT DESCRIPTION,LOOKUP_CODE FROM APPLSYS.FND_LOOKUP_VALUES LV WHERE LV.LOOKUP_TYPE = 'XXQP_TIPOS_PUERTA' AND LV.LANGUAGE='ESA' "
                        + " ) S3 ON S3.LOOKUP_CODE = DEVO.TIPO_PUERTA  "
                        + " LEFT JOIN ( "
                        + " SELECT DESCRIPTION,LOOKUP_CODE FROM APPLSYS.FND_LOOKUP_VALUES LV WHERE LV.LOOKUP_TYPE = 'XXQP_ESTATUS_MEDIDOR' AND LV.LANGUAGE='ESA' "
                        + " ) S4 ON  S4.LOOKUP_CODE =DEVO.MEDIDOR "
                        + " LEFT JOIN ( "
                        + " SELECT TAG,DESCRIPTION,ATTRIBUTE3,ATTRIBUTE2 FROM APPLSYS.FND_LOOKUP_VALUES LV WHERE LV.LOOKUP_TYPE = 'XXQP_MOTIVOS_PLATINUM' AND LV.LANGUAGE='ESA' "
                        + " ) S5 ON S5.DESCRIPTION= DEVO.MOTIVO  AND S5.ATTRIBUTE2 =" + rs.getString("cliente_id")
                        + " WHERE  ROWNUM<2 AND DEVO.CODIGO_QP= " + rs.getString("CODIGO_QP") ;
ResultSet rsSegundo = stSegundo.executeQuery(SQLDevo);
if(rsSegundo.next()){
   Nombre = rsSegundo.getString("NOMBRE");
   Num_Visitas = rsSegundo.getString("NUM_VISITAS");
   Num_Identificador = rsSegundo.getString("NUM_IDENTIFICADOR");
}
}

So if I have 1000 rows in my first query if it processes them well, but when it comes in the second query, consult 1 for one.

How could this process be improved?

Greetings.

    
asked by Jairo Ordaz Moreno 30.11.2016 в 17:26
source

2 answers

3

The problem is that you are doing 1 query to bring the ids you want to consult, and then a second query that runs for each of the results of the previous query , which means that if you first query brought 1000 rows, then you make 1000 queries to the database. No matter how much you optimize your query, the problem is simply how you are doing it.

It would be best to use a JOIN and thus execute a single query: 

select s.*
from devoluciones d
    inner join remesas r
where d.codigo_qp = r.codigo_qp

And from this query (1 single execution), read the data you really need.

    
answered by 30.11.2016 в 17:29
0

You'll have better performance if you do it all in one query at a time with a join: 

select d.*
  from remesas r
  join devoluciones d
    on d.codigo_qp = r.codigo_qp
    
answered by 30.11.2016 в 17:29
Validate a Jquery calendar with a number of days in Select Paginate objects from the same loop in Rails