I've been following this with interest, because this is the opposite of
what I remember. I have a big "cheat sheet" document with all the
helpful RBase tips I see over the years. And here's the exact wording
of an email from RBTI comparing different ways of doing things. Now,
this shows a "select", not an "update". But I would think the update
would operate in the same fashion. Am I wrong? Or is it because
as they say there's only a 1-to-1 match?
Karen
How to speed up SQL Syntax:
To illustrate, compare the results obtained by using the different
techniques on two tables, each with 1,000 unique rows (each row in Table1 has
only
one match in Table2).
(1) A multi-table select, shown below, took 4 seconds (the fastest).
SELECT collist FROM table1,table2 WHERE Table1.LinkCol =
Table2.LinkCol
(2) A correlated sub-select, which looked at every row in Table1 but needed
to look at only one row in Table2 for each row in Table1, takes 10 seconds.
The example is shown below:
SELECT collist FROM Table1 WHERE LinkCol IN +
(SELECT LinkCol FROM Table2 WHERE Table2.LinkCol=Table1.LinkCol)
(3) A plain sub-select, which had to look for a match in every row in
Table2 for each of the 1,000 rows of Table1, will take more than 3 minutes to
complete. This example, by far the slowest, is shown below:
SELECT collist FROM Table1 WHERE LinkCol IN (SELECT LinkCol FROM
Table2)
> I am writing a routine that has not been deployed yet so the symptoms
> manifest themselves on a standalone PC. When I use the update with a
> select
> in the where clause it is fast (a second or two verses 3 minutes). I plan
> to do some more testing when I get a chance. I would expect table
> correlation to add overhead but not that much overhead.
>
> John
