> -----Original Message----- > From: Mikkel Kamstrup Erlandsen [mailto:[email protected]] > Sent: Wednesday, January 14, 2009 12:12 AM > To: Derby Discussion > Cc: [email protected] > 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
