Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!utgpu!water!watnot!watmath!clyde!rutgers!ames!ptsfa!ihnp4!gargoyle!jpusa1!stu
From: stu@jpusa1.UUCP
Newsgroups: comp.databases
Subject: need help with sql problem (informix)
Message-ID: <276@jpusa1.UUCP>
Date: Fri, 20-Mar-87 04:26:00 EST
Article-I.D.: jpusa1.276
Posted: Fri Mar 20 04:26:00 1987
Date-Received: Sun, 22-Mar-87 19:04:14 EST
Sender: news@jpusa1.UUCP
Reply-To: stu@jpusa1.UUCP (Stu Heiss)
Organization: JPUSA - Chicago, IL
Lines: 24
Keywords: sql,informix

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