Menor tiempo de Ejecucion para una funcion Informix

[pelacuaz]

Mis funciones son las siguientes:

DROP FUNCTION avanceobrascant(char);

CREATE PROCEDURE "informix".avanceobrascant(p_anio char(4))
RETURNING char(50) as nombrezona, char(500) as obra, integer as anio, char(50) as mat,
char(50) as mo, char(50) as tot, char(2) as matp, char(2) as matr, char(2) as mop,
char(2) as mor, char(2) as totp, char(2) as totr,
integer as matpene, integer as matrene, integer as matpfeb, integer as matrfeb,
integer as matpmar, integer as matrmar, integer as matpabr, integer as matrabr,
integer as matpmay, integer as matrmay, integer as matpjun, integer as matrjun,
integer as matpjul, integer as matrjul, integer as matpago, integer as matrago,
integer as matpsep, integer as matrsep, integer as matpoct, integer as matroct,
integer as matpnov, integer as matrnov, integer as matpdic, integer as matrdic,

integer as manopene, integer as manorene, integer as manopfeb, integer as manorfeb,
integer as manopmar, integer as manormar, integer as manopabr, integer as manorabr,
integer as manopmay, integer as manormay, integer as manopjun, integer as manorjun,
integer as manopjul, integer as manorjul, integer as manopago, integer as manorago,
integer as manopsep, integer as manorsep, integer as manopoct, integer as manoroct,
integer as manopnov, integer as manornov, integer as manopdic, integer as manordic,

integer as totpene, integer as totrene, integer as totpfeb, integer as totrfeb,
integer as totpmar, integer as totrmar, integer as totpabr, integer as totrabr,
integer as totpmay, integer as totrmay, integer as totpjun, integer as totrjun,
integer as totpjul, integer as totrjul, integer as totpago, integer as totrago,
integer as totpsep, integer as totrsep, integer as totpoct, integer as totroct,
integer as totpnov, integer as totrnov, integer as totpdic, integer as totrdic;

define v_nomzona char(50); define v_numzona char(2); define v_obra char(500); define v_anio char(4);
define v_noobra integer; define v_div char(2);

define v_mat char(50); define v_mo char(50); define v_tot char(50); define v_matp char(2); define v_matr char(2);
define v_mop char(2); define v_mor char(2); define v_totp char(2); define v_totr char(2);

define v_matpene integer; define v_matrene integer;
define v_matpfeb integer; define v_matrfeb integer;
define v_matpmar integer; define v_matrmar integer;
define v_matpabr integer; define v_matrabr integer;
define v_matpmay integer; define v_matrmay integer;
define v_matpjun integer; define v_matrjun integer;
define v_matpjul integer; define v_matrjul integer;
define v_matpago integer; define v_matrago integer;
define v_matpsep integer; define v_matrsep integer;
define v_matpoct integer; define v_matroct integer;
define v_matpnov integer; define v_matrnov integer;
define v_matpdic integer; define v_matrdic integer;

define v_manopene integer; define v_manorene integer;
define v_manopfeb integer; define v_manorfeb integer;
define v_manopmar integer; define v_manormar integer;
define v_manopabr integer; define v_manorabr integer;
define v_manopmay integer; define v_manormay integer;
define v_manopjun integer; define v_manorjun integer;
define v_manopjul integer; define v_manorjul integer;
define v_manopago integer; define v_manorago integer;
define v_manopsep integer; define v_manorsep integer;
define v_manopoct integer; define v_manoroct integer;
define v_manopnov integer; define v_manornov integer;
define v_manopdic integer; define v_manordic integer;

define v_totpene integer; define v_totrene integer;
define v_totpfeb integer; define v_totrfeb integer;
define v_totpmar integer; define v_totrmar integer;
define v_totpabr integer; define v_totrabr integer;
define v_totpmay integer; define v_totrmay integer;
define v_totpjun integer; define v_totrjun integer;
define v_totpjul integer; define v_totrjul integer;
define v_totpago integer; define v_totrago integer;
define v_totpsep integer; define v_totrsep integer;
define v_totpoct integer; define v_totroct integer;
define v_totpnov integer; define v_totrnov integer;
define v_totpdic integer; define v_totrdic integer;

--set debug file to 'd://AvanceObrasCant';
--trace on;

