db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Performance affected by adding a column?
Date Wed, 02 Jan 2008 18:53:40 GMT
Without a test case it is all guess work.  But if I had to guess I
would go along the lines that Stan outlines.  Key to understanding the
issue are 2 factors:
1) is a nullable column being added?
2) in case of nullable column how many rows are eventually updated to
    have a non-null value in the new column?

In the nullable column case no physical change happens to the table,
so I would expect exact same performance until updates of existing
rows happen.  Once updates happen then depending on the reserved space
on the table updates may cause rows which previously fit on one page
to now span multiple pages, usually referred to as long rows rather
than pagesplits.  Derby tries whenever possible at insert time to
not split a single column across multiple pages, and to not split rows
across multiple pages.  But after insert the row front of the row has
to stay where it was originally inserted and thus subsequent updates
that add space to the column/row can cause the row to span multiple

As Stan suggests running a full compress table will optimize the space
utililization of the table, and lay out the rows in contiguous space -
so if performance is critical it may be worth it to run this after
doing the bulk of the updates.


Stanley Bradbury wrote:
> Christian Rørdam wrote:
>> We have a Derby database version of size about 1.5 GB. Some 
>> people here say they have noticed a performance degradation after 
>> adding a new column to an existing table. The table has about 12 000 
>> rows.
>> Is it normal that performance will degrade after adding a new column? 
>> And if so, is there something special one should do when adding columns?
>> Christian Rørdam
>> Schlumberger OFS - IT Business Systems
> Hi Christian -
> My guess is the slowness has to do with space allocation in the 
> datapages of the existing records that were created before the column 
> was added.  Populating the column could cause many pagesplits.  You can 
> use COMPRESS_TABLE to rebuild the table, reload the pages and 
> re-establish free space in the datapages - my guess is it will help.

View raw message