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

"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?

> (fn_naturClase( ac.codigo_completo, ac.es_debito))
^^^^^^^^^^^^^^

What's in here?

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
> 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