Oninit cdi / Archive
Talk to an expert →

Re: SQL performance problem

Posted in 1999

Topics: Performance & Tuning, SQL Development & Query Writing

Rudy Fernandes — — source: Informix-list mailing list archive (1991-1998)

Could be a case of sub-query flattening goof-up - the 'explained' path is clearly non-optimal. Besides ensuring that you have updated statistics, you could try the following (in an attempt to get the optimzer to behave) UPDATE table1 SET updt_field = "SOMETHING" WHERE EXISTS (SELECT 1 FROM table1 a, table2 b WHERE table3_unique_key = 434343 AND a.table1_unique_key = b.table1_unique_key) Rudy In article <7uepvk$543$1@plutonium.compulink.co.uk>, lsystemsb@cix.compulink.co.uk wrote: > I am having a performance problem with SQL similar to the following. Is > anyone able to suggest an alternative? The engine in question is Informix > DSA for NT v7.3.TC2 and TC7. > > Assuming table 1: > > create table table1 > ( table1_unique_key SERIAL, > updt_field CHAR (10) > )> > create unique index table1_ind ON table1(table1_unique_key)> > and table 2: > > create table table2 > ( table1_unique_key INTEGER, > table3_unique_key INTEGER > )> > create index table2_table1_ind ON table2(table1_unique_key) > create index table2_table3_ind ON table2(table3_unique_key)> > The SQL in question is :- > > UPDATE table1 SET updt_field = "SOMETHING" > WHERE table1_unique_key IN > (SELECT table1_unique_key FROM table2 > WHERE table3_unique_key = 434343)> > The results from SQLEXPLAIN look something like:= > > Estimated cost: 38058 > Estimated # of Rows Returned : 12 > > 1) informix.table2: INDEX PATH (Skip duplicate) > > Filters: informix.table2.table3_unique_key = 434343 > > (1) Index Keys: table1_unique_key > > 2) informix.table1: INDEX PATH > > (1) Index Keys: table1_unique_key > > Lower Index Filter: table1.table1_unique_key = > table2.table1_unique_key > NESTED LOOP JOIN > > I have tried OPTCOMPIND at all settings but this has no effect. > > Thanks in advance > > David. > > Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to Rudy Fernandes
Rudy Fernandes — — source: Informix-list mailing list archive (1991-1998)

The UPDATE statement I'd posted earlier is incorrect (and dangerous! Appears the Optimizer isn't the only goofball around!). It should read as follows (a correlated sub-query) UPDATE table1 SET updt_field = "SOMETHING" WHERE EXISTS (SELECT 1 FROM table2 b WHERE b.table3_unique_key = 434343 AND table1.table1_unique_key = b.table1_unique_key) Test it by replacing the UPDATE line with "SELECT count(*) from table1" or something similar to ensure that the UPDATE is going to work correctly. You could even save a backup of the data by replacing the UPDATE line with "UNLOAD TO backup.dat SELECT * FROM table1" Sorry about that. Rudy In article <7uhnj8$6lc$1@nnrp1.deja.com>, Rudy Fernandes <rferdy@my-deja.com> wrote: > Could be a case of sub-query flattening goof-up - the 'explained' path > is clearly non-optimal. > > Besides ensuring that you have updated statistics, you could try the > following (in an attempt to get the optimzer to behave) > > UPDATE table1 SET updt_field = "SOMETHING" > WHERE EXISTS > (SELECT 1 FROM table1 a, table2 b > WHERE table3_unique_key = 434343 > AND a.table1_unique_key = b.table1_unique_key)> > Rudy > > In article <7uepvk$543$1@plutonium.compulink.co.uk>, > lsystemsb@cix.compulink.co.uk wrote: > > I am having a performance problem with SQL similar to the following. > Is > > anyone able to suggest an alternative? The engine in question is > Informix > > DSA for NT v7.3.TC2 and TC7. > > > > Assuming table 1: > > > > create table table1 > > ( table1_unique_key SERIAL, > > updt_field CHAR (10) > > )> > > > create unique index table1_ind ON table1(table1_unique_key)> > > > and table 2: > > > > create table table2 > > ( table1_unique_key INTEGER, > > table3_unique_key INTEGER > > )> > > > create index table2_table1_ind ON table2(table1_unique_key) > > create index table2_table3_ind ON table2(table3_unique_key)> > > > The SQL in question is :- > > > > UPDATE table1 SET updt_field = "SOMETHING" > > WHERE table1_unique_key IN > > (SELECT table1_unique_key FROM table2 > > WHERE table3_unique_key = 434343)> > > > The results from SQLEXPLAIN look something like:= > > > > Estimated cost: 38058 > > Estimated # of Rows Returned : 12 > > > > 1) informix.table2: INDEX PATH (Skip duplicate) > > > > Filters: informix.table2.table3_unique_key = 434343 > > > > (1) Index Keys: table1_unique_key > > > > 2) informix.table1: INDEX PATH > > > > (1) Index Keys: table1_unique_key > > > > Lower Index Filter: table1.table1_unique_key = > > table2.table1_unique_key > > NESTED LOOP JOIN > > > > I have tried OPTCOMPIND at all settings but this has no effect. > > > > Thanks in advance > > > > David. > > > > > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.