Replies: 8 comments 14 replies
-
|
Sure no problem, I can try it out on a production-sized codebase which uses SchemaCrawler tomorrow and report back =) |
Beta Was this translation helpful? Give feedback.
-
|
Sure, I am actually using the Java API in my gradle project, I will try it out. |
Beta Was this translation helpful? Give feedback.
-
|
@ltxlouis - in that case, I will release a new version of SchemaCrawler for you to try out with |
Beta Was this translation helpful? Give feedback.
-
|
@ltxlouis @GavinRay97 - please try SchemaCrawler 16.18.1 that I just released and let me know what you think. Thanks. |
Beta Was this translation helpful? Give feedback.
-
|
I have configured it like below: val datasource = AgroalDataSourceService.createDataSource(
jdbcUrl = "jdbc:mysql://localhost:3306/Chinook?user=root&password=Password123%23&allowMultiQueries=true",
)
System.setProperty("SC_EXPERIMENTAL", "true")
val dbConnSource = DatabaseConnectionSources.fromDataSource(datasource)
val schemaCrawlerOptions = DataConnectorService.mkSchemaCrawlerOptions(datasource.connection)
val schemaCrawlDurationMillis = measureTimeMillis {
SchemaCrawlerUtility.getCatalog(dbConnSource, schemaCrawlerOptions)
}
println("SchemaCrawlerCatalog took $schemaCrawlDurationMillis ms")The log output shows it is using multiple threads -- the runtime seems to drop from ~450ms previous to ~400ms 👍 |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, @GavinRay97! Sounds like we have a feature ready for the next major SchemaCrawler release. |
Beta Was this translation helpful? Give feedback.
-
|
I tested it on an Oracle db with 1600 tables and I managed to get from less than 1 minute to 35 seconds. I found that At first there was no much difference whether I set Then I added another taskRunner in private void retrieveTableColumnsFromMetadata(
final NamedObjectList<MutableTable> allTables,
final InclusionRuleFilter<Column> columnFilter,
final Set<NamedObjectKey> hiddenTableColumnsLookupKeys)
throws Exception {
SchemaInfoLevel minimum = SchemaInfoLevelBuilder.minimum();
RetrievalTaskRunner taskRunner = new RetrievalTaskRunner(minimum, 10);
for (final MutableTable table : allTables) {
taskRunner.add("metadataGetColumnFor" + table.getName(), () -> {
LOGGER.log(Level.FINE, "Retrieving table columns for " + table);
try (final Connection connection = getRetrieverConnection().getConnection();
final MetadataResultSet results =
new MetadataResultSet(
connection
.getMetaData()
.getColumns(
table.getSchema().getCatalogName(),
table.getSchema().getName(),
table.getName(),
null),
"DatabaseMetaData::getColumns");) {
while (results.next()) {
createTableColumn(results, allTables, columnFilter, hiddenTableColumnsLookupKeys);
}
} catch (final SQLException e) {
throw new WrappedSQLException(
String.format(
"Could not retrieve table columns for %s <%s>", table.getTableType(), table),
e);
}
});
}
taskRunner.submit();
taskRunner.stopAndLogTime();
} |
Beta Was this translation helpful? Give feedback.
-
|
@ltxlouis - thanks for this suggestion and research. This has been a problem with Oracle and has been reported by SchemaCrawler users. This was the reason that I introduced the data dictionary retrieval as an option. Now that I have support for multithreading, I can try out your approach. Stay tuned. |
Beta Was this translation helpful? Give feedback.
-
|
Sweet jesus, 1600 tables. That's quite a database! Great tip about the |
Beta Was this translation helpful? Give feedback.
-
|
Or wait a bit until I have a new release? 😄 |
Beta Was this translation helpful? Give feedback.
-
|
I had skimmed it and thought it was a config property for the builder -- whoops! |
Beta Was this translation helpful? Give feedback.
-
|
No worries! |
Beta Was this translation helpful? Give feedback.
-
|
@ltxlouis - I am getting flaky runs when I try to make the changes that you suggested. Sometimes builds succeed and sometimes they don't. I am almost at a point where I want to leave what I have as an experimental feature. Would you be able to try one more thing: do not make any changes to |
Beta Was this translation helpful? Give feedback.
-
|
Ok, I will try it. |
Beta Was this translation helpful? Give feedback.
-
|
I removed System.setProperty("SC_EXPERIMENTAL", "true");
System.setProperty("SC_WITHOUT_DATABASE_PLUGIN", "oracle");It took 00:18:07.045 hours |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, @ltxlouis. If I am reading this correctly, it took you 18 minutes versus one minute earlier? |
Beta Was this translation helpful? Give feedback.
-
|
Yes, same database, 1661 tables. schemacrawler-oracle unmodified00:05:11.841 hours .with(tableColumnsRetrievalStrategy, metadata)00:01:09.626 hours
|
Beta Was this translation helpful? Give feedback.
-
|
Thanks |
Beta Was this translation helpful? Give feedback.
-
|
@ltxlouis @GavinRay97 - clearing I have missed something in the multi-threaded code piece, since I am not getting consistently passing builds. I am getting flaky failures.
I will have to look harder to find what I have missed. If you see something clearly missed on the "master" branch, let me know as well. |
Beta Was this translation helpful? Give feedback.
-
|
Sure. Thank you for all the hard work. |
Beta Was this translation helpful? Give feedback.
-
|
@ltxlouis @GavinRay97 - I have released SchemaCrawler v16.18.2 with experimental multi-threading for table columns. Please take a look if you would like to try it out. I am still not fully happy with the multi-threading code, since it has flaky test failures. |
Beta Was this translation helpful? Give feedback.
-
|
Ok, I will give it a try. 👍 |
Beta Was this translation helpful? Give feedback.
-
@ltxlouis @GavinRay97 - would you like to test the new SchemaCrawler multi-threaded operation? I have made an early-access-release available. To use it, you would need to set
SC_EXPERIMENTALeither as an environmental variable or as a Java system property. I would love to have some early feedback, so please let me know if you try it out. Thanks!Beta Was this translation helpful? Give feedback.
All reactions