SQL performance problem

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.

You could try the second select into a temporary table to hold only rows that match your > WHERE table3_unique_key = 434343) Have you ran UPDATE STATISTICS lately ?

Try replacing the indexes on table2 with composite keyindexes: 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_table1_ind ON table2( table1_unique_key, table3_unique_key ); > create index table2_table3_ind ON table2(table3_unique_key) create index table2_table3_ind ON table2( table3_unique_key, table1_unique_key ); This will permit the optimizer to perform KEY-ONLY searches on table2. Also make sure that statistics are updated to the recommended levels which would include, at least: UPDATE STATISTICS HIGH FOR table2( table1_unique_key ); UPDATE STATISTICS LOW FOR table2( table1_unique_key, table3_unique_key ); UPDATE STATISTICS HIGH FOR table2( table3_unique_key ); UPDATE STATISTICS LOW FOR table2( table3_unique_key, table1_unique_key ); UPDATE STATISTICS HIGH FOR table1( table1_unique_key ); UPDATE STATISTICS HIGH FOR table3( table3_unique_key ); Art S. Kagel