FWD » Core Development » Database

Startup of the server for an application with a lot of permanent tables is very slow. While this probably is not a big problem for production use (where the server is restarted rarely), it is very annoying for development use and debugging.

I believe the main reason for the long startup time is the many JDBC metadata queries we make to gather index information (one per table). A potential fix is to make a smaller number of queries (a single one, if possible), store the information temporarily during server startup, then discard the information to recover memory once the persistence framework has been configured.

I've done a little bit of research and trial and error testing. I've found that unfortunately, the JDBC DatabaseMetaData API for retrieving index information is not scalable in terms of querying metadata for multiple tables at once. The API requires a specific table name be passed; null and wildcards are not valid. So, the solution is not as simple as requesting a broader result set.

• defer the work of querying index info so that it is done lazily, on demand;
• multi-thread the startup, so that multiple metadata queries can be executed simultaneously;
• access the information directly from the database using system table queries.

None of these options is very attractive. Doing the work lazily would involve refactoring quite a bit of code. Multi-threading the startup may be the cleanest option, though the benefit will depend heavily on the number of CPUs and I/O throughput available. Accessing the information from system tables is IMO the least attractive option, in that it is complicated, dialect-specific, and fragile across database versions.

I will look a bit more into the impact/effort/risk of refactoring the code to implement the lazy option and the multi-threading option. I am avoiding the system table option for now.

We have redundant index metadata queries coming from DMOIndex$DMOMetadata.collectIndexData and DirtyTempTableHelper.getIndexes. The latter needs to work with a copy, because it modifies the indexes to work with the dirty database manager, but we probably can reroute the call to collect the initial metadata to leverage the cache DMOIndex maintains of this information, then make copies of the P2JIndex objects that come back, and modify those copies. The copy work may still take some time, but we will avoid many round trips to the database, which has to provide a significant benefit to server startup time.