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_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
> > Filters:
> > Table Scan Filters: informix.vr.d_inicio_periodo = 2002-10-01
> > (1) Index Keys: d_vigencia_datos
> > Lower Index Filter: informix.ma.d_version_dato_fuente <=
> > informix.vr.d_vigencia_datos
> > Upper Index Filter: informix.ma.d_vigencia_hasta >=
> > informix.vr.d