db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Bad table design leads to bad performance...RE: Bad performance with UPDATE on a nested SELECT
Date Wed, 14 Jan 2009 13:15:02 GMT


> -----Original Message-----
> From: Mikkel Kamstrup Erlandsen [mailto:mke@statsbiblioteket.dk]
> Sent: Wednesday, January 14, 2009 12:12 AM
> To: Derby Discussion
> Cc: msegel@segel.com
> Subject: RE: Bad table design leads to bad performance...RE: Bad
> performance with UPDATE on a nested SELECT
[SNIP]
> >
> > As I said above, I just tried out your strategy. Using only integer
> > handles the query runs in about 4s. I still need a factor 100 better
> > than that...
> 
> Sorry, forgot to add that this was on a base with ~700k rows only, not
> the full 1,5M rows one...
> 
> Cheers,
> Mikkel

Mikkel,

Sorry if I'm being a bit slow...

That 4 seconds was on 700K rows using integers instead of varchars?
Does that also include the optimizer hint or was that without the optimizer
hint?

The reason I ask is that if your query was the same and you just switched
from varchar to integers, then there clearly is an issue with varchars and
the optimizer. 

If I understood you correctly, it sounded like when you tried the optimizer
hint, using varchars, you got the same result as the integers. Is that the
case?
Again its evidence that supports the issue that there is something wrong
with the optimizer.

If you're going to loop through the result set, you may find it faster to
delete the old row and then perform the insert. Updates are hard(er) on a
database.

Derby isn't Postgres and there are some neat things you can do in C that you
can't do in Java that could give a C based database an edge. (Pointers can
be your friend. ;-) At the same time, there are design decisions that could
have been made which are hampering performance now. 

-Mike




Mime
View raw message