Optimzation of Update from a table that select from other table
The poster wanted to update a 35M-row table A from a small (~20,000-row) temp table B on matching id, without the optimizer scanning B for every row of A. Suggestions: rewrite the statement as UPDATE A SET col = (SELECT col FROM B WHERE A.id=B.id) WHERE id IN (SELECT id FROM B) with indexes on id; or build a third table from the join and rename it (though that risks losing non-matching rows). Art Kagel argued this isn't a job for pure SQL and posted a simple SPL/host-language loop that FOREACHes over the small temp table and updates the big table by id, which should run far faster. No confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a simple question. I want to update table "A" with contents in table "B" where A.id = B.id. I also want that optimizer walks through table B and updates table A. How do I get the query that does this. My current queries look like this update A set id = (select id from B where A.id = B.id) This query will for each row in table A scan complete table B. I could use index and index directive on column of table A but rows in table A is 10 times more than table A. Basically, I want to walk through B that matches id in A.
How big are the tables? In Gigabytes or whatnot, nrows would be nice too j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of mohitanchlia@gmail.com Sent: Saturday, July 14, 2007 8:29 PM To: informix-list@iiug.org Subject: Optimzation of Update from a table that select from other table I have a simple question. I want to update table "A" with contents in table "B" where A.id = B.id. I also want that optimizer walks through table B and updates table A. How do I get the query that does this. My current queries look like this update A set id = (select id from B where A.id = B.id) This query will for each row in table A scan complete table B. I could use index and index directive on column of table A but rows in table A is 10 times more than table A. Basically, I want to walk through B that matches id in A. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
mohitanchlia@gmail.com wrote:
> My current queries look like this
>
> update A
> set id = (select id from B where A.id = B.id)
This is a bizarre query...
Are you sure you don't want:
UPDATE A SET c1 = (select c1 FROM B WHERE A.id = B.id)
WHERE id IN (SELECT id FROM B)
Assuming id is indexed for both A and B I would expect an index to be
picked up.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
On Jul 14, 6:33 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> mohitanch...@gmail.com wrote:
> > My current queries look like this
>
> > update A
> > set id = (select id from B where A.id = B.id)
>
> This is a bizarre query...
> Are you sure you don't want:
> UPDATE A SET c1 = (select c1 FROM B WHERE A.id = B.id)
> WHERE id IN (SELECT id FROM B)>
> Assuming id is indexed for both A and B I would expect an index to be
> picked up.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
I have around 35M rows in table A. Table B is a temp table which will
have 20000 rows and will be created atleast 200 times, which also
means the update statement will be called 200 times. What's the most
optimal way of updating table A. Is USE_HASH directive going to help ?
At that volume a rewrite approach may be faster. Build a third table with
the results of the join of the two, at the end rename it. YMMV.
Here is a write up of that approach.
http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/par
ker/0502parker.html
j.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of
mohitanchlia@gmail.com
Sent: Sunday, July 15, 2007 1:51 AM
To: informix-list@iiug.org
Subject: Re: Optimzation of Update from a table that select from other
table
On Jul 14, 6:33 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> mohitanch...@gmail.com wrote:
> > My current queries look like this
>
> > update A
> > set id = (select id from B where A.id = B.id)
>
> This is a bizarre query...
> Are you sure you don't want:
> UPDATE A SET c1 = (select c1 FROM B WHERE A.id = B.id)
> WHERE id IN (SELECT id FROM B)>
> Assuming id is indexed for both A and B I would expect an index to be
> picked up.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
I have around 35M rows in table A. Table B is a temp table which will
have 20000 rows and will be created atleast 200 times, which also
means the update statement will be called 200 times. What's the most
optimal way of updating table A. Is USE_HASH directive going to help ?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
On Jul 15, 5:19 am, "Jack Parker" <jack.park...@verizon.net> wrote:
> At that volume a rewrite approach may be faster. Build a third table with
> the results of the join of the two, at the end rename it. YMMV.
>
> Here is a write up of that approach.http://www.ibm.com/developerworks/db2/zones/informix/library/techarti...
> ker/0502parker.html
>
> j.
>
>
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of
> mohitanch...@gmail.com
> Sent: Sunday, July 15, 2007 1:51 AM
> To: informix-l...@iiug.org
> Subject: Re: Optimzation of Update from a table that select from other
> table
>
> On Jul 14, 6:33 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> > mohitanch...@gmail.com wrote:
> > > My current queries look like this
>
> > > update A
> > > set id = (select id from B where A.id = B.id)
>
> > This is a bizarre query...
> > Are you sure you don't want:
> > UPDATE A SET c1 = (select c1 FROM B WHERE A.id = B.id)
> > WHERE id IN (SELECT id FROM B)>
> > Assuming id is indexed for both A and B I would expect an index to be
> > picked up.
>
> > Cheers
> > Serge
> > --
> > Serge Rielau
> > DB2 Solutions Development
> > IBM Toronto Lab
>
> I have around 35M rows in table A. Table B is a temp table which will
> have 20000 rows and will be created atleast 200 times, which also
> means the update statement will be called 200 times. What's the most
> optimal way of updating table A. Is USE_HASH directive going to help ?
>
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text -
>
> - Show quoted text -
But there might be some rows in table A that will not get updated and
hence will not get into the new table. So if I rename I'll lose those
rows. Do you mean insert them separately, but is it more efficient
than update ?
I haven't looked at the article as it's not a valid link.
On Jul 15, 5:44 pm, mohitanch...@gmail.com wrote:
> On Jul 15, 5:19 am, "Jack Parker" <jack.park...@verizon.net> wrote:
>
>
>
> > At that volume a rewrite approach may be faster. Build a third table with
> > the results of the join of the two, at the end rename it. YMMV.
>
> > Here is a write up of that approach.http://www.ibm.com/developerworks/db2/zones/informix/library/techarti...
> > ker/0502parker.html
>
> > j.
>
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org
>
> > [mailto:informix-list-boun...@iiug.org]On Behalf Of
> > mohitanch...@gmail.com
> > Sent: Sunday, July 15, 2007 1:51 AM
> > To: informix-l...@iiug.org
> > Subject: Re: Optimzation of Update from a table that select from other
> > table
>
> > On Jul 14, 6:33 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> > > mohitanch...@gmail.com wrote:
> > > > My current queries look like this
>
> > > > update A
> > > > set id = (select id from B where A.id = B.id)
>
> > > This is a bizarre query...
> > > Are you sure you don't want:
> > > UPDATE A SET c1 = (select c1 FROM B WHERE A.id = B.id)
> > > WHERE id IN (SELECT id FROM B)>
> > > Assuming id is indexed for both A and B I would expect an index to be
> > > picked up.
>
> > > Cheers
> > > Serge
> > > --
> > > Serge Rielau
> > > DB2 Solutions Development
> > > IBM Toronto Lab
>
> > I have around 35M rows in table A. Table B is a temp table which will
> > have 20000 rows and will be created atleast 200 times, which also
> > means the update statement will be called 200 times. What's the most
> > optimal way of updating table A. Is USE_HASH directive going to help ?
>
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list-Hide quoted text -
>
> > - Show quoted text -
>
> But there might be some rows in table A that will not get updated and
> hence will not get into the new table. So if I rename I'll lose those
> rows. Do you mean insert them separately, but is it more efficient
> than update ?
>
> I haven't looked at the article as it's not a valid link.
This is a perfect example of a problem that was NEVER meant to be
solved with SQL. You can write a host language
application in Perl, ESQL/C, C w/ODBC, Java w/JDBC, etc. and it will
run FAR faster than any pure SQL solution. You could probably even
code an SPL routine to do the updates more quickly. Here's a simple
one:
create procedure quick_update ();define l_var1, l_var2, l_var3 char(100);
define l_id integer;
foreach
select id, var1, var2, var3 from temp_table
into l_id, l_var1, l_var2, l_var3do
update orig_table
set var1 = l_var1, var2 = l_var2, var3 = l_var3
where id = l_id;
end foreach;
end procedure;
That's just Q&D pseudo SPL and not checked for compileablity, and
certainly not compatible with your tables, but
you'll get the idea.
Art S. Kagel