1

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.

Eli
  • 69
  • 1
  • 7

1 Answers1

1

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

Sualeh Fatehi
  • 4,700
  • 2
  • 24
  • 28
  • Thanks, Sualeh! I ended up fetching only the schemas and tables first by setting the SchemaInfoLevel to minimum, and then later fetch the columns of a specific table using setTableNamePattern() method from SCOptions (which is way faster than using TableInclusionRule, why is that?). And another thing, is there a way to turn off foreign keys retrieval? – Eli May 11 '17 at 09:27
  • I got that, I can modify the SchemaInfoLevel and set retrieveForeignKeys to false :-) – Eli May 11 '17 at 09:42
  • Eli, `setTableNamePattern()` filters on the server, `TableInclusionRule` filters on the client, so it is faster. However, in the process, you may lose some foreign key metadata. Turns out, you do not need foreign key metadata in your first pass, so you can use `setTableNamePattern()`. – Sualeh Fatehi May 11 '17 at 15:06