On SP's performance (kinda weird)

Hi. I catched a SP whichs take 10 seconds in perform. I checked that SQL on it was correctly written, used indexes, and so on (anyway, it's not a complex one, it uses a primary key and only returns one record), but I realized that central SQL takes almost all the time to execute. I tried executing that SQL alone and it was quick, then I tried changing SP in order to write down literal values instead of parameters or variables, and also ran fine. Then I did this operation again changing a variable for a literal each time, 'till I found a single variable which gave me same behaviour when I changed it for its literal. Here it is the source code. It's fast when I write the value 590891 instead of variable, and slow when I use variable. I verified via debug file that values in the variables were equivalent between versions. Update statistics and re-creation of SP didn't work. My question is Why is there such a difference of behaviour? How can I fix it? I was expecting that both ways would be the same (one static and the other dynamic, and I want to fix the dynamic one, of course) This situation happened on an IDS 10 FC8 on a Solaris9 server Thanks in advance Omar Muñoz create procedure "informix".sp_rec_cert_gm_x (p_moneda smallint, p_sucur char(3), p_cve_linea smallint, p_cve_prod smallint, p_poliza integer, p_endoso integer, p_aemi smallint, p_nreexp smallint, p_cve_seccion smallint, p_no_cert integer, p_no_dep smallint ) returning smallint, char(30), char(30), char(30), date, smallint, integer, smallint, smallint, smallint, smallint, smallint, date, date, date, date, smallint, date, decimal(12,2), date, date, smallint, decimal (8,4), char(30), char(30), char(30), date, -- fec_baja smallint, -- status date; -- fec_rehab -- datos asegurado DEFINE vd_endosok smallint; DEFINE vd_ap_paterno char(30); DEFINE vd_ap_materno char(30); DEFINE vd_nombre char(30); DEFINE vd_fecha_nac date; DEFINE vd_sexo smallint; DEFINE vd_num_asegurado integer; DEFINE vd_edad smallint; DEFINE vd_tipo_asegurado smallint; DEFINE vd_cve_edo_civ smallint; DEFINE vd_cve_ocupacion smallint; DEFINE vd_cve_riesgo smallint; DEFINE vd_fec_ing_cia date; DEFINE vd_fec_ing_atlas date; DEFINE vd_fec_antig_nac date; DEFINE vd_fec_antig_ext date; DEFINE vd_cve_estado smallint; DEFINE vd_fec_alta date; DEFINE vd_salario decimal(12,2); DEFINE vd_fec_preex date; DEFINE vd_fec_sida date; DEFINE vd_cve_parent smallint; DEFINE vd_porc_extrap decimal (8,4); DEFINE vd_nombre_tit char(30); DEFINE vd_ap_pat_tit char(30); DEFINE vd_ap_mat_tit char(30); DEFINE vd_endoso_alta smallint; DEFINE vd_fec_baja date; DEFINE vd_status smallint; DEFINE vd_fec_rehab date; LET vd_nombre_tit = ''; LET vd_ap_pat_tit = ''; LET vd_ap_mat_tit = ''; select i.endosok, a.ap_paterno, a.ap_materno, a.nombre, a.fecha_nac, a.sexo, c.num_asegurado,c.edad, c.tipo_asegurado, c.cve_edo_civ,c.cve_ocupacion,c.cve_riesgo, c.fec_ingreso_cia, c.fec_ingreso_atlas, c.fec_antig_nac, c.fec_antig_ext, c.cve_estado, c.fec_alta, c.salario, c.fec_preexistencia, c.fec_sida, c.cve_parent, c.porc_extraprima, c.cve_estado,c.fec_baja, c.status, c.fec_rehabilitacion into vd_endosok, vd_ap_paterno, vd_ap_materno, vd_nombre, vd_fecha_nac, vd_sexo, vd_num_asegurado, vd_edad, vd_tipo_asegurado, vd_cve_edo_civ, vd_cve_ocupacion, vd_cve_riesgo, vd_fec_ing_cia, vd_fec_ing_atlas, vd_fec_antig_nac, vd_fec_antig_ext, vd_cve_estado, vd_fec_alta, vd_salario, vd_fec_preex, vd_fec_sida, vd_cve_parent, vd_porc_extrap, vd_cve_estado, vd_fec_baja, vd_status, vd_fec_rehab from s07_pol_cert c, s4_incgral i, s4_aseg_comun a where c.sucursalk = p_sucur and c.cve_linea = p_cve_linea and c.cve_prod = p_cve_prod and c.polizak = 590891 and --p_poliza and c.endosok = 0 and c.a_emisionk = p_aemi and c.no_reexpedicionk = p_nreexp and c.cve_linea_indk = p_cve_linea and c.cve_secc_gpok = p_cve_seccion and c.num_certk = p_no_cert and c.num_dependk = p_no_dep and i.sucursalk = c.sucursalk and i.cve_linea = c.cve_linea and i.cve_prod = c.cve_prod and i.polizak = c.polizak and i.endosok = c.endosok and i.a_emisionk = c.a_emisionk and i.no_reexpedicionk = c.no_reexpedicionk and i.cve_linea_indk = c.cve_linea and i.cve_secc_gpok = c.cve_secc_gpok and i.num_incisok = c.num_certk and i.num_dependk = c.num_dependk and c.num_asegurado = a.num_asegurado; return vd_endosok, vd_ap_paterno, vd_ap_materno, vd_nombre, vd_fecha_nac, vd_sexo, vd_num_asegurado, vd_edad, vd_tipo_asegurado, vd_cve_edo_civ, vd_cve_ocupacion, vd_cve_riesgo, vd_fec_ing_cia, vd_fec_ing_atlas, vd_fec_antig_nac, vd_fec_antig_ext, vd_cve_estado, vd_fec_alta, vd_salario, vd_fec_preex, vd_fec_sida, vd_cve_parent, vd_porc_extrap, vd_ap_pat_tit, vd_ap_mat_tit, vd_nombre_tit, vd_fec_baja, vd_status, vd_fec_rehab ; --with resume; end procedure;

Omar Mu'oz wrote: > Hi. > > I catched a SP whichs take 10 seconds in perform. I checked that SQL on it was correctly written, used indexes, and so on (anyway, it's not a complex one, it uses a primary key and only returns one record), but I realized that central SQL takes almost all the time to execute. > > I tried executing that SQL alone and it was quick, then I tried changing SP in order to write down literal values instead of parameters or variables, and also ran fine. Then I did this operation again changing a variable for a literal each time, 'till I found a single variable which gave me same behaviour when I changed it for its literal. > > Here it is the source code. It's fast when I write the value 590891 instead of variable, and slow when I use variable. I verified via debug file that values in the variables were equivalent between versions. Update statistics and re-creation of SP didn't work. > > My question is Why is there such a difference of behaviour? How can I fix it? I was expecting that both ways would be the same (one static and the other dynamic, and I want to fix the dynamic one, of course) > > This situation happened on an IDS 10 FC8 on a Solaris9 server > > Thanks in advance > > Omar Mu'oz > > > > create procedure "informix".sp_rec_cert_gm_x > (p_moneda smallint, > p_sucur char(3), > p_cve_linea smallint, > p_cve_prod smallint, > p_poliza integer, > p_endoso integer, > p_aemi smallint, > p_nreexp smallint, > p_cve_seccion smallint, > p_no_cert integer, > p_no_dep smallint > ) > returning > smallint, > char(30), > char(30), > char(30), > date, > smallint, > integer, > smallint, > smallint, > smallint, > smallint, > smallint, > date, > date, > date, > date, > smallint, > date, > decimal(12,2), > date, > date, > smallint, > decimal (8,4), > char(30), > char(30), > char(30), > date, -- fec_baja > smallint, -- status > date; -- fec_rehab > > -- datos asegurado > DEFINE vd_endosok smallint; > DEFINE vd_ap_paterno char(30); > DEFINE vd_ap_materno char(30); > DEFINE vd_nombre char(30); > DEFINE vd_fecha_nac date; > DEFINE vd_sexo smallint; > DEFINE vd_num_asegurado integer; > DEFINE vd_edad smallint; > DEFINE vd_tipo_asegurado smallint; > DEFINE vd_cve_edo_civ smallint; > DEFINE vd_cve_ocupacion smallint; > DEFINE vd_cve_riesgo smallint; > DEFINE vd_fec_ing_cia date; > DEFINE vd_fec_ing_atlas date; > DEFINE vd_fec_antig_nac date; > DEFINE vd_fec_antig_ext date; > DEFINE vd_cve_estado smallint; > DEFINE vd_fec_alta date; > DEFINE vd_salario decimal(12,2); > DEFINE vd_fec_preex date; > DEFINE vd_fec_sida date; > DEFINE vd_cve_parent smallint; > DEFINE vd_porc_extrap decimal (8,4); > DEFINE vd_nombre_tit char(30); > DEFINE vd_ap_pat_tit char(30); > DEFINE vd_ap_mat_tit char(30); > DEFINE vd_endoso_alta smallint; > DEFINE vd_fec_baja date; > DEFINE vd_status smallint; > DEFINE vd_fec_rehab date; > > > LET vd_nombre_tit = ''; > LET vd_ap_pat_tit = ''; > LET vd_ap_mat_tit = ''; > > select > i.endosok, a.ap_paterno, a.ap_materno, a.nombre, > a.fecha_nac, a.sexo, c.num_asegurado,c.edad, c.tipo_asegurado, > c.cve_edo_civ,c.cve_ocupacion,c.cve_riesgo, > c.fec_ingreso_cia, c.fec_ingreso_atlas, c.fec_antig_nac, > c.fec_antig_ext, c.cve_estado, c.fec_alta, c.salario, > c.fec_preexistencia, c.fec_sida, c.cve_parent, > c.porc_extraprima, c.cve_estado,c.fec_baja, > c.status, c.fec_rehabilitacion > into > vd_endosok, vd_ap_paterno, vd_ap_materno, vd_nombre, > vd_fecha_nac, vd_sexo, vd_num_asegurado, vd_edad, > vd_tipo_asegurado, vd_cve_edo_civ, vd_cve_ocupacion, > vd_cve_riesgo, vd_fec_ing_cia, vd_fec_ing_atlas, > vd_fec_antig_nac, vd_fec_antig_ext, vd_cve_estado, > vd_fec_alta, vd_salario, vd_fec_preex, vd_fec_sida, > vd_cve_parent, vd_porc_extrap, vd_cve_estado, vd_fec_baja, > vd_status, vd_fec_rehab > from > s07_pol_cert c, > s4_incgral i, > s4_aseg_comun a > where > c.sucursalk = p_sucur and > c.cve_linea = p_cve_linea and > c.cve_prod = p_cve_prod and > c.polizak = 590891 and --p_poliza and > c.endosok = 0 and > c.a_emisionk = p_aemi and > c.no_reexpedicionk = p_nreexp and > c.cve_linea_indk = p_cve_linea and > c.cve_secc_gpok = p_cve_seccion and > c.num_certk = p_no_cert and > c.num_dependk = p_no_dep and > i.sucursalk = c.sucursalk and > i.cve_linea = c.cve_linea and > i.cve_prod = c.cve_prod and > i.polizak = c.polizak and > i.endosok = c.endosok and > i.a_emisionk = c.a_emisionk and > i.no_reexpedicionk = c.no_reexpedicionk and > i.cve_linea_indk = c.cve_linea and > i.cve_secc_gpok = c.cve_secc_gpok and > i.num_incisok = c.num_certk and > i.num_dependk = c.num_dependk and > c.num_asegurado = a.num_asegurado; > > return > vd_endosok, > vd_ap_paterno, > vd_ap_materno, > vd_nombre, > vd_fecha_nac, > vd_sexo, > vd_num_asegurado, > vd_edad, > vd_tipo_asegurado, > vd_cve_edo_civ, > vd_cve_ocupacion, > vd_cve_riesgo, > vd_fec_ing_cia, > vd_fec_ing_atlas, > vd_fec_antig_nac, > vd_fec_antig_ext, > vd_cve_estado, > vd_fec_alta, > vd_salario, > vd_fec_preex, > vd_fec_sida, > vd_cve_parent, > vd_porc_extrap, > vd_ap_pat_tit, > vd_ap_mat_tit, > vd_nombre_tit, > vd_fec_baja, > vd_status, > vd_fec_rehab > ; --with resume; > end procedure; > > > > You should get the EXPLAIN from the procedure and compare it with and without variable. My guess is that it will be different or you're using a value very specific... The big picture is this: When you send queries to the engine using variables, the query optimization may not be optimal. Why? Because it will have to choose between different query plans without having the values given in runtime. The common side effect of this is that for some values query plan A may be optimal, but for other values it may be very bad. If you ask the engine to choose the query plan with values it can look at the table histograms (distributions) and see if for that specific values