[
https://issues.apache.org/jira/browse/DRILL-5216?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16793747#comment-16793747
]
Bernd Lunghamer commented on DRILL-5216:
----------------------------------------
JdbcStoragePlugin per default enumerates * all catalog
* all schemata
of the underlying JDBC connection no matter which catalog and schema the user
is connected to a connection level. That way we are experiencing queries that
take 2 second in oracle without metadata discovery on a connection level to
take 3 minutes because all schema information is requested in every query. An
we have a weak network link (roundtrip latencies).
I patched JdbcStorage Plugin with a configuration option to filter schemata and
catalog to those of the underlying connection getSchema() vs. getSchemas(),
getCatalog() vs. getCatalog(). Would there be any interest do merge this
upstream?
> Make use of FetchSize in JDBC storage plugin
> --------------------------------------------
>
> Key: DRILL-5216
> URL: https://issues.apache.org/jira/browse/DRILL-5216
> Project: Apache Drill
> Issue Type: Improvement
> Components: Storage - JDBC
> Affects Versions: 1.9.0
> Environment: drill-embedded on ubuntu client - connected to a remote
> Oracle
> Reporter: Thomas Bünger
> Priority: Minor
>
> For remotely located database servers it usually is preferred to increase the
> fetch size for selected or all queries to limit number of roundtrips.
> This could either be controlled via plugin config or on a query basis via
> comments.
> One has also to take metadata retrieval into account, as in larger scenarios
> - as in mine - the Oracle cluster hosts thousands of schemas and the small
> fetchsize results in hundres of individual roundtrips.
> In the end every Drill query against this storage takes at least a minute
> justs for querying the metadata.
> Analysis so far for metadata-retrieving SQL calls:
> So far, Drill is using the JDBC metadata API
> {{java.sql.DatabaseMetaData.getSchemas()}} inside JdbcStoragePlugin.java and
> could set an appropriate fetchsize before iterating the result set.
> I've tested this locally and improved latency a lot, but am note sure how
> this affects other non-oracle JDBC drivers.
> The other (potentially long) query is the table enumeration.
> From what I've seen is Drill not calling the JDBC driver directly, but goes
> through apache.calcite calling {{getTableNames()}} which under the hood calls
> {{java.sql.DatabaseMetaData.getTables()}} and also contributes to slow
> metadata retrieval due to small default fetch size.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
