db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Performance deleting lots of rows from a table
Date Wed, 14 Jan 2009 12:08:02 GMT
Monica Ferrero wrote:
> Hi!
> I need to delete a considerable number of rows from a table, and it is 
> taking too long.  The operation takes equally long from ij, so I think 
> it is independent of my application code.
> So for instance in the case below, it took about 23 seconds to execute 
> the delete statement, which was deleting 156112 rows out of 380600:

Hello Monica,

Just a general observation.
After you delete that many rows from a table, you should regenerate the 
statistics. If the statistics are outdated, the optimizer might take bad 
decisions regarding the query plan.

You can do this by compressing the table, or dropping and recreating the 
index. Work is also going on to make the statistics update automatic.

Regarding your delete, you can check the query plan to see if it is 
doing a table scan or using the index. You should also see how your IO 
system is taking the load. For instance, you may see improved 
performance by putting the log on a separate device.


[ snip ]

View raw message