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... Cheers, Mikkel
