You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "wudejin (Jira)" <ji...@apache.org> on 2020/12/16 06:42:00 UTC
[jira] [Created] (CALCITE-4439) how to avoid query database column
meta when I execute select query
wudejin created CALCITE-4439:
--------------------------------
Summary: 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: Improvement
Components: core
Affects Versions: 1.21.0
Reporter: wudejin
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)