View source | View content page | Page history | Printable version   

Projects:DBSM Performance Improvements/Technical Specifications



This project groups performance improvements in different DBSM tasks.


Here are listed the different areas to be improved.

Prevent table recreation

When updating database, there are some cases tables are recreated, this process is slow because requires not only to recreate the table preserving its data, but also to recreate all its indexes, which in case of big data volumes can be time consuming.

In 3.0PR15Q3, most of these recreations were prevented (see Table recreation prevention project). Though there are still some (not that common) cases when it is still required to recreate.

From those cases, these are the most common ones. No recreation will be optimized as possible depending on the actual RDBMS.

Old type New type PG ORA Comments
char nchar yes yes
varchar nvarchar yes yes
nchar char yes no
nvarchar varchar yes no
varchar(10) varchar(20) yes yes
char(10) char(20) yes yes
varchar text yes no
char text yes no
numeric(10,0) numeric(20,0) yes yes
numeric(10,0) numeric(10,5) no no
numeric(10,5) numeric(10,0) yes no
numeric numeric(10,0) yes no PG: As far as all data can be migrated
varchar(20) nvarchar(40) yes yes
varchar(20) nvarchar(40) + onCreateDefault yes yes
text varchar no no PG could support it depending on actual data, ORA doesn't support. Unlikely to happen so won't be implemented


Some processes can be improved by parallelizing their execution.

For all these cases:

Adding indexes on existing tables

New indexes added to existing tables can be parallelized. Because index creation is a heavy CPU operation (by DB processes), the improvement obtained by this parallelization can be very relevant in cases where multiple indexes are going to be created, specially if each of them is slow to create.

PostgreSQL: PL standardization

When importing PL code in PostgreSQL, it is gets automatically transformed from its actual definition in xml which is ORA compatible to PG specific PL. This process is heavy in CPU so it can get benefit of parallelization.

The improvement here will not be relevant when performing actual customers updates, but it will be significant while developing where typically many update.database and export.database commands are executed without big amounts of data but with all PL code present.

PostgreSQL: PL standardization check

When exporting PL code in PostgreSQL, it requires to be transformed from PL specific to ORA compatible before saving to xml. Correctness of this exportation is checked in a parallelizable manner.

This optimization, again, targets developers exporting database metadata.

Retrieved from ""

This page has been accessed 1,124 times. This page was last modified on 16 December 2016, at 12:12. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.