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 table design leads to bad performance...RE: Bad performance with UPDATE on a nested SELECT
Date Wed, 14 Jan 2009 06:12:04 GMT
On Tue, 2009-01-13 at 23:47 +0100, Mikkel Kamstrup Erlandsen wrote:
> On Tue, 2009-01-13 at 16:59 +0100, Michael Segel wrote:
> > Yes. My point is that Derby isn't PostgresSQL and there are a lot of
> > internal factors that could cause the problems you are seeing. How database
> > engines handle varchars internally could be an issue when concerning
> > performance. In terms of languages, C is very different from Java. There are
> > things that you can do in C that you can't do in Java.
> > 
> > With respect to your problem, I believe that there is an issue with the
> > query optimizer. By cleaning up your table design the optimizer may make a
> > better selection. As a last resort, you then apply the hint.
> > 
> > What I am suggesting is that for testing purposes only, you create the table
> > that maps a varchar to an identity integer. Then for each row in your base
> > table, you insert a row in to a new table that stores the integer id instead
> > of the varchar. You then do the same for the relationship table.
> > 
> > When you re-run the query you can either join the table in the inner select,
> > or you could just hardcode the integer value instead of the varchar value.
> > 
> > Yes there is minimal cost for the join, but you're talking milliseconds.
> > Weigh that against the time spent on a sequential scan of 1.5million +
> > records...
> 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...


View raw message