SQL Tunning
A user asked for help tuning a slow multi-table join that used two correlated NOT IN subqueries (against agrupado and am_bolsa) plus a range join on phone-number suffixes. Art Kagel said the correlated subqueries were the main cost and suggested rewriting in ANSI-92 syntax, replacing them with LEFT OUTER JOINs filtered on NULL keys, keeping inner-table join/filter conditions in ON clauses and only the IS NULL tests in WHERE. The poster's first attempt was malformed (extra copies of abonado) and still costed badly; Art posted a corrected version, and another reader suggested a missing index on mae_mercado.co_mercado. No confirmation of the final result is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello all again
Im bothering you all just because im facing same issues while trying to do sql
tunning.
Thjis is the query:
SELECT
edithor.localidad_rango.co_mercado,
edithor.mae_mercado.no_mercado,
count(edithor.abonado.nu_inscripcion )
FROM
edithor.abonado,
edithor.am_par_categorias,
edithor.localidad_rango,
edithor.mae_mercado
WHERE
( edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria ) and
( edithor.am_par_categorias.mr_procesar = '1' ) and
( edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad ) and
( edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) and
( edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) and
( edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde) and
( edithor.abonado.nu_sufijo <= edithor.localidad_rango.nu_sufijo_hasta) and
( edithor.localidad_rango.co_mercado in ('259','260','261','262','263','264'))
and
( edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado )
group by 1,2
and
( edithor.abonado.nu_inscripcion not in (
select
g.nu_inscripcion
from
agrupado g
where
g.nu_inscripcion = edithor.abonado.nu_inscripcion) ) and
( edithor.abonado.nu_inscripcion not in (
select
b.nu_inscripcion
from
am_bolsa b
where
b.nu_inscripcion = edithor.abonado.nu_inscripcion) )
group by 1,2
Here are the dbschema of the tables:
Table: abonado
create table "edithor".abonado
(
nu_inscripcion char(8) not null ,
co_ddn varchar(5) not null ,
nu_telefono char(10) not null ,
nu_telefono_letras varchar(20),
nu_prefijo char(5),
nu_sufijo char(5),
nu_cpp char(4),
ti_abonado char(2),
co_categoria char(1),
ti_servicio char(2),
no_figuracion varchar(50),
ap_figuracion varchar(80),
no_calle varchar(30),
nu_casa varchar(5),
nu_piso varchar(3),
nu_depart varchar(4),
ac_direcc varchar(60),
co_localidad char(7) not null ,
co_iva char(2),
co_postal char(4),
cpa varchar(9),
nu_cuit char(11),
mr_publicar char(1),
ti_documento_id char(4),
nu_documento_id varchar(15),
mr_activo char(1),
mr_pbx char(1),
co_ddn_principal varchar(5),
nu_tel_principal char(10),
co_actividad char(6),
co_cli_telefonica char(12),
nu_ult_ooss integer,
fe_alta datetime year to second,
fe_cambio datetime year to second,
co_usuario char(10),
co_cliente_teco char(12),
nu_inscripcion_pub char(8),
co_categoria_teco char(2),
primary key (nu_inscripcion)
);
revoke all on "edithor".abonado from "public";
create index "edithor".fe_alta on "edithor".abonado (fe_alta)
using btree ;
create index "edithor".ix405_23 on "edithor".abonado (mr_publicar)
using btree ;
create unique index "edithor".ix_abonado1 on "edithor".abonado
(co_ddn,nu_telefono) using btree ;
create index "edithor".ix_abonado2 on "edithor".abonado (ap_figuracion)
using btree ;
create index "edithor".ix_abonado3 on "edithor".abonado (no_calle)
using btree ;
create index "edithor".ix_abonado4 on "edithor".abonado (nu_ult_ooss)
using btree ;
create index "edithor".ix_cli_tasa on "edithor".abonado (co_cli_telefonica)
using btree ;
create index "edithor".ix_cuit on "edithor".abonado (nu_cuit)
using btree ;
create index "informix".ix_leox4 on "edithor".abonado (nu_sufijo)
using btree ;
create index "edithor".xabo_clipub on "edithor".abonado (co_cliente_teco)
using btree ;
create index "edithor".xie1abonado on "edithor".abonado (co_ddn,
nu_prefijo,co_localidad) using btree ;
create index "edithor".xie2abonado on "edithor".abonado (co_localidad,
co_postal) using btree ;
alter table "edithor".abonado add constraint (foreign key (ti_documento_id)
references "edithor".mae_documento_id );
alter table "edithor".abonado add constraint (foreign key (co_categoria)
references "edithor".mae_categoria );
alter table "edithor".abonado add constraint (foreign key (co_ddn)
references "edithor".mae_ddn );
alter table "edithor".abonado add constraint (foreign key (co_usuario)
references "edithor".usuario );
alter table "edithor".abonado add constraint (foreign key (co_iva)
references "edithor".mae_iva );
alter table "edithor".abonado add constraint (foreign key (co_localidad)
references "edithor".mae_localidad );
create trigger "edithor".tu_abonado_activo update of mr_activo
on "edithor".abonado referencing old as anterior new as nuevo
for each row
when ((nuevo.mr_activo = '0' ) )
(
execute procedure "edithor".sp_elimina_te_sucursal(
'00000000' ,anterior.nu_inscripcion ,USER ));
create trigger "edithor".tu_abonado_publicar update of mr_publicar
on "edithor".abonado referencing old as anterior new as nuevo
for each row
when ((nuevo.mr_publicar = '0' ) )
(
execute procedure "edithor".sp_elimina_te_sucursal(
'00000000' ,anterior.nu_inscripcion ,USER ));
Table am_par_categorias
create table "edithor".am_par_categorias
(
co_categoria char(1) not null ,
mr_no_cli_tasa char(1)
default '0',
co_origen_cuenta char(2),
mr_procesar char(1)
default '0',
fe_cambio datetime year to second,
co_usuario char(10),
primary key (co_categoria) constraint "edithor".pk_am_par_cate
);
revoke all on "edithor".am_par_categorias from "public";
create index "informix".ix_leox6 on "edithor".am_par_categorias
(mr_procesar) using btree ;
alter table "edithor".am_par_categorias add constraint (foreign
key (co_origen_cuenta) references "edithor".mae_origen_cuenta
constraint "informix".fk_ori_cta);
Table: localidad rango
create table "edithor".localidad_rango
(
co_localidad char(7) not null ,
co_ddn varchar(5) not null ,
nu_prefijo char(5) not null ,
nu_sufijo_desde char(5) not null ,
nu_sufijo_hasta char(5) not null ,
ti_servicio char(2) not null ,
ti_empresa char(2) not null ,
co_formato char(4) not null ,
co_mercado char(3),
co_usuario char(10),
fe_alta datetime year to second,
fe_cambio datetime year to second,
Keeping the Estimated cost aside for a while, did you have any other better
plan by forcing certain "optimizer directives" for the same query?
That is, do you have any other execution plan-B that executes much faster
than the one you listed here? What is the difference in execution time?
Srini
"LEONARDO
SANTAGOSTINI"
<lsantagostini@gm To
ail.com> ids@iiug.org
Sent by: cc
ids-bounces@iiug.
org Subject
SQL Tunning [20105]
12/05/2010 19:12
Please respond to
ids@iiug.org
Hello all again
Im bothering you all just because im facing same issues while trying to do
sql
tunning.
Thjis is the query:
SELECT
edithor.localidad_rango.co_mercado,
edithor.mae_mercado.no_mercado,
count(edithor.abonado.nu_inscripcion )
FROM
edithor.abonado,
edithor.am_par_categorias,
edithor.localidad_rango,
edithor.mae_mercado
WHERE
( edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria )
and
( edithor.am_par_categorias.mr_procesar = '1' ) and
( edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad ) and
( edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) and
( edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) and
( edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde) and
( edithor.abonado.nu_sufijo <= edithor.localidad_rango.nu_sufijo_hasta) and
( edithor.localidad_rango.co_mercado in
('259','260','261','262','263','264'))
and
( edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado )
group by 1,2
and
( edithor.abonado.nu_inscripcion not in (
select
g.nu_inscripcion
from
agrupado g
where
g.nu_inscripcion = edithor.abonado.nu_inscripcion) ) and
( edithor.abonado.nu_inscripcion not in (
select
b.nu_inscripcion
from
am_bolsa b
where
b.nu_inscripcion = edithor.abonado.nu_inscripcion) )
group by 1,2
Here are the dbschema of the tables:
Table: abonado
create table "edithor".abonado
(
nu_inscripcion char(8) not null ,
co_ddn varchar(5) not null ,
nu_telefono char(10) not null ,
nu_telefono_letras varchar(20),
nu_prefijo char(5),
nu_sufijo char(5),
nu_cpp char(4),
ti_abonado char(2),
co_categoria char(1),
ti_servicio char(2),
no_figuracion varchar(50),
ap_figuracion varchar(80),
no_calle varchar(30),
nu_casa varchar(5),
nu_piso varchar(3),
nu_depart varchar(4),
ac_direcc varchar(60),
co_localidad char(7) not null ,
co_iva char(2),
co_postal char(4),
cpa varchar(9),
nu_cuit char(11),
mr_publicar char(1),
ti_documento_id char(4),
nu_documento_id varchar(15),
mr_activo char(1),
mr_pbx char(1),
co_ddn_principal varchar(5),
nu_tel_principal char(10),
co_actividad char(6),
co_cli_telefonica char(12),
nu_ult_ooss integer,
fe_alta datetime year to second,
fe_cambio datetime year to second,
co_usuario char(10),
co_cliente_teco char(12),
nu_inscripcion_pub char(8),
co_categoria_teco char(2),
primary key (nu_inscripcion)
);
revoke all on "edithor".abonado from "public";
create index "edithor".fe_alta on "edithor".abonado (fe_alta)
using btree ;
create index "edithor".ix405_23 on "edithor".abonado (mr_publicar)
using btree ;
create unique index "edithor".ix_abonado1 on "edithor".abonado
(co_ddn,nu_telefono) using btree ;
create index "edithor".ix_abonado2 on "edithor".abonado (ap_figuracion)
using btree ;
create index "edithor".ix_abonado3 on "edithor".abonado (no_calle)
using btree ;
create index "edithor".ix_abonado4 on "edithor".abonado (nu_ult_ooss)
using btree ;
create index "edithor".ix_cli_tasa on "edithor".abonado (co_cli_telefonica)
using btree ;
create index "edithor".ix_cuit on "edithor".abonado (nu_cuit)
using btree ;
create index "informix".ix_leox4 on "edithor".abonado (nu_sufijo)
using btree ;
create index "edithor".xabo_clipub on "edithor".abonado (co_cliente_teco)
using btree ;
create index "edithor".xie1abonado on "edithor".abonado (co_ddn,
nu_prefijo,co_localidad) using btree ;
create index "edithor".xie2abonado on "edithor".abonado (co_localidad,
co_postal) using btree ;
alter table "edithor".abonado add constraint (foreign key (ti_documento_id)
references "edithor".mae_documento_id );
alter table "edithor".abonado add constraint (foreign key (co_categoria)
references "edithor".mae_categoria );
alter table "edithor".abonado add constraint (foreign key (co_ddn)
references "edithor".mae_ddn );
alter table "edithor".abonado add constraint (foreign key (co_usuario)
references "edithor".usuario );
alter table "edithor".abonado add constraint (foreign key (co_iva)
references "edithor".mae_iva );
alter table "edithor".abonado add constraint (foreign key (co_localidad)
references "edithor".mae_localidad );
create trigger "edithor".tu_abonado_activo update of mr_activo
on "edithor".abonado referencing old as anterior new as nuevo
for each row
when ((nuevo.mr_activo = '0' ) )
(
execute procedure "edithor".sp_elimina_te_sucursal(
'00000000' ,anterior.nu_inscripcion ,USER ));
create trigger "edithor".tu_abonado_publicar update of mr_publicar
on "edithor".abonado referencing old as anterior new as nuevo
for each row
when ((nuevo.mr_publicar = '0' ) )
(
execute procedure "edithor".sp_elimina_te_sucursal(
'00000000' ,anterior.nu_inscripcion ,USER ));
Table am_par_categorias
create table "edithor".am_par_categorias
(
co_categoria char(1) not null ,
mr_no_cli_tasa char(1)
default '0',
co_origen_cuenta char(2),
mr_procesar char(1)
default '0',
fe_cambio datetime year to second,
co_usuario char(10),
primary key (co_categoria) constraint "edithor".pk_am_par_cate
);
revoke all on "edithor".am_par_categorias from "public";
create index "informix".ix_leox6 on "edithor".am_par_categorias
(mr_procesar) using btree ;
alter table "edithor".am_par_categorias add constraint (foreign
key (co_origen_cuenta) references "edithor@@D
Is the estimated number of rows (1) accurate? Are the data distributions on
these tables up-to-date? Is there an index on the
edithor.localidad_rango.co_mercado column?
You can also try to move this to ANSI '92 SQL syntax and replace the NOT IN
correlated sub-queries with OUTER JOINS filtering for NULLs in the dependent
table's key column(s). Thos correlated sub-queries are killing you worse
than anything else. At a client so I can't take the time to rewrite this
for you.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, May 12, 2010 at 9:42 AM, LEONARDO SANTAGOSTINI <
lsantagostini@gmail.com> wrote:
> Hello all again
>
> Im bothering you all just because im facing same issues while trying to do
> sql
> tunning.
>
> Thjis is the query:
>
> SELECT
>
> edithor.localidad_rango.co_mercado,
>
> edithor.mae_mercado.no_mercado,
>
> count(edithor.abonado.nu_inscripcion )
> FROM
>
> edithor.abonado,
>
> edithor.am_par_categorias,
>
> edithor.localidad_rango,
>
> edithor.mae_mercado
> WHERE
>
> ( edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria )
> and
>
> ( edithor.am_par_categorias.mr_procesar = '1' ) and
>
> ( edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad ) and
>
> ( edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) and
>
> ( edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) and
>
> ( edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde) and
>
> ( edithor.abonado.nu_sufijo <= edithor.localidad_rango.nu_sufijo_hasta) and
>
> ( edithor.localidad_rango.co_mercado in
> ('259','260','261','262','263','264'))
> and
>
> ( edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado )
> group by 1,2
>
> and
>
> ( edithor.abonado.nu_inscripcion not in (
>
> select
>
> g.nu_inscripcion
>
> from
>
> agrupado g
>
> where
>
> g.nu_inscripcion = edithor.abonado.nu_inscripcion) ) and
>
> ( edithor.abonado.nu_inscripcion not in (
>
> select
>
> b.nu_inscripcion
>
> from
>
> am_bolsa b
>
> where
>
> b.nu_inscripcion = edithor.abonado.nu_inscripcion) )
> group by 1,2
>
> Here are the dbschema of the tables:
>
> Table: abonado
> create table "edithor".abonado
> (
>
> nu_inscripcion char(8) not null ,
>
> co_ddn varchar(5) not null ,
>
> nu_telefono char(10) not null ,
>
> nu_telefono_letras varchar(20),
>
> nu_prefijo char(5),
>
> nu_sufijo char(5),
>
> nu_cpp char(4),
>
> ti_abonado char(2),
>
> co_categoria char(1),
>
> ti_servicio char(2),
>
> no_figuracion varchar(50),
>
> ap_figuracion varchar(80),
>
> no_calle varchar(30),
>
> nu_casa varchar(5),
>
> nu_piso varchar(3),
>
> nu_depart varchar(4),
>
> ac_direcc varchar(60),
>
> co_localidad char(7) not null ,
>
> co_iva char(2),
>
> co_postal char(4),
>
> cpa varchar(9),
>
> nu_cuit char(11),
>
> mr_publicar char(1),
>
> ti_documento_id char(4),
>
> nu_documento_id varchar(15),
>
> mr_activo char(1),
>
> mr_pbx char(1),
>
> co_ddn_principal varchar(5),
>
> nu_tel_principal char(10),
>
> co_actividad char(6),
>
> co_cli_telefonica char(12),
>
> nu_ult_ooss integer,
>
> fe_alta datetime year to second,
>
> fe_cambio datetime year to second,
>
> co_usuario char(10),
>
> co_cliente_teco char(12),
>
> nu_inscripcion_pub char(8),
>
> co_categoria_teco char(2),
>
> primary key (nu_inscripcion)
> );
> revoke all on "edithor".abonado from "public";>
> create index "edithor".fe_alta on "edithor".abonado (fe_alta)
>
> using btree ;
> create index "edithor".ix405_23 on "edithor".abonado (mr_publicar)
>
> using btree ;
> create unique index "edithor".ix_abonado1 on "edithor".abonado
>
> (co_ddn,nu_telefono) using btree ;
> create index "edithor".ix_abonado2 on "edithor".abonado (ap_figuracion)
>
> using btree ;
> create index "edithor".ix_abonado3 on "edithor".abonado (no_calle)
>
> using btree ;
> create index "edithor".ix_abonado4 on "edithor".abonado (nu_ult_ooss)
>
> using btree ;
> create index "edithor".ix_cli_tasa on "edithor".abonado (co_cli_telefonica)
>
> using btree ;
> create index "edithor".ix_cuit on "edithor".abonado (nu_cuit)
>
> using btree ;
> create index "informix".ix_leox4 on "edithor".abonado (nu_sufijo)
>
> using btree ;
> create index "edithor".xabo_clipub on "edithor".abonado (co_cliente_teco)
>
> using btree ;
> create index "edithor".xie1abonado on "edithor".abonado (co_ddn,
>
> nu_prefijo,co_localidad) using btree ;
> create index "edithor".xie2abonado on "edithor".abonado (co_localidad,
>
> co_postal) using btree ;
> alter table "edithor".abonado add constraint (foreign key (ti_documento_id)
>
> references "edithor".mae_documento_id );
>
> alter table "edithor".abonado add constraint (foreign key (co_categoria)
>
> references "edithor".mae_categoria );
>
> alter table "edithor".abonado add constraint (foreign key (co_ddn)
>
> references "edithor".mae_ddn );
>
> alter table "edithor".abonado add constraint (foreign key (co_usuario)
>
> references "edithor".usuario );
>
> alter table "edithor".abonado add constraint (foreign key (co_iva)
>
> references "edithor".mae_iva );
>
> alter table "edithor".abonado add constraint (foreign key (co_localidad)
>
> references "edithor".mae_localidad );
>
> create trigger "edithor".tu_abonado_activo update of mr_activo
>
> on "edithor".abonado referencing old as anterior new as nuevo
>
> for each row
>
> when ((nuevo.mr_activo = '0' ) )
>
> (
>
> execute procedure "edithor".sp_elimina_te_sucursal(
>
> '00000000' ,anterior.nu_inscripcion ,USER ));
>
> create trigger "edithor".tu_abonado_publicar update of mr_publicar
>
> on "edithor".abonado referencing old
Hello Art, Data distributions are ok, update statistics run every nigth at diferent levels on all tables. I will try rewritting this query for better perfomance using OUTER JOINS as you recommend. Later, i will post the results. Thanks for your response. Leonardo
Make sure to put all of the join conditions and other filters on INNER tables (but not the tests for NULL values on the OUTER table's keys) into the ON clauses not the WHERE clause. In ANSI '92 syntax the standard requires that WHERE clause filters be applied post-join so all rows are selected and written to a temp table then the WHERE clause filters are applied to the temp table to return the final rows. ON clause filters are applied pre-join just like in the older informix syntax. The tests for NULLs on the tables that are now sub-queries must go into the WHERE clause since they can't be applied until after the joins are completed. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 12, 2010 at 10:38 AM, LEONARDO SANTAGOSTINI < lsantagostini@gmail.com> wrote: > Hello Art, > > Data distributions are ok, update statistics run every nigth at diferent > levels on all tables. > I will try rewritting this query for better perfomance using OUTER JOINS as > you recommend. > > Later, i will post the results. > Thanks for your response. > Leonardo > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502cc2b69b628048666ac87
Well, i make this query using outer join, but im not familiar with outer join. So, results were not good. Here the output. QUERY: ------ SELECT edithor.localidad_rango.co_mercado, edithor.mae_mercado.no_mercado, count(edithor.abonado.nu_inscripcion ) FROM edithor.abonado, edithor.am_par_categorias, edithor.localidad_rango, edithor.mae_mercado, (abonado ab left outer join agrupado ag on ab.nu_inscripcion = ag.nu_inscripcion), (abonado abo left outer join am_bolsa am on abo.nu_inscripcion = am.nu_inscripcion) WHERE ( edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria ) and ( edithor.am_par_categorias.mr_procesar = '1' ) and ( edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad ) and ( edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) and ( edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) and ( edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde) and ( edithor.abonado.nu_sufijo <= edithor.localidad_rango.nu_sufijo_hasta) and ( edithor.localidad_rango.co_mercado in ('259','260','261','262','263','264')) and ( edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado ) -- ( edithor.abonado.nu_inscripcion not in (select g.nu_inscripcion from agrupado g where g.nu_inscripcion = edithor.abonado.nu_inscripcion) ) and -- ( edithor.abonado.nu_inscripcion not in (select b.nu_inscripcion from am_bolsa b where b.nu_inscripcion = edithor.abonado.nu_inscripcion) ) group by 1,2 Estimated Cost: 2147483647 Estimated # of Rows Returned: 3486 Temporary Files Required For: Group By 1) edithor.mae_mercado: SEQUENTIAL SCAN 2) edithor.localidad_rango: INDEX PATH (1) Index Keys: co_mercado (Key-First) (Serial, fragments: ALL) Lower Index Filter: edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado Index Key Filters: (edithor.localidad_rango.co_mercado IN ('259' , '260' , '261' , '262' , '263' , '264' )) NESTED LOOP JOIN 3) edithor.abonado: INDEX PATH (1) Index Keys: co_ddn nu_prefijo co_localidad (Serial, fragments: ALL) Lower Index Filter: ((edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad AND edithor.abonado.co_ddn = edithor.localid ad_rango.co_ddn ) AND edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) NESTED LOOP JOIN 4) edithor.am_par_categorias: INDEX PATH Filters: edithor.am_par_categorias.mr_procesar = '1' (1) Index Keys: co_categoria (Serial, fragments: ALL) Lower Index Filter: edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria NESTED LOOP JOIN 5) informix.abo: INDEX PATH (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) 6) informix.am: INDEX PATH (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.abo.nu_inscripcion = informix.am.nu_inscripcion ON-Filters:informix.abo.nu_inscripcion = informix.am.nu_inscripcion NESTED LOOP JOIN(LEFT OUTER JOIN) NESTED LOOP JOIN 7) informix.ab: INDEX PATH (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) 8) informix.ag: INDEX PATH (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.ab.nu_inscripcion = informix.ag.nu_inscripcion ON-Filters:informix.ab.nu_inscripcion = informix.ag.nu_inscripcion NESTED LOOP JOIN(LEFT OUTER JOIN) NESTED LOOP JOIN PostJoin-Filters:((((((((edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria AND edithor.am_par_categorias.mr_procesar = '1' ) AND edithor.a bonado.co_localidad = edithor.localidad_rango.co_localidad ) AND edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) AND edithor.abonado.nu_prefijo = ed ithor.localidad_rango.nu_prefijo ) AND edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde ) AND edithor.abonado.nu_sufijo <= edithor.localid ad_rango.nu_sufijo_hasta ) AND edithor.localidad_rango.co_mercado IN ('259' , '260' , '261' , '262' , '263' , '264' )) AND edithor.mae_mercado.co_mercado = ed ithor.localidad_rango.co_mercado ) So, is, in somewhere a reading like "Teach yourself outer joins" ? By the way, if anyone can help me i will really apreciatte Kind regards, Leonardo
Try this one: SELECT edithor.localidad_rango.co_mercado, edithor.mae_mercado.no_mercado, count(edithor.abonado.nu_inscripcion ) FROM edithor.abonado JOIN edithor.am_par_categorias ON edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria AND edithor.am_par_categorias.mr_procesar = '1' JOIN edithor.localidad_rango ON edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad AND edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn AND edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo AND edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde AND edithor.abonado.nu_sufijo <= edithor.localidad_rango.nu_sufijo_hasta AND edithor.localidad_rango.co_mercado IN ('259','260','261','262','263','264') JOIN edithor.mae_mercado ON edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado LEFT OUTER JOIN agrupado ag ON edithor.abonado.nu_inscripcion = ag.nu_inscripcion LEFT OUTER JOIN am_bolsa am ON edithor.abonado.nu_inscripcion = am.nu_inscripcion WHERE ag.nu_inscripcion IS NULL AND am.nu_inscripcion IS NULL group by 1,2; Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 12, 2010 at 1:03 PM, LEONARDO SANTAGOSTINI < lsantagostini@gmail.com> wrote: > Well, i make this query using outer join, but im not familiar with outer > join. > So, results were not good. > > Here the output. > > QUERY: > ------ > SELECT > > edithor.localidad_rango.co_mercado, > > edithor.mae_mercado.no_mercado, > > count(edithor.abonado.nu_inscripcion ) > FROM > > edithor.abonado, > > edithor.am_par_categorias, > > edithor.localidad_rango, > > edithor.mae_mercado, > > (abonado ab left outer join agrupado ag on ab.nu_inscripcion = > ag.nu_inscripcion), > > (abonado abo left outer join am_bolsa am on abo.nu_inscripcion = > am.nu_inscripcion) > WHERE > ( edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria ) > and > ( edithor.am_par_categorias.mr_procesar = '1' ) and > ( edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad ) and > ( edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) and > ( edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) and > ( edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde) and > ( edithor.abonado.nu_sufijo <= edithor.localidad_rango.nu_sufijo_hasta) and > ( edithor.localidad_rango.co_mercado in > ('259','260','261','262','263','264')) > and > ( edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado ) > -- ( edithor.abonado.nu_inscripcion not in (select g.nu_inscripcion from > agrupado g where g.nu_inscripcion = edithor.abonado.nu_inscripcion) ) and > -- ( edithor.abonado.nu_inscripcion not in (select b.nu_inscripcion from > am_bolsa b where b.nu_inscripcion = edithor.abonado.nu_inscripcion) ) > group by 1,2 > > Estimated Cost: 2147483647 > Estimated # of Rows Returned: 3486 > Temporary Files Required For: Group By > > 1) edithor.mae_mercado: SEQUENTIAL SCAN > > 2) edithor.localidad_rango: INDEX PATH > > (1) Index Keys: co_mercado (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: edithor.mae_mercado.co_mercado = > edithor.localidad_rango.co_mercado > > Index Key Filters: (edithor.localidad_rango.co_mercado IN ('259' , '260' , > '261' , '262' , '263' , '264' )) > > NESTED LOOP JOIN > > 3) edithor.abonado: INDEX PATH > > (1) Index Keys: co_ddn nu_prefijo co_localidad (Serial, fragments: ALL) > > Lower Index Filter: ((edithor.abonado.co_localidad = > edithor.localidad_rango.co_localidad AND edithor.abonado.co_ddn = > edithor.localid > ad_rango.co_ddn ) AND edithor.abonado.nu_prefijo = > edithor.localidad_rango.nu_prefijo ) > > NESTED LOOP JOIN > > 4) edithor.am_par_categorias: INDEX PATH > > Filters: edithor.am_par_categorias.mr_procesar = '1' > > (1) Index Keys: co_categoria (Serial, fragments: ALL) > > Lower Index Filter: edithor.abonado.co_categoria = > edithor.am_par_categorias.co_categoria > > NESTED LOOP JOIN > > 5) informix.abo: INDEX PATH > > (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) > > 6) informix.am: INDEX PATH > > (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.abo.nu_inscripcion = > informix.am.nu_inscripcion > > ON-Filters:informix.abo.nu_inscripcion = informix.am.nu_inscripcion > > NESTED LOOP JOIN(LEFT OUTER JOIN) > > NESTED LOOP JOIN > > 7) informix.ab: INDEX PATH > > (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) > > 8) informix.ag: INDEX PATH > > (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.ab.nu_inscripcion = informix.ag.nu_inscripcion > > ON-Filters:informix.ab.nu_inscripcion = informix.ag.nu_inscripcion > > NESTED LOOP JOIN(LEFT OUTER JOIN) > > NESTED LOOP JOIN > > PostJoin-Filters:((((((((edithor.abonado.co_categoria = > edithor.am_par_categorias.co_categoria AND > edithor.am_par_categorias.mr_procesar = '1' ) AND edithor.a > bonado.co_localidad = edithor.localidad_rango.co_localidad ) AND > edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) AND > edithor.abonado.nu_prefijo = ed > ithor.localidad_rango.nu_prefijo ) AND edithor.abonado.nu_sufijo >= > edithor.localidad_rango.nu_sufijo_desde ) AND edithor.abonado.nu_sufijo <= > edithor.localid > ad_rango.nu_sufijo_hasta ) AND edithor.localidad_rango.co_mercado IN ('259' > , > '260' , '261' , '262' , '263' , '264' )) AND edithor.mae_mercado.co_mercado > = > ed > ithor.localidad_rango.co_mercado ) > > So, is, in somewhere a reading like "Teach yourself outer joins" ? > > By the way, if anyone can help me i will really apreciatte > > Kind regards, > Leonardo > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502af96955de40486695609
Leo, I think you have miss and index in mae_mercado.co_mercado, maybe i'm wrong or you should should force an index scan on that column . Saludos Fito P.s: saludos desde Asuncion!!!! > Well, i make this query using outer join, but im not familiar with outer > join. > So, results were not good. > > Here the output. > > QUERY: > ------ > SELECT > > edithor.localidad_rango.co_mercado, > > edithor.mae_mercado.no_mercado, > > count(edithor.abonado.nu_inscripcion ) > FROM > > edithor.abonado, > > edithor.am_par_categorias, > > edithor.localidad_rango, > > edithor.mae_mercado, > > (abonado ab left outer join agrupado ag on ab.nu_inscripcion = > ag.nu_inscripcion), > > (abonado abo left outer join am_bolsa am on abo.nu_inscripcion = > am.nu_inscripcion) > WHERE > ( edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria ) > and > ( edithor.am_par_categorias.mr_procesar = '1' ) and > ( edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad ) > and > ( edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) and > ( edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) and > ( edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde) > and > ( edithor.abonado.nu_sufijo <= edithor.localidad_rango.nu_sufijo_hasta) > and > ( edithor.localidad_rango.co_mercado in > ('259','260','261','262','263','264')) > and > ( edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado ) > -- ( edithor.abonado.nu_inscripcion not in (select g.nu_inscripcion from > agrupado g where g.nu_inscripcion = edithor.abonado.nu_inscripcion) ) and > -- ( edithor.abonado.nu_inscripcion not in (select b.nu_inscripcion from > am_bolsa b where b.nu_inscripcion = edithor.abonado.nu_inscripcion) ) > group by 1,2 > > Estimated Cost: 2147483647 > Estimated # of Rows Returned: 3486 > Temporary Files Required For: Group By > > 1) edithor.mae_mercado: SEQUENTIAL SCAN > > 2) edithor.localidad_rango: INDEX PATH > > (1) Index Keys: co_mercado (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: edithor.mae_mercado.co_mercado = > edithor.localidad_rango.co_mercado > > Index Key Filters: (edithor.localidad_rango.co_mercado IN ('259' , '260' , > '261' , '262' , '263' , '264' )) > > NESTED LOOP JOIN > > 3) edithor.abonado: INDEX PATH > > (1) Index Keys: co_ddn nu_prefijo co_localidad (Serial, fragments: ALL) > > Lower Index Filter: ((edithor.abonado.co_localidad = > edithor.localidad_rango.co_localidad AND edithor.abonado.co_ddn = > edithor.localid > ad_rango.co_ddn ) AND edithor.abonado.nu_prefijo = > edithor.localidad_rango.nu_prefijo ) > > NESTED LOOP JOIN > > 4) edithor.am_par_categorias: INDEX PATH > > Filters: edithor.am_par_categorias.mr_procesar = '1' > > (1) Index Keys: co_categoria (Serial, fragments: ALL) > > Lower Index Filter: edithor.abonado.co_categoria = > edithor.am_par_categorias.co_categoria > > NESTED LOOP JOIN > > 5) informix.abo: INDEX PATH > > (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) > > 6) informix.am: INDEX PATH > > (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.abo.nu_inscripcion = > informix.am.nu_inscripcion > > ON-Filters:informix.abo.nu_inscripcion = informix.am.nu_inscripcion > > NESTED LOOP JOIN(LEFT OUTER JOIN) > > NESTED LOOP JOIN > > 7) informix.ab: INDEX PATH > > (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) > > 8) informix.ag: INDEX PATH > > (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.ab.nu_inscripcion = > informix.ag.nu_inscripcion > > ON-Filters:informix.ab.nu_inscripcion = informix.ag.nu_inscripcion > > NESTED LOOP JOIN(LEFT OUTER JOIN) > > NESTED LOOP JOIN > > PostJoin-Filters:((((((((edithor.abonado.co_categoria = > edithor.am_par_categorias.co_categoria AND > edithor.am_par_categorias.mr_procesar = '1' ) AND edithor.a > bonado.co_localidad = edithor.localidad_rango.co_localidad ) AND > edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn ) AND > edithor.abonado.nu_prefijo = ed > ithor.localidad_rango.nu_prefijo ) AND edithor.abonado.nu_sufijo >= > edithor.localidad_rango.nu_sufijo_desde ) AND edithor.abonado.nu_sufijo <= > edithor.localid > ad_rango.nu_sufijo_hasta ) AND edithor.localidad_rango.co_mercado IN > ('259' , > '260' , '261' , '262' , '263' , '264' )) AND > edithor.mae_mercado.co_mercado = > ed > ithor.localidad_rango.co_mercado ) > > So, is, in somewhere a reading like "Teach yourself outer joins" ? > > By the way, if anyone can help me i will really apreciatte > > Kind regards, > Leonardo > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > -- > Este mensaje ha sido analizado por MailScanner > en busca de virus y otros contenidos peligrosos, > y se considera que est? limpio. > MailScanner agradece a transtec Computers por su apoyo. > > > -- Este mensaje ha sido analizado por MailScanner en busca de virus y otros contenidos peligrosos, y se considera que est limpio. MailScanner agradece a transtec Computers por su apoyo.
Hello Art ! Well here is the output from the explain. group by 1,2 Estimated Cost: 2914668 Estimated # of Rows Returned: 1 Temporary Files Required For: Group By 1) edithor.am_par_categorias: INDEX PATH (1) Index Keys: mr_procesar (Serial, fragments: ALL) Lower Index Filter: edithor.am_par_categorias.mr_procesar = '1' 2) edithor.abonado: INDEX PATH (1) Index Keys: co_categoria (Serial, fragments: ALL) Lower Index Filter: edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria ON-Filters:(edithor.abonado.co_categoria = edithor.am_par_categorias.co_categoria AND edithor.am_par_categorias.mr_procesar = '1' ) NESTED LOOP JOIN 3) edithor.localidad_rango: INDEX PATH Filters: edithor.localidad_rango.co_mercado IN ('259' , '260' , '261' , '262' , '263' , '264' ) (1) Index Keys: co_localidad co_ddn nu_prefijo nu_sufijo_desde nu_sufijo_hasta (Serial, fragments: ALL) Lower Index Filter: ((edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad AND edithor.abonado.co_ddn = edithor.localid ad_rango.co_ddn ) AND edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) Upper Index Filter: edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde ON-Filters:(((((edithor.abonado.co_localidad = edithor.localidad_rango.co_localidad AND edithor.abonado.co_ddn = edithor.localidad_rango.co_dd n ) AND edithor.abonado.nu_prefijo = edithor.localidad_rango.nu_prefijo ) AND edithor.abonado.nu_sufijo >= edithor.localidad_rango.nu_sufijo_desde ) AND edith or.abonado.nu_sufijo <= edithor.localidad_rango.nu_sufijo_hasta ) AND edithor.localidad_rango.co_mercado IN ('259' , '260' , '261' , '262' , '263' , '264' )) NESTED LOOP JOIN 4) edithor.mae_mercado: INDEX PATH (1) Index Keys: co_mercado (Serial, fragments: ALL) Lower Index Filter: edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado ON-Filters:edithor.mae_mercado.co_mercado = edithor.localidad_rango.co_mercado NESTED LOOP JOIN 5) informix.ag: INDEX PATH (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) Lower Index Filter: edithor.abonado.nu_inscripcion = informix.ag.nu_inscripcion ON-Filters:edithor.abonado.nu_inscripcion = informix.ag.nu_inscripcion NESTED LOOP JOIN(LEFT OUTER JOIN) 6) informix.am: INDEX PATH (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) Lower Index Filter: edithor.abonado.nu_inscripcion = informix.am.nu_inscripcion ON-Filters:edithor.abonado.nu_inscripcion = informix.am.nu_inscripcion NESTED LOOP JOIN(LEFT OUTER JOIN) PostJoin-Filters:informix.ag.nu_inscripcion IS NULL
Hi Rodolfo !!! How are you doing ? Its good to read you ! Well the point is i have index created on this tables and also i have statistics up to date (in fact statistics runs every night) Kind regards, Leonardo
OK. Does this one run any faster? Slower? What? Don't remember the orig= inal cost. Is this cost better? Art=20 -----Original Message----- From: LEONARDO SANTAGOSTINI <lsantagostini@gmail.com> Sent: Wednesday, May 12, 2010 2:33 PM To: ids@iiug.org Subject: Re: SQL Tunning [20114] Hello Art !=20 Well here is the output from the explain.=20 group by 1,2=20 Estimated Cost: 2914668=20 Estimated # of Rows Returned: 1=20 Temporary Files Required For: Group By=20 1) edithor.am_par_categorias: INDEX PATH=20 (1) Index Keys: mr_procesar (Serial, fragments: ALL)=20 Lower Index Filter: edithor.am_par_categorias.mr_procesar =3D '1'=20 2) edithor.abonado: INDEX PATH=20 (1) Index Keys: co_categoria (Serial, fragments: ALL)=20 Lower Index Filter: edithor.abonado.co_categoria =3D=20 edithor.am_par_categorias.co_categoria=20 ON-Filters:(edithor.abonado.co_categoria =3D=20 edithor.am_par_categorias.co_categoria AND=20 edithor.am_par_categorias.mr_procesar =3D '1' )=20 NESTED LOOP JOIN=20 3) edithor.localidad_rango: INDEX PATH=20 Filters: edithor.localidad_rango.co_mercado IN ('259' , '260' , '261' , '26= 2'=20 , '263' , '264' )=20 (1) Index Keys: co_localidad co_ddn nu_prefijo nu_sufijo_desde nu_sufijo_ha= sta=20 (Serial, fragments: ALL)=20 Lower Index Filter: ((edithor.abonado.co_localidad =3D=20 edithor.localidad_rango.co_localidad AND edithor.abonado.co_ddn =3D=20 edithor.localid=20 ad_rango.co_ddn ) AND edithor.abonado.nu_prefijo =3D=20 edithor.localidad_rango.nu_prefijo )=20 Upper Index Filter: edithor.abonado.nu_sufijo >=3D=20 edithor.localidad_rango.nu_sufijo_desde=20 ON-Filters:(((((edithor.abonado.co_localidad =3D=20 edithor.localidad_rango.co_localidad AND edithor.abonado.co_ddn =3D=20 edithor.localidad_rango.co_dd=20 n ) AND edithor.abonado.nu_prefijo =3D edithor.localidad_rango.nu_prefijo )= AND=20 edithor.abonado.nu_sufijo >=3D edithor.localidad_rango.nu_sufijo_desde ) AN= D=20 edith=20 or.abonado.nu_sufijo <=3D edithor.localidad_rango.nu_sufijo_hasta ) AND=20 edithor.localidad_rango.co_mercado IN ('259' , '260' , '261' , '262' , '263= ' ,=20 '264' ))=20 NESTED LOOP JOIN=20 4) edithor.mae_mercado: INDEX PATH=20 (1) Index Keys: co_mercado (Serial, fragments: ALL)=20 Lower Index Filter: edithor.mae_mercado.co_mercado =3D=20 edithor.localidad_rango.co_mercado=20 ON-Filters:edithor.mae_mercado.co_mercado =3D edithor.localidad_rango.co_me= rcado=20 NESTED LOOP JOIN=20 5) informix.ag: INDEX PATH=20 (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)=20 Lower Index Filter: edithor.abonado.nu_inscripcion =3D=20 informix.ag.nu_inscripcion=20 ON-Filters:edithor.abonado.nu_inscripcion =3D informix.ag.nu_inscripcion=20 NESTED LOOP JOIN(LEFT OUTER JOIN)=20 6) informix.am: INDEX PATH=20 (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)=20 Lower Index Filter: edithor.abonado.nu_inscripcion =3D=20 informix.am.nu_inscripcion=20 ON-Filters:edithor.abonado.nu_inscripcion =3D informix.am.nu_inscripcion=20 NESTED LOOP JOIN(LEFT OUTER JOIN)=20 PostJoin-Filters:informix.ag.nu_inscripcion IS NULL=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20
Hello Art, The query that you send me runs almost at same time, 30 minutes more or less. And the cost is: Original one:1358807 Last one: 2914668 Im trying to rewrite this query again. With better costs, but no luck for now. Thanks a lot, Yours, Leonardo
Well im still trying but without good result. I have tried this one, but it didnt work. SELECT lr.co_mercado, mm.no_mercado, count(ab.nu_inscripcion) FROM abonado ab, am_par_categorias amp, localidad_rango lr, mae_mercado mm, agrupado ag, am_bolsa am WHERE lr.co_mercado in ('259','260','261','262','263','264') and amp.mr_procesar = '1' and ab.co_categoria = amp.co_categoria and ab.nu_inscripcion <> ag.nu_inscripcion and ab.nu_inscripcion <> am.nu_inscripcion and ab.co_localidad = lr.co_localidad and ab.co_ddn = lr.co_ddn and ab.nu_prefijo = lr.nu_prefijo and ab.nu_sufijo >= lr.nu_sufijo_desde and ab.nu_sufijo <= lr.nu_sufijo_hasta and mm.co_mercado = lr.co_mercado group by 1,2 Estimated Cost: 2147483647 Estimated # of Rows Returned: 19 Temporary Files Required For: Group By 1) informix.mm: INDEX PATH (1) Index Keys: co_mercado (Serial, fragments: ALL) Lower Index Filter: informix.mm.co_mercado = '259' (2) Index Keys: co_mercado (Serial, fragments: ALL) Lower Index Filter: informix.mm.co_mercado = '260' (3) Index Keys: co_mercado (Serial, fragments: ALL) Lower Index Filter: informix.mm.co_mercado = '261' (4) Index Keys: co_mercado (Serial, fragments: ALL) Lower Index Filter: informix.mm.co_mercado = '262' (5) Index Keys: co_mercado (Serial, fragments: ALL) Lower Index Filter: informix.mm.co_mercado = '263' (6) Index Keys: co_mercado (Serial, fragments: ALL) Lower Index Filter: informix.mm.co_mercado = '264' 2) informix.lr: INDEX PATH (1) Index Keys: co_mercado (Serial, fragments: ALL) Lower Index Filter: informix.mm.co_mercado = informix.lr.co_mercado NESTED LOOP JOIN 3) informix.ab: INDEX PATH Filters: (informix.ab.nu_sufijo >= informix.lr.nu_sufijo_desde AND informix.ab.nu_sufijo <= informix.lr.nu_sufijo_hasta ) (1) Index Keys: co_ddn nu_prefijo co_localidad (Serial, fragments: ALL) Lower Index Filter: ((informix.ab.nu_prefijo = informix.lr.nu_prefijo AND informix.ab.co_localidad = informix.lr.co_localidad ) AND informix.ab.co_ddn = informix.lr.co_ddn ) NESTED LOOP JOIN 4) informix.amp: INDEX PATH Filters: informix.amp.mr_procesar = '1' (1) Index Keys: co_categoria (Serial, fragments: ALL) Lower Index Filter: informix.ab.co_categoria = informix.amp.co_categoria NESTED LOOP JOIN 5) informix.am: INDEX PATH Filters: informix.ab.nu_inscripcion != informix.am.nu_inscripcion (1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL) NESTED LOOP JOIN 6) informix.ag: INDEX PATH Filters: informix.ab.nu_inscripcion != informix.ag.nu_inscripcion (1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Serial, fragments: ALL) NESTED LOOP JOIN Does anybody have some clue ? Thank you very much, Leonardo
Hi all !!
Well i was doing some test with the subqueries, and i found that one of them
is very resource consumig and has a very high cost.
Take a look:
QUERY:
------
select distinct ag.nu_inscripcion from agrupado ag, abonado ab whereag.nu_inscripcion = ab.nu_inscripcion
Estimated Cost: 2288701
Estimated # of Rows Returned: 2002
1) informix.ag: INDEX PATH
(1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Serial, fragments: ALL)
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.ag.nu_inscripcion = informix.ab.nu_inscripcion
NESTED LOOP JOIN
So, im wondering about anything else than simple index or secuential scans.
Clues will be welcome.
Thanks all,
Leonardo
What's the estimated number of rows returned when you don't use
'distinct'. ?
=
From: "LEONARDO SANTAGOSTINI" <lsantagostini@gmail.com> =
=
To: ids@iiug.org =
=
Date: 05/12/2010 03:46 PM =
=
Subject: Re: RE: SQL Tunning [20119] =
=
Sent by: ids-bounces@iiug.org =
=
Hi all !!
Well i was doing some test with the subqueries, and i found that one of=
them
is very resource consumig and has a very high cost.
Take a look:
QUERY:
------
select distinct ag.nu_inscripcion from agrupado ag, abonado ab whereag.nu_inscripcion =3D ab.nu_inscripcion
Estimated Cost: 2288701
Estimated # of Rows Returned: 2002
1) informix.ag: INDEX PATH
(1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Serial, fragments:=
ALL)
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.ag.nu_inscripcion =3D informix.ab.nu_inscr=
ipcion
NESTED LOOP JOIN
So, im wondering about anything else than simple index or secuential sc=
ans.
Clues will be welcome.
Thanks all,
Leonardo
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
Hello Madison,
Thanks for answering.
Here is the data you asked me.
QUERY:
------
select ag.nu_inscripcion from agrupado ag, abonado ab where ag.nu_inscripcion= ab.nu_inscripcion
Estimated Cost: 2288701
Estimated # of Rows Returned: 2312068
1) informix.ag: INDEX PATH
(1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Serial, fragments: ALL)
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.ag.nu_inscripcion = informix.ab.nu_inscripcion
NESTED LOOP JOIN
QUERY:
------
select distinct ag.nu_inscripcion from agrupado ag, abonado ab whereag.nu_inscripcion = ab.nu_inscripcion
Estimated Cost: 2288701
Estimated # of Rows Returned: 2002
1) informix.ag: INDEX PATH
(1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Serial, fragments: ALL)
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.ag.nu_inscripcion = informix.ab.nu_inscripcion
NESTED LOOP JOIN
So now we know the reason for the high cost. ;-)
In order to perform the query, even though we only expect to return 200=
2
rows, it looks like we'll be
having to scan over 2 million.
M.P.
=
From: "LEONARDO SANTAGOSTINI" <lsantagostini@gmail.com> =
=
To: ids@iiug.org =
=
Date: 05/12/2010 03:56 PM =
=
Subject: Re: RE: SQL Tunning [20121] =
=
Sent by: ids-bounces@iiug.org =
=
Hello Madison,
Thanks for answering.
Here is the data you asked me.
QUERY:
------
select ag.nu_inscripcion from agrupado ag, abonado ab whereag.nu_inscripcion
=3D ab.nu_inscripcion
Estimated Cost: 2288701
Estimated # of Rows Returned: 2312068
1) informix.ag: INDEX PATH
(1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Serial, fragments:=
ALL)
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.ag.nu_inscripcion =3D informix.ab.nu_inscr=
ipcion
NESTED LOOP JOIN
QUERY:
------
select distinct ag.nu_inscripcion from agrupado ag, abonado ab whereag.nu_inscripcion =3D ab.nu_inscripcion
Estimated Cost: 2288701
Estimated # of Rows Returned: 2002
1) informix.ag: INDEX PATH
(1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Serial, fragments:=
ALL)
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.ag.nu_inscripcion =3D informix.ab.nu_inscr=
ipcion
NESTED LOOP JOIN
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
Yes, of course. Im thinking the way this query can be rewrited in order to not make informix be so busy with this query. Suggestions ? All will be welcome. Thanks All for your help. Leonardo
I'm thinking that the main problem may be that this query is thrashing the
buffer cache. Try this:
1. zero out the server stats - onstat -z
2. run the query
3. calculate the number of estimated buffer cache turnovers: (pagread +
bufwrits) / buffers
4. if this is much more than 1.0 you need more buffers to process this
kind of query efficiently
Also try setting OPTCOMPIND to 2 or using an optimizer directive to get the
optimizer to use a hash join for parts of the query. Try running it with
PDQPRIORITY > 1 also so the engine can use light scans.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, May 12, 2010 at 3:45 PM, LEONARDO SANTAGOSTINI <
lsantagostini@gmail.com> wrote:
> Hello Art,
>
> The query that you send me runs almost at same time, 30 minutes more or
> less.
>
> And the cost is:
>
> Original one:1358807
> Last one: 2914668
>
> Im trying to rewrite this query again. With better costs, but no luck for
> now.
>
> Thanks a lot,
> Yours,
> Leonardo
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0cd2e05c65aec204867b5bc4
Do the SELECT DISTINCT into a temp table and join to or sub-query that instead. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 12, 2010 at 5:13 PM, LEONARDO SANTAGOSTINI < lsantagostini@gmail.com> wrote: > Yes, of course. > > Im thinking the way this query can be rewrited in order to not make > informix > be so busy with this query. > > Suggestions ? > > All will be welcome. > Thanks All for your help. > Leonardo > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636e0b727499a2904867b6443
Hello Art.
Good news, take a look at my sqexplain
QUERY:
------
SELECT
lr.co_mercado,
mm.no_mercado,
count(ab.nu_inscripcion)
FROM
abonado ab,
am_par_categorias apc,
localidad_rango lr,
mae_mercado mm
WHERE
(ab.co_categoria = apc.co_categoria) and
(apc.mr_procesar = '1') and
(ab.co_localidad = lr.co_localidad) and
(ab.co_ddn = lr.co_ddn) and
(ab.nu_prefijo = lr.nu_prefijo) and
(ab.nu_sufijo >= lr.nu_sufijo_desde) and
(ab.nu_sufijo <= lr.nu_sufijo_hasta) and
(lr.co_mercado in ('259','260','261','262','263','264')) and
(mm.co_mercado = lr.co_mercado) and
(ab.nu_inscripcion not in (select g.nu_inscripcion from agrupado g where
g.nu_inscripcion = ab.nu_inscripcion)) and
(ab.nu_inscripcion not in (select b.nu_inscripcion from am_bolsa b where
b.nu_inscripcion = ab.nu_inscripcion))
group by 1,2
Estimated Cost: 833590
Estimated # of Rows Returned: 1
Maximum Threads: 0
Temporary Files Required For: Group By
1) informix.mm: INDEX PATH
(1) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '259'
(2) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '260'
(3) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '261'
(4) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '262'
(5) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '263'
(6) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '264'
2) informix.lr: INDEX PATH
(1) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = informix.lr.co_mercado
NESTED LOOP JOIN
3) informix.ab: INDEX PATH
Filters: (((informix.ab.nu_sufijo >= informix.lr.nu_sufijo_desde AND
informix.ab.nu_sufijo <= informix.lr.nu_sufijo_hasta ) AND
informix.ab.nu_inscripcion != ALL <subquery> ) AND informix.ab.nu_inscripcion
!= ALL <subquery> )
(1) Index Keys: co_ddn nu_prefijo co_localidad (Parallel, fragments: ALL)
Lower Index Filter: ((informix.ab.nu_prefijo = informix.lr.nu_prefijo AND
informix.ab.co_localidad = informix.lr.co_localidad ) AND informix.ab.co_ddn =
informix.lr.co_ddn )
NESTED LOOP JOIN
4) informix.apc: INDEX PATH
Filters: informix.apc.mr_procesar = '1'
(1) Index Keys: co_categoria (Parallel, fragments: ALL)
Lower Index Filter: informix.ab.co_categoria = informix.apc.co_categoria
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) informix.g: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Parallel, fragments: ALL)
Lower Index Filter: informix.g.nu_inscripcion = informix.ab.nu_inscripcion
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) informix.b: SEQUENTIAL SCAN
Filters: informix.b.nu_inscripcion = informix.ab.nu_inscripcion
Today: 833590
Parameters:
SET ENVIRONMENT OPTCOMPIND '2';
SET PDQPRIORITY 1;
From yesterdays mail:
Original one:1358807
Last one: 2914668
Yes ! In fact, i am talking with development team in order to do it. in the meanwhile ill try doing it manually, to see what happens. Thank you very much. Leonardo. PS: Later i will post the results.
Ok, Here the results:
SET ENVIRONMENT OPTCOMPIND '2';
SET PDQPRIORITY 1;
set explain on;
create temp table temp_agrupado
(
nu_inscripcion char(8)
) with no log;
create temp table temp_am_bolsa
(
nu_inscripcion char(8)
) with no log;
insert into temp_agrupado select distinct g.nu_inscripcion from agrupado g,abonado ab where g.nu_inscripcion = ab.nu_inscripcion ;
insert into temp_am_bolsa select distinct b.nu_inscripcion from am_bolsa b,abonado ab where b.nu_inscripcion = ab.nu_inscripcion ;
create index temp_ix_leo1 on temp_agrupado(nu_inscripcion);
create index temp_ix_leo2 on temp_am_bolsa(nu_inscripcion);
update statistics medium for table temp_agrupado;
update statistics medium for table temp_am_bolsa;
update statistics high for table temp_agrupado(nu_inscripcion);
update statistics high for table temp_am_bolsa(nu_inscripcion);
SELECT
lr.co_mercado,
mm.no_mercado,
count(ab.nu_inscripcion)
FROM
abonado ab,
am_par_categorias apc,
localidad_rango lr,
mae_mercado mm
WHERE
(ab.co_categoria = apc.co_categoria) and
(apc.mr_procesar = '1') and
(ab.co_localidad = lr.co_localidad) and
(ab.co_ddn = lr.co_ddn) and
(ab.nu_prefijo = lr.nu_prefijo) and
(ab.nu_sufijo >= lr.nu_sufijo_desde) and
(ab.nu_sufijo <= lr.nu_sufijo_hasta) and
(lr.co_mercado in ('259','260','261','262','263','264')) and
(mm.co_mercado = lr.co_mercado) and
ab.nu_inscripcion not in (select nu_inscripcion from temp_agrupado) and
ab.nu_inscripcion not in (select nu_inscripcion from temp_am_bolsa)
group by 1,2
Query still running.
I will check how long this query runs.
Thanks all,
Leonardo
Well Well Well.
Here the query and results:
at my env vars i have configured psortnprocs = 2
Query (from command line):
[edithordt: test (ontestcp)][/temporal/seguimiento/sesiones]
>export PSORTNPROCS=2
[edithordt: test (ontestcp)][/temporal/seguimiento/sesiones]
>time dbaccess edithort << EOM
> SET ENVIRONMENT OPTCOMPIND '2';
> SET PDQPRIORITY 1;
> set explain on;
> create temp table temp_agrupado
> (
> nu_inscripcion char(8)
> ) with no log;>
> create temp table temp_am_bolsa
> (
> nu_inscripcion char(8)
> ) with no log;>
> insert into temp_agrupado select distinct g.nu_inscripcion from agrupado g,abonado ab where g.nu_inscripcion = ab.nu_inscripcion ;
> insert into temp_am_bolsa select distinct b.nu_inscripcion from am_bolsa b,abonado ab where b.nu_inscripcion = ab.nu_inscripcion ;
>
> create index temp_ix_leo1 on temp_agrupado(nu_inscripcion);
> create index temp_ix_leo2 on temp_am_bolsa(nu_inscripcion);>
> update statistics medium for table temp_agrupado;
> update statistics medium for table temp_am_bolsa;>
> update statistics high for table temp_agrupado(nu_inscripcion);
> update statistics high for table temp_am_bolsa(nu_inscripcion);>
> SELECT
> lr.co_mercado,
> mm.no_mercado,
> count(ab.nu_inscripcion)
> FROM
abonado ab,
am_par_categorias apc,
localidad_rango lr,
mae_mercado mm
WHERE
(ab.co_categoria = apc.co_categoria) and
(apc.mr_procesar = '1') and
(ab.co_localidad = lr.co_localidad) and
(ab.co_ddn = lr.co_ddn) and
(ab.nu_prefijo = lr.nu_prefijo) and
(ab.nu_sufijo >= lr.nu_sufijo_desde) and
> (ab.nu_sufijo <= lr.nu_sufijo_hasta) and
abonado ab,
> am_par_categorias apc,
> localidad_rango lr,
> mae_mercado mm
> WHERE
> (ab.co_categoria = apc.co_categoria) and
> (apc.mr_procesar = '1') and
> (ab.co_localidad = lr.co_localidad) and
> (ab.co_ddn = lr.co_ddn) and
> (ab.nu_prefijo = lr.nu_prefijo) and
> (ab.nu_sufijo >= lr.nu_sufijo_desde) and
> (ab.nu_sufijo <= lr.nu_sufijo_hasta) and
> (lr.co_mercado in ('259','260','261','262','263','264')) and
> (mm.co_mercado = lr.co_mercado) and
> ab.nu_inscripcion not in (select nu_inscripcion from temp_agrupado) and
> ab.nu_inscripcion not in (select nu_inscripcion from temp_am_bolsa)
> group by 1,2
> EOM
Database selected.
Environment set.
PDQ Priority set.
Explain set.
Temporary table created.
Temporary table created.
1878798 row(s) inserted.
0 row(s) inserted.
Index created.
Index created.
Statistics updated.
Statistics updated.
Statistics updated.
Statistics updated.
co_mercado no_mercado (count)
259 GBA SUR 1
260 GBA OESTE 1
264 MENDOZA 4
261 BAHIA BLANCA 4
262 CORDOBA (TECO) 22
263 MAR DEL PLATA 3
6 row(s) retrieved.
Database closed.
real 33m49.63s
user 0m0.03s
sys 0m0.00s
So, i will execute in the same way, but without psortnprocs and OPTCOMPIND and
PDQPRIORITY
Later i will post the results and i will take note of the buffers.
Regards,
Leonardo
I apologize (maybe apologyze) for dont remember appending my statistics plan.
QUERY:
------
insert into temp_agrupado select distinct g.nu_inscripcion from agrupado g,abonado ab where g.nu_inscripcion = ab.nu_inscripcion
Estimated Cost: 2307483
Estimated # of Rows Returned: 2040
Maximum Threads: 1
1) informix.g: INDEX PATH
(1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Parallel, fragments: ALL)
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Parallel, fragments: ALL)
Lower Index Filter: informix.g.nu_inscripcion = informix.ab.nu_inscripcion
NESTED LOOP JOIN
QUERY:
------
insert into temp_am_bolsa select distinct b.nu_inscripcion from am_bolsa b,abonado ab where b.nu_inscripcion = ab.nu_inscripcion
Estimated Cost: 3
Estimated # of Rows Returned: 2
Maximum Threads: 1
1) informix.b: SEQUENTIAL SCAN
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Parallel, fragments: ALL)
Lower Index Filter: informix.b.nu_inscripcion = informix.ab.nu_inscripcion
NESTED LOOP JOIN
UPDATE STATISTICS:==================
Table: informix.temp_agrupado
Mode: MEDIUM
Number of Bins: 54 Bin size 74
Sort data 0.3 MB Sort memory granted 0.3 MB
Estimated number of table scans 1
PASS #1 nu_inscripcion
Scan 0 Sort 0 Build 0 Insert 0 Close 0 Total 0
Completed pass 1 in 0 minutes 0 seconds
UPDATE STATISTICS:==================
Table: informix.temp_agrupado
Mode: HIGH
Number of Bins: 267 Bin size 9393
Sort data 28.9 MB Sort memory granted 15.0 MB
Estimated number of table scans 1
PASS #1 nu_inscripcion
Light scans enabled
Scan 0 Sort 0 Build 6 Insert 0 Close 0 Total 6
Completed pass 1 in 0 minutes 6 seconds
QUERY:
------
SELECT
lr.co_mercado,
mm.no_mercado,
count(ab.nu_inscripcion)
FROM
abonado ab,
am_par_categorias apc,
localidad_rango lr,
mae_mercado mm
WHERE
(ab.co_categoria = apc.co_categoria) and
(apc.mr_procesar = '1') and
(ab.co_localidad = lr.co_localidad) and
(ab.co_ddn = lr.co_ddn) and
(ab.nu_prefijo = lr.nu_prefijo) and
(ab.nu_sufijo >= lr.nu_sufijo_desde) and
(ab.nu_sufijo <= lr.nu_sufijo_hasta) and
(lr.co_mercado in ('259','260','261','262','263','264')) and
(mm.co_mercado = lr.co_mercado) and
ab.nu_inscripcion not in (select nu_inscripcion from temp_agrupado) and
ab.nu_inscripcion not in (select nu_inscripcion from temp_am_bolsa)
group by 1,2
Estimated Cost: 306401
Estimated # of Rows Returned: 1
Maximum Threads: 1
Temporary Files Required For: Group By
1) informix.mm: INDEX PATH
(1) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '259'
(2) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '260'
(3) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '261'
(4) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '262'
(5) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '263'
(6) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '264'
2) informix.lr: INDEX PATH
(1) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = informix.lr.co_mercado
NESTED LOOP JOIN
3) informix.ab: INDEX PATH
Filters: (((informix.ab.nu_sufijo >= informix.lr.nu_sufijo_desde AND
informix.ab.nu_inscripcion != ALL <subquery> ) AN mix.lr.nu_sufijo_hasta ) AND
informix.ab.nu_inscripcion != ALL <subquery> )
(1) Index Keys: co_ddn nu_prefijo co_localidad (Parallel, fragments: ALL)
Lower Index Filter: ((informix.ab.nu_prefijo = informix.lr.nu_prefijo AND
informix.ab.co_localidad = informix.lr.co_lo = informix.lr.co_ddn )
NESTED LOOP JOIN
4) informix.apc: INDEX PATH
Filters: informix.apc.mr_procesar = '1'
(1) Index Keys: co_categoria (Parallel, fragments: ALL)
Lower Index Filter: informix.ab.co_categoria = informix.apc.co_categoria
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 63731
Estimated # of Rows Returned: 1878798
Maximum Threads: 1
1) informix.temp_agrupado: SEQUENTIAL SCAN
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) informix.temp_am_bolsa: SEQUENTIAL SCAN
Regards,
Leonardo
Leonardo,
What version of IDS are you running? If you are running 11.10 or newer use
onmode -ym EXPLAIN_STAT=1 then rerun the query with explain on to see whatpart of the query is taking the most time.
It will give you a better idea on where in the query you need to
concentrate you research.
George.
From: "LEONARDO SANTAGOSTINI" <lsantagostini@gmail.com>
To: ids@iiug.org
Date: 05/13/2010 02:21 PM
Subject: Re: RE: SQL Tunning [20145]
Sent by: ids-bounces@iiug.org
I apologize (maybe apologyze) for dont remember appending my statistics
plan.
QUERY:
------
insert into temp_agrupado select distinct g.nu_inscripcion from agrupado g,
abonado ab where g.nu_inscripcion = ab.nu_inscripcion
Estimated Cost: 2307483
Estimated # of Rows Returned: 2040
Maximum Threads: 1
1) informix.g: INDEX PATH
(1) Index Keys: co_cuenta nu_inscripcion (Key-Only) (Parallel, fragments:
ALL)
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Parallel, fragments: ALL)
Lower Index Filter: informix.g.nu_inscripcion = informix.ab.nu_inscripcion
NESTED LOOP JOIN
QUERY:
------
insert into temp_am_bolsa select distinct b.nu_inscripcion from am_bolsa b,
abonado ab where b.nu_inscripcion = ab.nu_inscripcion
Estimated Cost: 3
Estimated # of Rows Returned: 2
Maximum Threads: 1
1) informix.b: SEQUENTIAL SCAN
2) informix.ab: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Parallel, fragments: ALL)
Lower Index Filter: informix.b.nu_inscripcion = informix.ab.nu_inscripcion
NESTED LOOP JOIN
UPDATE STATISTICS:==================
Table: informix.temp_agrupado
Mode: MEDIUM
Number of Bins: 54 Bin size 74
Sort data 0.3 MB Sort memory granted 0.3 MB
Estimated number of table scans 1
PASS #1 nu_inscripcion
Scan 0 Sort 0 Build 0 Insert 0 Close 0 Total 0
Completed pass 1 in 0 minutes 0 seconds
UPDATE STATISTICS:==================
Table: informix.temp_agrupado
Mode: HIGH
Number of Bins: 267 Bin size 9393
Sort data 28.9 MB Sort memory granted 15.0 MB
Estimated number of table scans 1
PASS #1 nu_inscripcion
Light scans enabled
Scan 0 Sort 0 Build 6 Insert 0 Close 0 Total 6
Completed pass 1 in 0 minutes 6 seconds
QUERY:
------
SELECT
lr.co_mercado,
mm.no_mercado,
count(ab.nu_inscripcion)
FROM
abonado ab,
am_par_categorias apc,
localidad_rango lr,
mae_mercado mm
WHERE
(ab.co_categoria = apc.co_categoria) and
(apc.mr_procesar = '1') and
(ab.co_localidad = lr.co_localidad) and
(ab.co_ddn = lr.co_ddn) and
(ab.nu_prefijo = lr.nu_prefijo) and
(ab.nu_sufijo >= lr.nu_sufijo_desde) and
(ab.nu_sufijo <= lr.nu_sufijo_hasta) and
(lr.co_mercado in ('259','260','261','262','263','264')) and
(mm.co_mercado = lr.co_mercado) and
ab.nu_inscripcion not in (select nu_inscripcion from temp_agrupado) and
ab.nu_inscripcion not in (select nu_inscripcion from temp_am_bolsa)
group by 1,2
Estimated Cost: 306401
Estimated # of Rows Returned: 1
Maximum Threads: 1
Temporary Files Required For: Group By
1) informix.mm: INDEX PATH
(1) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '259'
(2) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '260'
(3) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '261'
(4) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '262'
(5) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '263'
(6) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = '264'
2) informix.lr: INDEX PATH
(1) Index Keys: co_mercado (Parallel, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = informix.lr.co_mercado
NESTED LOOP JOIN
3) informix.ab: INDEX PATH
Filters: (((informix.ab.nu_sufijo >= informix.lr.nu_sufijo_desde AND
informix.ab.nu_inscripcion != ALL <subquery> ) AN mix.lr.nu_sufijo_hasta )
AND
informix.ab.nu_inscripcion != ALL <subquery> )
(1) Index Keys: co_ddn nu_prefijo co_localidad (Parallel, fragments: ALL)
Lower Index Filter: ((informix.ab.nu_prefijo = informix.lr.nu_prefijo AND
informix.ab.co_localidad = informix.lr.co_lo = informix.lr.co_ddn )
NESTED LOOP JOIN
4) informix.apc: INDEX PATH
Filters: informix.apc.mr_procesar = '1'
(1) Index Keys: co_categoria (Parallel, fragments: ALL)
Lower Index Filter: informix.ab.co_categoria = informix.apc.co_categoria
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 63731
Estimated # of Rows Returned: 1878798
Maximum Threads: 1
1) informix.temp_agrupado: SEQUENTIAL SCAN
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) informix.temp_am_bolsa: SEQUENTIAL SCAN
Regards,
Leonardo
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hello George, Thank you for the reply. Im using 9.40.FC9W2X3, Good tip you give me, maybe in a future upgrade we will have 10.x or 11.x, so, this tip was added to my workbook =) Thank you !
Ok, now the results with the first query + execution time
>env | grep PS
PSORTNPROCS=2
PS1=[edithordt: $INST ($INFORMIXSERVER)][$PWD]
[edithordt: test (ontestcp)][/temporal/seguimiento/sesiones]
>unset PSORTNPROCS
>time dbaccess edithort << EOM
> set explain on;> SELECT
> lr.co_mercado,
> mm.no_mercado,
> count(ab.nu_inscripcion)
> FROM
> abonado ab,
> am_par_categorias apc,
> localidad_rango lr, mae_mercado mm
> mae_mercado mm
> WHERE
> (ab.co_categoria = apc.co_categoria) and
> (apc.mr_procesar = '1') and
> (ab.co_localidad = lr.co_localidad) and
> (ab.co_ddn = lr.co_ddn) and
> (ab.nu_prefijo = lr.nu_prefijo) and
> (ab.nu_sufijo >= lr.nu_sufijo_desde) and
> (ab.nu_sufijo <= lr.nu_sufijo_hasta) and
> (lr.co_mercado in ('259','260','261','262','263','264')) and
> (mm.co_mercado = lr.co_mercado) and
> (ab.nu_inscripcion not in (select g.nu_inscripcion from agrupado g where
g.nu_inscripcion = ab.nu_inscripcion)) and
> (ab.nu_inscripcion not in (select b.nu_inscripcion from am_bolsa b where
b.nu_inscripcion = ab.nu_inscripcion))
> group by 1,2
> EOM
Database selected.
Explain set.
co_mercado no_mercado (count)
259 GBA SUR 1
260 GBA OESTE 1
264 MENDOZA 4
261 BAHIA BLANCA 4
262 CORDOBA (TECO) 22
263 MAR DEL PLATA 3
6 row(s) retrieved.
Database closed.
real 9m45.86s
user 0m0.01s
sys 0m0.01s
[edithordt: test (ontestcp)][/temporal/seguimiento/sesiones]
And here is my sqexplain
QUERY:
------
SELECT
lr.co_mercado,
mm.no_mercado,
count(ab.nu_inscripcion)
FROM
abonado ab,
am_par_categorias apc,
localidad_rango lr,
mae_mercado mm
WHERE
(ab.co_categoria = apc.co_categoria) and
(apc.mr_procesar = '1') and
(ab.co_localidad = lr.co_localidad) and
(ab.co_ddn = lr.co_ddn) and
(ab.nu_prefijo = lr.nu_prefijo) and
(ab.nu_sufijo >= lr.nu_sufijo_desde) and
(ab.nu_sufijo <= lr.nu_sufijo_hasta) and
(lr.co_mercado in ('259','260','261','262','263','264')) and
(mm.co_mercado = lr.co_mercado) and
(ab.nu_inscripcion not in (select g.nu_inscripcion from agrupado g where
g.nu_inscripcion = ab.nu_inscripcion)) and
(ab.nu_inscripcion not in (select b.nu_inscripcion from am_bolsa b where
b.nu_inscripcion = ab.nu_inscripcion))
group by 1,2
Estimated Cost: 2372965
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By
1) informix.apc: INDEX PATH
(1) Index Keys: mr_procesar (Serial, fragments: ALL)
Lower Index Filter: informix.apc.mr_procesar = '1'
2) informix.ab: INDEX PATH
Filters: (informix.ab.nu_inscripcion != ALL <subquery> AND
informix.ab.nu_inscripcion != ALL <subquery> )
(1) Index Keys: co_categoria (Serial, fragments: ALL)
Lower Index Filter: informix.ab.co_categoria = informix.apc.co_categoria
NESTED LOOP JOIN
3) informix.lr: INDEX PATH
Filters: (informix.lr.co_mercado IN ('259' , '260' , '261' , '262' , '263' ,
'264' )AND informix.ab.nu_sufijo <= informix.lr.nu_sufijo_hasta )
(1) Index Keys: co_localidad co_ddn nu_prefijo nu_sufijo_desde nu_sufijo_hasta
(Serial, fragments: ALL)
Lower Index Filter: ((informix.ab.nu_prefijo = informix.lr.nu_prefijo AND
informix.ab.co_localidad = informix.lr.co_localidad ) AND informix.ab.co_ddn =
informix.lr.co_ddn )
Upper Index Filter: informix.ab.nu_sufijo >= informix.lr.nu_sufijo_desde
NESTED LOOP JOIN
4) informix.mm: INDEX PATH
(1) Index Keys: co_mercado (Serial, fragments: ALL)
Lower Index Filter: informix.mm.co_mercado = informix.lr.co_mercado
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.g: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.g.nu_inscripcion = informix.ab.nu_inscr
Subquery:
---------
Estimated Cost: 101
Estimated # of Rows Returned: 1
1) informix.b: INDEX PATH
(1) Index Keys: nu_inscripcion (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.b.nu_inscripcion = informix.ab.nu_inscr
So even with major cost, it executes faster than with lower cost.
Its so strange.
Any clue ?
Best regards,
Leonardo
Art,
Here are the results from the run made to the original query.
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
462221 557073 447946102 99.90 2128 8171 47802429 100.00
isamtot open start read write rewrite delete commit rollbk
88298475 2827965 4315674 9011102 1447025 3396 5 38 1
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 1080.80 33.39 5 10
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
11200 0 43789482 0 0 15 1403673 2807701
ixda-RA idx-RA da-RA RA-pgsused lchwaits
118544 7 42675 161221 0
[edithordt: test (ontestcp)][/temporal/seguimiento/sesiones]
onstat -c | grep -i locksLOCKS 1600000 # Maximum number of locks
[edithordt: test (ontestcp)][/temporal/seguimiento/sesiones]
(557073 + 47802429) / 1600000 = 30.2246888
So i will extend the number of buffers tomorrow and we will see what happens :D
Thanks for your help really appreciate it.
Regards,
Leonardo