You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by 吴 德金 <wu...@hotmail.com> on 2020/12/16 06:12:35 UTC
how to avoid query database column meta when I execute select query？
hello everyone：
I use calcite jdbc to query greenplum, code as follow：
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();
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：
[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
most of the time is spent on JdbcSchema.getRelDataType to getColumns，because my db have five million columns.
Is there any way to solve this problem or avoid this column query?
Thanks!
Re: how to avoid query database column meta when I execute select query？
Posted by Julian Hyde <jh...@apache.org>.
Maybe the JDBC adapter needs a new mode that will read metadata more
conservatively. People could use it for certain classes of large
schemas, such as this one with 5 million columns. Maybe additional
modes would be needed for other kinds of large schemas (e.g. one with
lots of tables but few columns per table).
On Wed, Dec 16, 2020 at 12:00 AM 吴 德金 <wu...@hotmail.com> wrote:
>
> hello everyone：
> I use calcite jdbc to query greenplum, code as follow：
>
> 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();
>
> 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：
>
> [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
>
> most of the time is spent on JdbcSchema.getRelDataType to getColumns，because my db have five million columns.
>
>
> Is there any way to solve this problem or avoid this column query?
>
> Thanks!