So I have a rather large database where I want to show its metadata (schemas, tables, and columns) in a tree browser (I use Schemacrawler to fetch all the DB data, and JTree for the tree). However, because there are so many tables and columns, the TableColumnRetriever class takes ages to fetch the columns which causes a bottleneck in my implementation.
My idea is now to do lazy loading on the columns so they will only be fetched when a user clicks on a table. Is there a way in Schemacrawler to only fetch schemas and tables at the beginning (maybe set the SchemaInfoLevel to minimum?) and then later fetch the columns based on an input table/schema?
ps: I implement everything in Java.
SchemaCrawler does not have exactly the functionality you are looking for, to load do incremental loads of metadata. The reason is that SchemaCrawler builds an interconnected object model graph, where you can get from say a table to a foreign-key to another table using Java object references. However, there may be a couple of ways to address you issue that you can consider. One is that you can ask your end-users to provide you a description of what tables they are interested in, in the form of a regular expression. You can use SchemaCrawler’s powerful grep functionality to quickly retrieve that information. Another way is to take advantage of the fact that schemas do not change very often. You can cache the schema metadata when your application starts for the first time, using SchemaCrawler’s built-in functionality. If you end-user wants to refresh the schema at any time, they can do that with the understanding that it may take time. Otherwise, your application’s performance will be very good using cached data.
Sualeh Fatehi, SchemaCrawler