How to avoid a subquery in Informix??

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.IDWhy 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

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

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

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

I knew of a customer that wanted to use ER on a table that didn't have a PK, so he made up a PK of all of the columns in the table.... ;-) "rkusenet" <rkusenet@sympatico.ca> wrote in message news:2h4nd7F8mlohU1@uni-berlin.de... > > "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- > >

Serge Rielau wrote: > 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. The above UPDATE FROM statement also returns an error in XPS if tab1.ID is not unique. The error message is 23911: Cannot update a row twice in a joined-row update. Andreas

Andreas Weininger wrote: > Serge Rielau wrote: > >> 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. > > > The above UPDATE FROM statement also returns an error in XPS if tab1.ID > is not unique. The error message is > > 23911: Cannot update a row twice in a joined-row update.> > > Andreas > > That's good news :-) On SQL Server it will do it without flinching. I couldn't find the documentation for the XPS behavior. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab