What am I missing?

[Will Honea]

Been too long since I wrote queries but this one bugs me. Two fairly
large tables (DB2 7.2 and a remote DB2 server), one needs to update
fairly often based upon some properties stored in the first (in the
remote DB). I cobbled this together and it works, but as the local
table grows performance goes to pot in a hurry

UPDATE TABLE2 SET VALID_INFO ='Y'
WHERE TABLE2.NAME = (SELECT DISTINCT TABLE1.NAME
FROM TABLE1
WHERE TABLE1.NAME
= TABLE2.NAME)

I've forgotten what the problem with the above update is exactly (been
5 years or so since I did much SQL) and would apprecitate someone
pointing out just what is causing the slowdown.

When I was doing a lot of this I had a web site that had excellent
tutorial coverage of this kind of question but I've lost the URL - any
suggestions on a good 'SQL for Dummies' site?

--
Will Honea

[Benjamin Gufler]

Hi Will.

Will Honea wrote:

UPDATE TABLE2 SET VALID_INFO ='Y'
WHERE TABLE2.NAME = (SELECT DISTINCT TABLE1.NAME
FROM TABLE1
WHERE TABLE1.NAME
= TABLE2.NAME)

I've forgotten what the problem with the above update is exactly (been
5 years or so since I did much SQL) and would apprecitate someone
pointing out just what is causing the slowdown.

I guess, the above statement is slow because it contains a correlated
subquery (I dont't know if that's the exact english name, sorry if not).
For each row in TABLE2, the subquery is executed and returns either zero
or one row, depending on whether TABLE1 contains the name or not. Iff a
row is returned, the "current" row of TABLE2 is updated.
The statement will be faster if you remove the correlation, for example
like this:

update table2
set valid_info='Y'
where name in ( select name from table1 )

hth,
Benjamin
--
Please compose your messages as plaintext:
http://www.netby.dk/Oest/Europa-Alle/vermeer/plain.html
And do not send MS Office attachments:
http://www.goldmark.org/netrants/no-word/attach.html

[Serge Rielau]

Will Honea wrote:

Been too long since I wrote queries but this one bugs me. Two fairly
large tables (DB2 7.2 and a remote DB2 server), one needs to update
fairly often based upon some properties stored in the first (in the
remote DB). I cobbled this together and it works, but as the local
table grows performance goes to pot in a hurry

UPDATE TABLE2 SET VALID_INFO ='Y'
WHERE TABLE2.NAME = (SELECT DISTINCT TABLE1.NAME
FROM TABLE1
WHERE TABLE1.NAME
= TABLE2.NAME)

I've forgotten what the problem with the above update is exactly (been
5 years or so since I did much SQL) and would apprecitate someone
pointing out just what is causing the slowdown.

When I was doing a lot of this I had a web site that had excellent
tutorial coverage of this kind of question but I've lost the URL - any
suggestions on a good 'SQL for Dummies' site?

I would use:
UPDATE TABLE2 SET VALID_INFO ='Y'
WHERE TABLE2.NAME IN (SELECT TABLE1.NAME
FROM TABLE1
WHERE TABLE1.NAME
= TABLE2.NAME)

or EXISTS(SELECT 1
FROM TABLE1
WHERE TABLE1.NAME WHERE TABLE1.NAME = TABLE2.NAME)
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Will Honea]

On Fri, 4 Feb 2005 12:41:45 UTC Serge Rielau <sr*****@ca.ibm.com>
wrote:

Will Honea wrote:

Been too long since I wrote queries but this one bugs me. Two fairly
large tables (DB2 7.2 and a remote DB2 server), one needs to update
fairly often based upon some properties stored in the first (in the
remote DB). I cobbled this together and it works, but as the local
table grows performance goes to pot in a hurry

UPDATE TABLE2 SET VALID_INFO ='Y'
WHERE TABLE2.NAME = (SELECT DISTINCT TABLE1.NAME
FROM TABLE1
WHERE TABLE1.NAME
= TABLE2.NAME)

I've forgotten what the problem with the above update is exactly (been
5 years or so since I did much SQL) and would apprecitate someone
pointing out just what is causing the slowdown.

When I was doing a lot of this I had a web site that had excellent
tutorial coverage of this kind of question but I've lost the URL - any
suggestions on a good 'SQL for Dummies' site?

I would use:
UPDATE TABLE2 SET VALID_INFO ='Y'
WHERE TABLE2.NAME IN (SELECT TABLE1.NAME
FROM TABLE1
WHERE TABLE1.NAME
= TABLE2.NAME)

or EXISTS(SELECT 1
FROM TABLE1
WHERE TABLE1.NAME WHERE TABLE1.NAME = TABLE2.NAME)

OK, I see where this goes and the fog is lifting - I was doing a full
select on table1 for every row of table2. I used the access plan tool
(things have sure improved since I fought with 5.2 and 6.1 4-5 years
back!) and the size of the mess became obvious. Thanks for pointing
what is a subtle difference with enormous consequences.

--
Will Honea

[PM \(pm3iinc-nospam\) CGO]

I think a common table expression is executed only once and reused.
You may try it to see if it helps.
WITH ... AS ... (... distinct...) Select ...
PM

"Will Honea" <wh****@yahoo.com> a écrit dans le message de
news:Jx***************************@anon.none.net.. .

Been too long since I wrote queries but this one bugs me. Two fairly
large tables (DB2 7.2 and a remote DB2 server), one needs to update
fairly often based upon some properties stored in the first (in the
remote DB). I cobbled this together and it works, but as the local
table grows performance goes to pot in a hurry

UPDATE TABLE2 SET VALID_INFO ='Y'
WHERE TABLE2.NAME = (SELECT DISTINCT TABLE1.NAME
FROM TABLE1
WHERE TABLE1.NAME
= TABLE2.NAME)

I've forgotten what the problem with the above update is exactly (been
5 years or so since I did much SQL) and would apprecitate someone
pointing out just what is causing the slowdown.

When I was doing a lot of this I had a web site that had excellent
tutorial coverage of this kind of question but I've lost the URL - any
suggestions on a good 'SQL for Dummies' site?

--
Will Honea

[ak_tiredofspam]

to my best knowledge, not always