foreach
select no, nombre, division, anio_obra, zona
into v_noobra, v_obra, v_div, v_anio, v_nomzona
from obras ORDER BY nombre ASC

if v_nomzona='4091' then
let v_nomzona='31 - MORELOS';
end if;
if v_nomzona='4096' then
let v_nomzona='81 - ACAPULCO';
end if;
if v_nomzona='4089' then
let v_nomzona='11 - CHILPANCINGO';
end if;
if v_nomzona='4090' then
let v_nomzona='21 - IGUALA';
end if;
if v_nomzona='4092' then
let v_nomzona='41 - TOLUCA';
end if;
if v_nomzona='4095' then
let v_nomzona='71 - VALLE DE BRAVO';
end if;
if v_nomzona='4093' then
let v_nomzona='51 - ALTAMIRANO';
end if;
if v_nomzona='4097' then
let v_nomzona='91 - ZIHUATANEJO';
end if;

select sum(cant) into v_matpene from materialesobra
where fechavencimiento like p_anio||'/01/__' and noobra=v_noobra;

select sum(cantidad) into v_matrene from mat_vales
where fechasalidaalmacen like p_anio||'/01/__' and numobrascod=v_noobra;

select sum(cant) into v_matpfeb from materialesobra
where fechavencimiento like p_anio||'/02/__' and noobra=v_noobra;

select sum(cantidad) into v_matrfeb from mat_vales
where fechasalidaalmacen like p_anio||'/02/__' and numobrascod=v_noobra;

select sum(cant) into v_matpmar from materialesobra
where fechavencimiento like p_anio||'/03/__' and noobra=v_noobra;

select sum(cantidad) into v_matrmar from mat_vales
where fechasalidaalmacen like p_anio||'/03/__' and numobrascod=v_noobra;

select sum(cant) into v_matpabr from materialesobra
where fechavencimiento like p_anio||'/04/__' and noobra=v_noobra;

select sum(cantidad) into v_matrabr from mat_vales
where fechasalidaalmacen like p_anio||'/04/__' and numobrascod=v_noobra;

select sum(cant) into v_matpmay from materialesobra
where fechavencimiento like p_anio||'/05/__' and noobra=v_noobra;

select sum(cantidad) into v_matrmay from mat_vales
where fechasalidaalmacen like p_anio||'/05/__' and numobrascod=v_noobra;

select sum(cant) into v_matpjun from materialesobra
where fechavencimiento like p_anio||'/06/__' and noobra=v_noobra;

select sum(cantidad) into v_matrjun from mat_vales
where fechasalidaalmacen like p_anio||'/06/__' and numobrascod=v_noobra;

select sum(cant) into v_matpjul from materialesobra
where fechavencimiento like p_anio||'/07/__' and noobra=v_noobra;

select sum(cantidad) into v_matrjul from mat_vales
where fechasalidaalmacen like p_anio||'/07/__' and numobrascod=v_noobra;

select sum(cant) into v_matpago from materialesobra
where fechavencimiento like p_anio||'/08/__' and noobra=v_noobra;

select sum(cantidad) into v_matrago from mat_vales
where fechasalidaalmacen like p_anio||'/08/__' and numobrascod=v_noobra;

select sum(cant) into v_matpsep from materialesobra
where fechavencimiento like p_anio||'/09/__' and noobra=v_noobra;

select sum(cantidad) into v_matrsep from mat_vales
where fechasalidaalmacen like p_anio||'/09/__' and numobrascod=v_noobra;

select sum(cant) into v_matpoct from materialesobra
where fechavencimiento like p_anio||'/10/__' and noobra=v_noobra;

select sum(cantidad) into v_matroct from mat_vales
where fechasalidaalmacen like p_anio||'/10/__' and numobrascod=v_noobra;

select sum(cant) into v_matpnov from materialesobra
where fechavencimiento like p_anio||'/11/__' and noobra=v_noobra;

select sum(cantidad) into v_matrnov from mat_vales
where fechasalidaalmacen like p_anio||'/11/__' and numobrascod=v_noobra;

select sum(cant) into v_matpdic from materialesobra
where fechavencimiento like p_anio||'/12/__' and noobra=v_noobra;

select sum(cantidad) into v_matrdic from mat_vales
where fechasalidaalmacen like p_anio||'/12/__' and numobrascod=v_noobra;

.... continuara