Hi all

If I have the same job, I'll do it in other way.

Please, let me know how much rows there is in each table.

Supose you have 2 tables and the number of rows are similar ( 1 by 1
in the relationship). Or in the 'child' table you have less rows than
the parent.
The data type and the length of the columns doesn't matter.

Tab1 (parent)
(col1 integer,
col2 smallint,
col3 char(50)
col5 char(20) )
create unique index (col1, col2) and constraint primary key (col1,
col2);

Tab2 (child)
(col1 integer,
col2 smallint,
col6 char(50)
col7 char(30) )
create index (col1, col2) and constraint foreign key (col1, col2);

set isolation to dirty read;
select t1.col1 , t1.col2, t2.col6 from tab1 t1, tab2 t2
where t1.col1 = t2.col1 and t1.col2 = t2.col2 for read only
into temp t_tab1 with no log ;
create index ixt01 on t_tab1 (col1, col2);
update statistics for table t_tab1;
update statistics high for table t_tab1 (col1, col2);

select col1, col2 from t_tab1 group by 1, 2
into temp t_tab2 with no log ;
create index ixt02 on t_tab2 (col1, col2);
update statistics for table t_tab2;

At end:

update tab1 set col3 = (select col6 from t_tab1
where t_tab1.col1 = tab1.col1 and t_tab1.col2 = tab1.col2 )
where exists
(select 0 from t_tab2
where t_tab2.col1 = tab1.col1 and t_tab2.col2 = tab1.col2 );

For sure, it will be quickly .....

If you have the tab2 with duplicates of col1, col2, then:

Add these steps:
create index ixt01d on t_tab1 (col6);
update statistics high for table t_tab1 (col6);

update tab1 set col3 = (select min(col6) from t_tab1
where t_tab1.col1 = tab1.col1 and t_tab1.col2 = tab1.col2 )
where exists
(select 0 from t_tab2
where t_tab2.col1 = tab1.col1 and t_tab2.col2 = tab1.col2 );


Cheers........ Boa sorte !! R. Ferronato

"Savio Pinto (s)" <spinto@cap.org> wrote in message news:<c8l4bc$ftb$1@terabinaries.xmission.com>...
> if it is taking 2 days to run the job, then i think you need to consider
> re-writing the query, here are few suggestions what you can do to speed up
> the query
>
> 1) you can use outer join and avoid sub-queries.
> 2) avoid using update statement (if it is a DSS environment, use
> insert/delete instead of updates)
> 3) explore the possibility of informix table fragmentation (if it is a DSS
> environment avoid using indexes), you can fragment the table using
> expression/mod and then use the fragment elimination technique to speed up
> the operation/query.
>
>
> -----Original Message-----
> From: owner-informix-list@iiug.org
> [mailtowner-informix-list@iiug.org]On Behalf Of s_zhangnz@yahoo.co.nz
> Sent: Thursday, May 20, 2004 4:42 PM
> To: informix-list@iiug.org
> Subject: Re: How to avoid a subquery in Informix??
>
>
> Serge Rielau <srielau@ca.eye-be-em.com> wrote in message
> news:<c8ieus$7v3$1@hanover.torolab.ibm.com>...
> > Serge Rielau wrote:
> >
> > > Taamial wrote:
> > >
> > >> I have a job using subqueries. it is extrimely slow. So I want to
> > >> remove the subquery, but it seems no way to do it in Informix?
> > >>
> > >> For example, there are 2 tables: tab1 and tab2, they have the same
> > >> fields: ID and DESC, ID is the PK. The 2 tables have different data.
> > >> If I want to update the records in tab2 to make the DESC equal to the
> > >> corresponding DESC in tab1, I have to use the following query:
> > >>
> > >> Update tab2 set tab2.DESC = (select DESC from tab1 where tab1.ID =
> > >> tab2.ID)
> > >> where Exists
> > >> (select * from tab1 where tab1.ID = tab2.ID)
> > >>
> > >> can somebody please advise me how to remove the 2 subqueries?
> > >>
> > >> Thanks a lot
> > >
> > > Here is a near correct rewrite.
> > > UPDATE tab2 SET tab2.DESC FROM tab2, tab1 WHERE tab1.ID = tab2.ID
> > > Why is it only near correct? If tab1.ID is not unique you can get
> > > interesting results.
> > > The subquery in the SET clause will return an error if more than one row
>
> > > matched tab1.ID.
> > >
> > > Cheers
> > > Serge
> > >
> > Note that what UPADTE FROM is supported by XPS only.
> > Thanks to June for pointing this out.
>
> Hi Serge
>
> Thanks for the help. I tried your way, it works in MS SQL server, but
> in Informix, the syntax is not correct?
>
> BTW, our SQL is Informix-SQL Version 7.20.UD6
>
> Here is just an exmaple, the real tables are complicated (with 7
> fields combined as the PK) and very big. It takes more than 2 days to
> run this job, and it is getting worse as the data are gettting more!
>
> Many thanks again!
> sending to informix-list