 |
IDS Forum
Re: SQL Tunning
Posted By: Art Kagel
Date: Wednesday, 12 May 2010, at 10:20 a.m.
In Response To: SQL Tunning (LEONARDO SANTAGOSTINI)
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 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,
>
> primary key
> (co_localidad,co_ddn,nu_prefijo,nu_sufijo_desde,nu_sufijo_hasta)
>
> );
> revoke all on "edithor".localidad_rango from "public";
>
> create index "informix".ix_leox1 on "edithor".localidad_rango
>
> (co_mercado) using btree ;
> create index "informix".ix_leox2 on "edithor".localidad_rango
>
> (nu_sufijo_desde) using btree ;
> create index "informix".ix_leox3 on "edithor".localidad_rango
>
> (nu_sufijo_hasta) using btree ;
> create index "informix".ix_leox7 on "edithor".localidad_rango
>
> (co_ddn,nu_prefijo,co_localidad) using btree ;
> alter table "edithor".localidad_rango add constraint (foreign
>
> key (co_localidad) references "edithor".mae_localidad );
>
> alter table "edithor".localidad_rango add constraint (foreign
>
> key (co_ddn) references "edithor".mae_ddn );
>
> alter table "edithor".localidad_rango add constraint (foreign
>
> key (ti_servicio) references "edithor".tipo_servicio constraint
>
> "edithor".fk_localidad_ra1);
>
> alter table "edithor".localidad_rango add constraint (foreign
>
> key (ti_empresa) references "edithor".tipo_empresa constraint
>
> "edithor".fk_localidad_ra2);
>
> table: mae_mercado
> create table "edithor".mae_mercado
> (
>
> co_mercado char(3) not null ,
>
> no_mercado varchar(30) not null ,
>
> co_camp_vigente char(4) not null ,
>
> co_camp_anterior char(4) not null ,
>
> fl_activo char(1) not null ,
>
> fe_alta date,
>
> fe_baja date,
>
> fl_mercado_tasa char(1),
>
> co_guia char(3),
>
> ti_mercado char(2),
>
> fl_multi_ficha char(1)
>
> default '0',
>
> co_mercado_pub char(3),
>
> primary key (co_mercado)
> );
> revoke all on "edithor".mae_mercado from "public";
>
> create index "edithor".xmer_mpub_nuk1 on "edithor".mae_mercado
>
> (co_mercado_pub) using btree ;
> alter table "edithor".mae_mercado add constraint (foreign key
>
> (ti_mercado) references "edithor".tipo_mercado constraint
>
> "edithor".fk_mae_mercado1);
>
> And the output from sqexplain.out:
>
> 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
>
> Estimated Cost: 878030
> Estimated # of Rows Returned: 1
> Temporary Files Required For: Group By
>
> 1) edithor.localidad_rango: SEQUENTIAL SCAN
>
> Filters: edithor.localidad_rango.co_mercado IN ('259' , '260' , '261' ,
> '262'
> , '263' , '264' )
>
> 2) edithor.abonado: INDEX PATH
>
> Filters: (((edithor.abonado.nu_sufijo >=
> edithor.localidad_rango.nu_sufijo_desde AND edithor.abonado.nu_sufijo <=
> edithor.localidad_rango.nu_sufijo_ha
> sta ) AND edithor.abonado.nu_inscripcion != ALL <subquery> ) AND
> edithor.abonado.nu_inscripcion != ALL <subquery> )
>
> (1) Index Keys: co_ddn nu_prefijo co_localidad (Serial, fragments: ALL)
>
> Lower Index Filter: ((edithor.abonado.nu_prefijo =
> edithor.localidad_rango.nu_prefijo AND edithor.abonado.co_localidad =
> edithor.localidad_rango.co_lo
> calidad ) AND edithor.abonado.co_ddn = edithor.localidad_rango.co_ddn )
> NESTED LOOP JOIN
>
> 3) 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
>
> 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
> 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 =
> edithor.abonado.nu_inscripcion
>
> Subquery:
>
> ---------
>
> Estimated Cost: 1
>
> 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 =
> edithor.abonado.nu_inscripcion
>
> My question is, can you figure out how to make this sql execute at lower
> cost
> ?
>
> Thank you very much,
> Leonardo
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636e0ba02201ccb0486665780
Messages In This Thread
- SQL Tunning
LEONARDO SANTAGOSTINI -- Wednesday, 12 May 2010, at 9:42 a.m.
- Re: SQL Tunning
Srinivasan R Mottupalli -- Wednesday, 12 May 2010, at 10:19 a.m.
- Re: SQL Tunning
Art Kagel -- Wednesday, 12 May 2010, at 10:20 a.m.
- Re: SQL Tunning
LEONARDO SANTAGOSTINI -- Wednesday, 12 May 2010, at 10:38 a.m.
- Re: SQL Tunning
Art Kagel -- Wednesday, 12 May 2010, at 10:44 a.m.
- Re: SQL Tunning
LEONARDO SANTAGOSTINI -- Wednesday, 12 May 2010, at 1:03 p.m.
- Re: SQL Tunning
Art Kagel -- Wednesday, 12 May 2010, at 1:54 p.m.
- Re: SQL Tunning
LEONARDO SANTAGOSTINI -- Wednesday, 12 May 2010, at 2:33 p.m.
- Re: SQL Tunning
rodolfo.molinas@eby.gov.py -- Wednesday, 12 May 2010, at 2:01 p.m.
- Re: SQL Tunning
LEONARDO SANTAGOSTINI -- Wednesday, 12 May 2010, at 2:37 p.m.
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
