Join IIUG
 for   
 

Informix News
18 Nov 13 - ZDNet - Top 20 mobile skills in demand... Read
09 Sep 13 - telecompaper - Shaspa and Tatung have shown a new smart home platform at Ifa in Berlin. Powered by the IBM Informix software... Read
06 Sep 13 - IBM data magazine - Mission Accomplished - Miami, Florida will be the backdrop for the 2014 IIUG Informix Conference... Read
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read


End of Support Dates

IIUG on Facebook IIUG on Twitter

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

Re: SQL Tunning

Posted By: Srinivasan R Mottupalli
Date: Wednesday, 12 May 2010, at 10:19 a.m.

In Response To: SQL Tunning (LEONARDO SANTAGOSTINI)

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".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.

Messages In This Thread

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.