Re: Query Permormance improvement: URGENT

Of course the order is important. However, the whole is related to the sum of the parts. If you can ensure that each of the parts are operating efficiently, then you can re-assemble them into an efficient whole. If the whole is slow, you can break it down into its component parts to find the critical slow element(s). That's what the explain plan is for. Was that clearer? cheers j. ----- Original Message ----- From: "Andy Kent" <andykent.bristol@virgin.net> To: <informix-list@iiug.org> Sent: Wednesday, March 03, 2004 9:59 AM Subject: Re: Query Permormance improvement: URGENT > Nonsense. The order in which the optimiser decides to do the processing is critical. > > Andy > > "Jack Parker" <vze2qjg5@verizon.net> wrote in message news:<c1qb73$oir$1@terabinaries.xmission.com>... > > Break it down to individual components and determine how fast each of them > > is executing. Your problem will surface. > > > > cheers > > j. > > ----- Original Message ----- > > From: "David Pineda" <david.pineda@co.unisys.com> > > To: <informix-list@iiug.org> > > Sent: Friday, February 27, 2004 6:12 PM > > Subject: Query Permormance improvement: URGENT > > > > > > > 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_ve