Menor tiempo de Ejecucion para una funcion Informix

[pelacuaz]

LA OTRA FUNCION (que se resuelve en 12 min)

DROP FUNCTION avanceobrascosto(char);

CREATE PROCEDURE "informix".avanceobrascosto(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,
decimal(16,2) as cosmatpene, decimal(16,2) as cosmatrene,
decimal(16,2) as cosmatpfeb, decimal(16,2) as cosmatrfeb,
decimal(16,2) as cosmatpmar, decimal(16,2) as cosmatrmar,
decimal(16,2) as cosmatpabr, decimal(16,2) as cosmatrabr,
decimal(16,2) as cosmatpmay, decimal(16,2) as cosmatrmay,
decimal(16,2) as cosmatpjun, decimal(16,2) as cosmatrjun,
decimal(16,2) as cosmatpjul, decimal(16,2) as cosmatrjul,
decimal(16,2) as cosmatpago, decimal(16,2) as cosmatrago,
decimal(16,2) as cosmatpsep, decimal(16,2) as cosmatrsep,
decimal(16,2) as cosmatpoct, decimal(16,2) as cosmatroct,
decimal(16,2) as cosmatpnov, decimal(16,2) as cosmatrnov,
decimal(16,2) as cosmatpdic, decimal(16,2) as cosmatrdic,

decimal(16,2) as cosmanopene, decimal(16,2) as cosmanorene,
decimal(16,2) as cosmanopfeb, decimal(16,2) as cosmanorfeb,
decimal(16,2) as cosmanopmar, decimal(16,2) as cosmanormar,
decimal(16,2) as cosmanopabr, decimal(16,2) as cosmanorabr,
decimal(16,2) as cosmanopmay, decimal(16,2) as cosmanormay,
decimal(16,2) as cosmanopjun, decimal(16,2) as cosmanorjun,
decimal(16,2) as cosmanopjul, decimal(16,2) as cosmanorjul,
decimal(16,2) as cosmanopago, decimal(16,2) as cosmanorago,
decimal(16,2) as cosmanopsep, decimal(16,2) as cosmanorsep,
decimal(16,2) as cosmanopoct, decimal(16,2) as cosmanoroct,
decimal(16,2) as cosmanopnov, decimal(16,2) as cosmanornov,
decimal(16,2) as cosmanopdic, decimal(16,2) as cosmanordic,

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

define v_noobra integer; define v_div char(2);
define v_nomzona char(50);
define v_numzona char(2);
define v_obra char(500);
define v_anio integer;
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 decimal(16,2);
define v_matrene decimal(16,2);
define v_matpfeb decimal(16,2);
define v_matrfeb decimal(16,2);
define v_matpmar decimal(16,2);
define v_matrmar decimal(16,2);
define v_matpabr decimal(16,2);
define v_matrabr decimal(16,2);
define v_matpmay decimal(16,2);
define v_matrmay decimal(16,2);
define v_matpjun decimal(16,2);
define v_matrjun decimal(16,2);
define v_matpjul decimal(16,2);
define v_matrjul decimal(16,2);
define v_matpago decimal(16,2);
define v_matrago decimal(16,2);
define v_matpsep decimal(16,2);
define v_matrsep decimal(16,2);
define v_matpoct decimal(16,2);
define v_matroct decimal(16,2);
define v_matpnov decimal(16,2);
define v_matrnov decimal(16,2);
define v_matpdic decimal(16,2);
define v_matrdic decimal(16,2);

define v_manopene decimal(16,2);
define v_manorene decimal(16,2);
define v_manopfeb decimal(16,2);
define v_manorfeb decimal(16,2);
define v_manopmar decimal(16,2);
define v_manormar decimal(16,2);
define v_manopabr decimal(16,2);
define v_manorabr decimal(16,2);
define v_manopmay decimal(16,2);
define v_manormay decimal(16,2);
define v_manopjun decimal(16,2);
define v_manorjun decimal(16,2);
define v_manopjul decimal(16,2);
define v_manorjul decimal(16,2);
define v_manopago decimal(16,2);
define v_manorago decimal(16,2);
define v_manopsep decimal(16,2);
define v_manorsep decimal(16,2);
define v_manopoct decimal(16,2);
define v_manoroct decimal(16,2);
define v_manopnov decimal(16,2);
define v_manornov decimal(16,2);
define v_manopdic decimal(16,2);
define v_manordic decimal(16,2);

define v_totpene decimal(16,2);
define v_totrene decimal(16,2);
define v_totpfeb decimal(16,2);
define v_totrfeb decimal(16,2);
define v_totpmar decimal(16,2);
define v_totrmar decimal(16,2);
define v_totpabr decimal(16,2);
define v_totrabr decimal(16,2);
define v_totpmay decimal(16,2);
define v_totrmay decimal(16,2);
define v_totpjun decimal(16,2);
define v_totrjun decimal(16,2);
define v_totpjul decimal(16,2);
define v_totrjul decimal(16,2);
define v_totpago decimal(16,2);
define v_totrago decimal(16,2);
define v_totpsep decimal(16,2);
define v_totrsep decimal(16,2);
define v_totpoct decimal(16,2);
define v_totroct decimal(16,2);
define v_totpnov decimal(16,2);
define v_totrnov decimal(16,2);
define v_totpdic decimal(16,2);
define v_totrdic decimal(16,2);

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


... continuara