Re: Query Permormance improvement: URGENT

There is plenty to go on here. The fact that it is scanning e.version_resultado to evaluate what looks like a very selective filter suggests you may need an index on this column, or for it to be included in one of your existing indexes. Also the autoindex to version_resultado suggests that you need to build a permanent index to the join columns into this table. However, the low cost you are getting suggests that UPDATE STATISTICS hasn't been run for a while. It may choose a totally different table order once you have done this, in which case you would need to reconsider your indexing strategy. I would definitely NOT follow the advice in this thread to split the query up. That really would be very, very bad practice and merely show a refusal to understand how the query should be getting optimised. DON'T DO IT!! Given the size of the tables and the selectivity of the query I think you should be able to get it to run in under 1 second without having to hack the code about. How about posting the explain output here after you've run UPDATE STATISTICS. Andy "David Pineda" <david.pineda@co.unisys.com> wrote in message news:<c1ofcn$ppj$1@trsvr.tr.unisys.com>... > We have a performance problem with a sentence that is been executed from a > store procedure in the DB Informix 9.30 on Compaq Unix Thru-64. This > sentence is taking over 30 sec. We need that this sentence take less time, > do you have any idea what I can do? > > We were thinking change this to use ESQL/C this is a good idea, we don't > really have experience in this tool. I thanks is somebody can give a way to > solve this.. ASAP. > > > > QUERY: > ------ > SELECT > e.nombre_arbol > ,e.cod_cons_padre > ,e.d_inicio_periodo > ,e.id_version_consolidacion > ,'xx' > , ac.codigo_completo > , ac.indice_codigo_i > , ac.indice_codigo_d > , ac.descripcion > , ac.es_debito > , ac.nivel > , SUM(NVL(sf.saldo_corriente,0) + > NVL(sf.saldo_nocorriente,0))--,REP_RC_SALDO_FUENTE > ,0-- ,REP_RC_TCONCILIACION > ,SUM(NVL(sf.saldo_corriente,0) + NVL(sf.saldo_nocorriente,0))-- > ,REP_RC_SALDO_TRAN > ,0-- ,REP_RC_RECIPROCAS > ,0-- ,REP_RC_TINTERES > ,0-- ,REP_RC_TDISTRIBUCION > ,0-- ,REP_RC_CIERRE > ,SUM(NVL(sf.saldo_corriente,0) + > NVL(sf.saldo_nocorriente,0)) --,REP_RC_SALDO_CON > ,SUM(NVL(sf.saldo_corriente,0) + NVL(sf.saldo_nocorriente,0)) * > (fn_naturClase( ac.codigo_completo, ac.es_debito)) > ,SUM(NVL(sf.saldo_corriente,0)) --,REP_RC_SF_CORR > ,SUM(NVL(sf.saldo_nocorriente,0)) --,REP_RC_SF_NOCORR > ,0-- ,REP_RC_TC_CORR > ,0-- ,REP_RC_TC_NOCORR > ,0-- ,REP_RC_TR_CORR > ,0-- ,REP_RC_TR_NOCORR > ,0-- ,REP_RC_TI_CORR > ,0-- ,REP_RC_TI_NOCORR > ,0-- ,REP_RC_TD_CORR > ,0-- ,REP_RC_TD_NOCORR > ,0-- ,REP_RC_TCI_CORR > ,0-- ,REP_RC_TCI_NOCORR > ,SUM(NVL(sf.saldo_corriente,0)) * (fn_naturClase( ac.codigo_completo, > ac.es_debito)) --,REP_RC_AG_CORR > ,SUM(NVL(sf.saldo_nocorriente,0)) * (fn_naturClase( ac.codigo_completo, > ac.es_debito)) --,REP_RC_AG_NOCORR > ,SUM(NVL(sf.saldo_corriente,0)) --,REP_RC_SCC_CORR > ,SUM(NVL(sf.saldo_nocorriente,0)) -- ,REP_RC_SCC_NOCORR > FROM > rep_entidad_por_nodo e > ,arbol_codigo ac > ,saldo_fuente sf > ,Version_dato_fuente ma > ,version_resultado vr > WHERE > e.id_entidad = sf.id_entidad > AND e.d_inicio_periodo = sf.d_inicio_periodo > AND ac.d_inicio_periodo_codigo = sf.d_inicio_periodo_codigo > AND ac.d_version_codigo = sf.d_version_codigo > AND ac.indice_codigo_i = sf.indice_codigo_i > and e.nombre_arbol = 'CLASIFICACION FMI' > and e.cod_cons_padre = 284 > and e.id_version_consolidacion = 8 > and e.d_inicio_periodo = mdy(10,01,2002) > AND e.version_resultado = '2004-02-17 16:19:10.00000' > AND E.USUARIO = 'Azucena Sanabria' > AND E.D_FECHASISTE = '2004-02-17 16:34:00.00000' > AND e.version_resultado = '2004-02-17 16:19:10.00000' > AND vr.d_inicio_periodo = mdy(10,01,2002) > and ma.d_Inicio_periodo = mdy(10,01,2002) > and ma.ID_entidad = e.id_entidad > and ma.d_Version_dato_fuente <= vr.d_vigencia_datos > and ma.d_vigencia_hasta >= vr.d_vigencia_datos > and sf.d_Inicio_periodo = ma.d_Inicio_periodo > and sf.d_Version_dato_fuente = ma.d_Version_dato_fuente > GROUP BY > e.nombre_arbol > , e.cod_cons_padre > , e.d_inicio_periodo > , e.id_version_consolidacion > , ac.codigo_completo > , ac.indice_codigo_i > , ac.indice_codigo_d > , ac.descripcion > , ac.es_debito > , ac.nivel > > > Rows expected:: > 3500 > > Tables size: > > select count(*) from rep_entidad_por_nodo ; --165 rows > select count(*) from arbol_codigo ; --24912 rows > select count(*) from saldo_fuente ; --7087 rows > select count(*) from Version_dato_fuente; --12 rows > select count(*) from version_resultado ; --5 rows> > > Execution plan output: > > > Estimated Cost: 45 > Estimated # of Rows Returned: 9 > Temporary Files Required For: Group By > > 1) informix.e: INDEX PATH > Filters: (informix.e.version_resultado = datetime(2004-02-17 > 16:19:10.00000) year to fraction(5) AND informix.e.version_resultado = > datetime(2004-02-17 16:19:10.00000) year to fraction(5) ) > (1) Index Keys: usuario d_fechasiste d_inicio_periodo > id_version_consolidacion nombre_arbol cod_cons_padre (Key-First) (Serial, > fragments: ALL) > Lower Index Filter: ((((informix.e.d_fechasiste = > datetime(2004-02-17 16:34:00.00000) year to fraction(5) AND > informix.e.id_version_consolidacion = 8 ) AND informix.e.usuario = 'Azucena > Sanabria' ) AND informix.e.nombre_arbol = 'CLASIFICACION FMI' ) AND > informix.e.d_inicio_periodo = 2002-10-01 ) > Key-First Filters: (informix.e.cod_cons_padre = 284 ) > 2) informix.ma: INDEX PATH > Filters: (informix.ma.d_inicio_periodo = 2002-10-01 AND > informix.e.d_inicio_periodo = informix.ma.d_inicio_periodo ) > (1) Index Keys: id_entidad (Serial, fragments: ALL) > Lower Index Filter: informix.ma.id_entidad = informix.e.id_entidad > NESTED LOOP JOIN > 3) informix.sf: INDEX PATH > (1) Index Keys: d_inicio_periodo d_version_dato_fuente id_entidad > (Serial, fragments: ALL) > Lower Index Filter: ((informix.sf.d_version_dato_fuente = > informix.ma.d_version_dato_fuente AND informix.sf.d_inicio_periodo = > informix.ma.d_inicio_periodo ) AND informix.sf.id_entidad = > informix.ma.id_entidad ) > NESTED LOOP JOIN > 4) informix.ac: INDEX PATH > (1) Index Keys: d_inicio_periodo_codigo d_version_codigo indice_codigo_i > (Serial, fragments: ALL) > Lower Index Filter: ((informix.ac.indice_codigo_i = > informix.sf.indice_codigo_i AND informix.ac.d_inicio_periodo_codigo = > informix.sf.d_inicio_periodo_codigo ) AND informix.ac.d_version_codigo = > informix.sf.d_version_codigo ) > NESTED LOOP JOIN > 5) informix.vr: AUTOINDEX PATH >