db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mikkel Kamstrup Erlandsen <...@statsbiblioteket.dk>
Subject Re: Bad performance with UPDATE on a nested SELECT
Date Tue, 13 Jan 2009 22:54:14 GMT
On Tue, 2009-01-13 at 16:42 +0100, Knut Anders Hatlen wrote:
> ij> create table summa_records (id varchar(20), base varchar(10));
> 0 rows inserted/updated/deleted
> ij> create table summa_relations (parentId varchar(20), childId varchar(20));
> 0 rows inserted/updated/deleted
> ij> insert into summa_records(id) values 'test1', 'test2';
> 2 rows inserted/updated/deleted
> ij> insert into summa_relations values ('test1', 'test2');
> 1 row inserted/updated/deleted
> ij> create index c on summa_relations(childId);
> 0 rows inserted/updated/deleted
> ij> UPDATE summa_records SET base='my_base' WHERE id IN
> >   (SELECT parentId FROM summa_relations --DERBY-PROPERTIES index=C
> >     WHERE childId='test2');
> 1 row inserted/updated/deleted

Ok, thanks for you patience, I got it working just now... The query
still runs in about 4-5s (on 700k records) though...

It seems I might have to hack around this by doing the SELECT first and
then iterating over the result set and doing one UPDATE per row... It
should still perform fairly well, even though it makes me feel a bit

If it will make any difference I can attach the query plan to
https://issues.apache.org/jira/browse/DERBY-4007. Likewise with the
results of the interger based ids in my discussion with Mike..?


View raw message