Re: Query Permormance improvement: URGENT
Have you tryed another kind of approach in your select?
Maybe optimizing directives.
Have you ran UPDATE STATISTICS recently?
If I was in the same situation I start to split the query in temporary tables.
Chucho!
-----Original Message-----
From: "David Pineda" <david.pineda@co.unisys.com>
To: informix-list@iiug.org
Date: Fri, 27 Feb 2004 17:12:35 -0500
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_vigencia_datos
NESTED LOOP JOIN
UDRs in query:
--------------
UDR id : 794
UDR name: fn_naturclase
UDR id : 794
UDR name: fn_naturclase
UDR id : 794
UDR name: fn_naturclase
Regards,
David Pineda
Jean Sagi
jeansagi@myrealbox.com
jeansagi@netscape.net
sending to informix-list