search

exasol / exasol-virtual-schema

Virtual Schema from Exasol to Exasol
MIT License
1 stars 2 forks source link

Speedup reading of column metadata #91

Closed exaSR closed 3 months ago

exaSR commented 1 year ago

Using the default implementation of virtual-schema-jdbc for reading column metadata is very slow with Exasol. Reading all column data in a single operation and then matching them to tables locally should be a lot faster.

ckunki commented 1 year ago

Reading all column data in a single operation is only possible if all tables of a common schema should be included. In this case VSCJDBC could ask the JDBC driver 

this.connection.getMetaData().getColumns(catalogName, schemaName, "%", "%")

This is not possible if a table filter is specified, i.e. metadata should be retrieved only for a subset of all tables.

This in turn would limit the genericity of the feature requested by the current ticket and would raise the question whether the implementation effort is justified.

exaSR commented 1 year ago

It is also possible with table filter, but then the application has to do the filtering -- the resultset has a strictly defined order.

redcatbear commented 4 months ago

Talked to the PM. If we invest this kind of effort, then it would be in the Lua variant, because that is much better from the performance perspective.

redcatbear commented 4 months ago

Fun fact: after a quick check of the code of the Lua variant (which I wrote, but that was too long ago to remember this detail) it looks like the Lua variant uses that optimization already. Including the table filter: https://github.com/exasol/exasol-virtual-schema-common-lua/blob/c18cc4e70fe799c67d3d84597833df3299f30377/src/exasol/evscl/AbstractMetadataReader.lua#L179

redcatbear commented 3 months ago

Shelving.