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

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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Is there a 1 to 1 relation between the rows in table 1 and those in
table2?

if so, I'd merge the 2 tables into one.

Otherwise, I would agree the preformance of this will not be optimal,
your query is performing 2 selects for every row it finds in the table
being updated

May be better to write a function that

selects every row from tab2 sequentially and then updates the
appropriate row(s) in table1



s_zhangnz@yahoo.co.nz (Taamial) wrote in message news:<3f095c0c.0405192144.2b2ad82b@posting.google. com>...
> 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

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.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

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!

"Taamial" <s_zhangnz@yahoo.co.nz> wrote

> 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!

In my 15 yrs of career, this is the first time I have heard of a PK of 7
fields. Something seems to be wrong terribly.

I do believe that the data in a table by itself should lead to primary key.
That is, not using a surrogate field like auto generated number (serial field).
But 7 fields is way too much. Preferably 1 field, two is some cases and
max 3 in rare case.

rk-

Thank you very much for reminding me of merging tables!

dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0405200622.26e3d07f@posting.google. com>...
> Is there a 1 to 1 relation between the rows in table 1 and those in
> table2?
>
> if so, I'd merge the 2 tables into one.
>
> Otherwise, I would agree the preformance of this will not be optimal,
> your query is performing 2 selects for every row it finds in the table
> being updated
>
> May be better to write a function that
>
> selects every row from tab2 sequentially and then updates the
> appropriate row(s) in table1
>
>
>
> s_zhangnz@yahoo.co.nz (Taamial) wrote in message news:<3f095c0c.0405192144.2b2ad82b@posting.google. com>...
> > 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

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.


The other reason it is only 'near correct' is that it doesn't work in
IDS - but does in DB2.


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

I suspect that the best way to improve performance with this query would be
to try to make the select against tab1 a key-only select. That would mean
creating a composite index on ID + DESC.

M.P.


"Taamial" <s_zhangnz@yahoo.co.nz> wrote in message
news:3f095c0c.0405192144.2b2ad82b@posting.google.c om...
> 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

Jonathan Leffler wrote:

> The other reason it is only 'near correct' is that it doesn't work in
> IDS - but does in DB2.
It doesn't, actually.
However DB2 will _internaly_ rewrite the double subquery into an UPDATE
FROM if it can prove the uniqueness condition.
other than that:
MERGE INTO T USING S
ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET T.data = S.data

Does the job in DB2 V8.1

Given that XPS, Sybase and MS SQL Server support UPDATE FROM, I can see
the writing on the wall, though. In my experience developers have great
difficulty mapping the statement across.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab