Loading schema objects for a large database is slow and takes a long time

[kdhrubo]

This is a continuation of issue #450

Tests on large Oracle9i databases over 3500 tables and many columns per table are slow and take forever to create schema cache if DB2Rest is the same network subnet. If DB2Rest is in a different region it takes even longer or sometimes can time out.

Investigations revealed this was due to inefficient generic queries used by OJDBC drivers to retrieve table and column information.

Options for optimization

1. Run DB2Rest in the same network region as DB
2. Use the INCLUDE_SCHEMA environment variable to limit the number of tables and columns loaded into the schema cache.

Other options to consider in the future

1. Use custom-optimized queries to retrieve schema information.
2. Load on demand (this will result in the initial first slow operation).

Reference - https://oracle-base.com/articles/9i/dbms_metadata

[kdhrubo]

How about loading schema info on demand in which case the first interaction with a database object can be slow. This can important optimization for multi db support.

[susanta-mukhopadhyay]

It will definitely work in case we do not need to engage majority of the tables in DB at one time.

[susanta-mukhopadhyay]

Another option may be combining the above two options together:

1. Run DB2Rest in the same network region as DB &
2. Use the INCLUDE_SCHEMA environment variable to limit the number of tables and columns loaded into the schema cache.

For multi DB connectivity, we may create separate schema files one by one.

Here also one open question is how we can support schema changes for large DB schema. Will it also need optimization?

[kdhrubo]

@MichalisDBA - Can you please help with a similar large schema for MySQL and PostgreSQL so that we can test and identify this as whether its just Oracle 9i bad SQL issue?

[kdhrubo]

Closing
As stated this was mostly an issue with the internal JDBC driver SQL.