[comp.databases] need help with sql problem

stu@jpusa1.UUCP (03/20/87)

I'm trying to do what should be a simple update and can't believe the
abysmal performance.  Maybe I'm missing something obvious.  The scenario
is a 3 table database (name,address,accounts) and I'm updating a batch
from a fourth table.  Here's the code to do the name:
	update name_table
		set name = (select name from change_table where
			name_table.id = change_table.id)
		where name_table.id in
			(select id from change_table);
This seems overly complex when all I want to do is find the entries in
the name_table whose id match those in the change_table and update the
name column (field).  If I leave the second where/select out, the
entries that don't match get thier name's nulled but this scheme seems
to require sizeof name_table * sizeof change_table querries.  The bad
news is that a 3 table update takes about 36 seconds per transaction and
an 1800 row batch took 14 hours!!!  Am I doing something wrong?  Please
e-mail any advice/hints/ideas.  I've already looked at unify and
informix and need to stay with an sql product that will run on an a.t.
under xenix or microport.

Thanks in advance.
Stu Heiss {...!ihnp4!jpusa1!stu}
-- 
Stu Heiss {gargoyle,ihnp4}!jpusa1!stu

garyp@cognos.UUCP (Gary Puckering) (03/25/87)

In article <276@jpusa1.UUCP> stu@jpusa1.UUCP (Stu Heiss) writes:
>I'm trying to do what should be a simple update and can't believe the
>abysmal performance.  Maybe I'm missing something obvious.  The scenario
>is a 3 table database (name,address,accounts) and I'm updating a batch
>from a fourth table.  Here's the code to do the name:
>	update name_table
>		set name = (select name from change_table where
>			name_table.id = change_table.id)
>		where name_table.id in
>			(select id from change_table);
>This seems overly complex when all I want to do is find the entries in
>the name_table whose id match those in the change_table and update the
>name column (field).  

Wow!  This is scarey!  I hope someone can come up with an elegant SQL
request for this.  I can't think of one.  However, I can offer a suggestion
and a comment:

1.  You *must* have an index on the id column of change_table.  Otherwise,
	a sequential scan of change_table will be required for every row
	read from name_table.

2.  The strategy SQL has forced you into is a bad one.  You should be
	able to drive the update request from the change_table, rather than
	from the name_table.  For examplem, in QTP, our 4GL transaction
	processor, you would code this request as:

		access change_table link to name_table
		output name_table update
		  item name final name of change_table
        go

    For this request to execute efficiently, their need only be an index
    on id of name_table.  Essentially, this request does a natural join of
    change_table and name_table and then it updates only the name_table row.

    Unfortunately, I can't see any way to do this in interactive SQL.

3.  This above strategy could be accomplished within a program by using
    cursor.  For example:

		sql declare get_changes cursor for
		  select id.change_table name.change_table
		  into :xid :xname
		  from change_table, name_table
		  where id.change_table = id.name_table

    Now you can code a loop which fetches the appropriate id's and names
	into host variables xid and xname.  Within the loop you code an SQL
	update request like:

		sql update name_table
		  set name = :xname
		  where id = :xid


Like I say, I hope someone else can come up with a decent solution which
can be coded in interactive SQL.  If not, it just goes to show you that
SQL is a lousy excuse for a 4GL.  It leads to counter-intuitive requests.

-- 
Gary Puckering        3755 Riverside Dr.
Cognos Incorporated   Ottawa, Ontario       decvax!utzoo!dciem!
(613) 738-1440        CANADA  K1G 3N3       nrcaer!cognos!garyp