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