[
https://issues.apache.org/jira/browse/CALCITE-4439?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
wudejin updated CALCITE-4439:
-----------------------------
Issue Type: Bug (was: Improvement)
Priority: Blocker (was: Major)
> how to avoid query database column meta when I execute select query
> -------------------------------------------------------------------
>
> Key: CALCITE-4439
> URL: https://issues.apache.org/jira/browse/CALCITE-4439
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.21.0
> Reporter: wudejin
> Priority: Blocker
>
> I use calcite jdbc to query greenplum, code as follow：
> {code:java}
> Class.forName("org.apache.calcite.jdbc.Driver");
> Properties info = new Properties();
> info.setProperty("lex", "JAVA");
> Connection connection =
> DriverManager.getConnection("jdbc:calcite:", info);
> CalciteConnection calciteConnection =
> connection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = calciteConnection.getRootSchema();
> Class.forName("com.mysql.cj.jdbc.Driver");
> BasicDataSource dataSource = new BasicDataSource();
> dataSource.setUrl("jdbc:postgresql://192.168.125.3:5432/sit_cicada_0819");
> dataSource.setUsername(USER);
> dataSource.setPassword(PASSWORD);
> Schema schema = JdbcSchema.create(rootSchema, "cicada", dataSource, null,
> null);
> rootSchema.add("cicada", schema);
> calciteConnection.setSchema("cicada");
> Statement statement = calciteConnection.createStatement();
> ResultSet resultSet = statement.executeQuery("select zqsl, y_w, sspcs from
> t_rh_aj_zq_jtj where ssfj='WQFJ' and n_w = '2020'");
> print(resultSet);
> resultSet.close();
> statement.close();
> connection.close();
> {code}
> this query cost 20 seconds, but i use navicat to run above sql only took
> 0.041 s.
>
> I use arthas to analysis the code consumed time：
> {code:java}
> [arthas@35681]$ trace --skipJDKMethod false -n 1
> org.apache.calcite.sql.validate.SqlValidatorImpl validateNamespace
> Press Q or Ctrl+C to abort.
> Affect(class count: 2 , method count: 1) cost in 471 ms, listenerId:
> 16
> `---ts=2020-12-14
> 17:33:21;thread_name=http-nio-8888-exec-3;id=59;is_daemon=true;priority=5;TCCL=com.code.plugin.loader.PluginClassLoader@2e7620e1
> `---[14841.944034ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:validateNamespace()
> +---[14841.716027ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:validate() #1005
> | `---[14831.796817ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:validateNamespace()
> | +---[14831.624599ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:validate() #1005
> | | `---[0.458664ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:validateNamespace()
> | | +---[0.176725ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:validate() #1005
> | | `---[0.012963ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1006
> | +---[0.006568ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1006
> | +---[0.011847ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1007
> | +---[0.029899ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getType() #57
> | `---[0.04292ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:setValidatedNodeType() #57
> +---[0.011767ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1006
> +---[0.009111ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1007
> +---[0.030568ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getType() #57
> `---[0.014533ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:setValidatedNodeType() #57
> [arthas@35681]$ trace --skipJDKMethod false -n 1
> org.apache.calcite.sql.validate.SqlValidatorImpl validateNamespace
> Press Q or Ctrl+C to abort.
> Affect(class count: 2 , method count: 1) cost in 471 ms, listenerId:
> 16
> `---ts=2020-12-14
> 17:33:21;thread_name=http-nio-8888-exec-3;id=59;is_daemon=true;priority=5;TCCL=com.code.plugin.loader.PluginClassLoader@2e7620e1
> `---[14841.944034ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:validateNamespace()
> +---[14841.716027ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:validate() #1005
> | `---[14831.796817ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:validateNamespace()
> | +---[14831.624599ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:validate() #1005
> | | `---[0.458664ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:validateNamespace()
> | | +---[0.176725ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:validate() #1005
> | | `---[0.012963ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1006
> | +---[0.006568ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1006
> | +---[0.011847ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1007
> | +---[0.029899ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getType() #57
> | `---[0.04292ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:setValidatedNodeType() #57
> +---[0.011767ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1006
> +---[0.009111ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getNode() #1007
> +---[0.030568ms]
> org.apache.calcite.sql.validate.SqlValidatorNamespace:getType() #57
> `---[0.014533ms]
> org.apache.calcite.sql.validate.SqlValidatorImpl:setValidatedNodeType()
> #57[arthas@35681]$ trace --skipJDKMethod false -n 1
> org.apache.calcite.adapter.jdbc.JdbcSchema getRelDataType
> Press Q or Ctrl+C to abort.
> Affect(class count: 1 , method count: 2) cost in 305 ms, listenerId:
> 30
> `---ts=2020-12-14
> 17:57:34;thread_name=http-nio-8888-exec-6;id=5c;is_daemon=true;priority=5;TCCL=com.code.plugin.loader.PluginClassLoader@2e7620e1
> `---[17509.186064ms]
> org.apache.calcite.adapter.jdbc.JdbcSchema:getRelDataType()
> +---[0.379488ms] javax.sql.DataSource:getConnection() #360
> +---[0.019712ms] java.sql.Connection:getMetaData() #361
> +---[17506.071495ms]
> org.apache.calcite.adapter.jdbc.JdbcSchema:getRelDataType() #362
> | `---[17506.003388ms]
> org.apache.calcite.adapter.jdbc.JdbcSchema:getRelDataType()
> | +---[17503.628169ms] java.sql.DatabaseMetaData:getColumns() #371
> | +---[0.022319ms]
> org.apache.calcite.sql.type.SqlTypeFactoryImpl:<init>() #376
> | +---[0.018522ms]
> org.apache.calcite.rel.type.RelDataTypeFactory:builder() #378
> | +---[min=0.003107ms,max=0.00738ms,total=0.062983ms,count=18]
> java.sql.ResultSet:next() #379
> | +---[min=0.003663ms,max=0.00737ms,total=0.0689ms,count=17]
> java.sql.ResultSet:getString() #380
> | +---[min=0.002867ms,max=0.018199ms,total=0.065724ms,count=17]
> java.sql.ResultSet:getInt() #381
> | +---[min=0.003508ms,max=0.006388ms,total=0.064031ms,count=17]
> java.sql.ResultSet:getString() #382
> | +---[min=0.004086ms,max=0.020781ms,total=0.089992ms,count=17]
> org.apache.calcite.avatica.SqlType:valueOf() #385
> | +---[min=0.003254ms,max=0.013515ms,total=0.073793ms,count=17]
> org.apache.calcite.avatica.SqlType:ordinal() #57
> | +---[min=0.002897ms,max=0.005934ms,total=0.048287ms,count=15]
> java.sql.ResultSet:getInt() #392
> | +---[min=0.002859ms,max=0.005633ms,total=0.047827ms,count=15]
> java.sql.ResultSet:getInt() #393
> | +---[min=0.007364ms,max=0.035082ms,total=0.16188ms,count=17]
> org.apache.calcite.adapter.jdbc.JdbcSchema:sqlType() #397
> | +---[min=0.00291ms,max=0.018887ms,total=0.067839ms,count=17]
> java.sql.ResultSet:getInt() #398
> | +---[min=0.005913ms,max=0.026077ms,total=0.123944ms,count=17]
> org.apache.calcite.rel.type.RelDataTypeFactory$Builder:add() #399
> | +---[min=0.005282ms,max=0.019108ms,total=0.108095ms,count=17]
> org.apache.calcite.rel.type.RelDataTypeFactory$Builder:nullable() #57
> | +---[min=0.00309ms,max=0.005495ms,total=0.008585ms,count=2]
> java.sql.ResultSet:getInt() #388
> | +---[0.006726ms] java.sql.ResultSet:close() #401
> | +---[0.03192ms]
> org.apache.calcite.rel.type.RelDataTypeFactory$Builder:build() #402
> | `---[0.022148ms]
> org.apache.calcite.rel.type.RelDataTypeImpl:proto() #57
> `---[2.510182ms] org.apache.calcite.adapter.jdbc.JdbcSchema:close()
> #364
> {code}
> most of the time is spent on JdbcSchema.getRelDataType to getColumns，because
> my db contains five million columns.
> Is there any way to solve this problem or avoid this column query?
>
> Thanks!
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
