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 吴 德金 <[email protected]> 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!
