You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "hufukang (Jira)" <ji...@apache.org> on 2023/03/29 08:30:00 UTC
[jira] [Assigned] (CALCITE-5618) count() + left join execution efficiency optimize
[ https://issues.apache.org/jira/browse/CALCITE-5618?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
hufukang reassigned CALCITE-5618:
---------------------------------
Assignee: hufukang
> count() + left join execution efficiency optimize
> -------------------------------------------------
>
> Key: CALCITE-5618
> URL: https://issues.apache.org/jira/browse/CALCITE-5618
> Project: Calcite
> Issue Type: Task
> Reporter: hufukang
> Assignee: hufukang
> Priority: Major
>
> SQL uses the count() aggregate function and uses left join to associate other tables, the execution efficiency is very slow, and try to optimize the aggregation function operator pushdown, hoping to put forward relevant optimization suggestions.
>
> {code:java}
> // Test demo
> public class Test4 { public static void main(String[] args) throws Exception {
> Properties config = new Properties();
> config.setProperty("lex", "JAVA");
> config.setProperty("fun", "oracle");
> config.setProperty("caseSensitive", "false");
> config.setProperty("parserFactory","org.apache.calcite.sql.parser.impl.SqlParserImpl#FACTORY"); Connection connection = DriverManager.getConnection("jdbc:calcite:", config); // "jdbc:calcite:lex=JAVA"
> CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = calciteConnection.getRootSchema(); HikariDataSource dataSource3 = new HikariDataSource();
> dataSource3.setJdbcUrl("jdbc:oracle:thin:@192.168.75.38:1521:orcl");
> dataSource3.setUsername("ypmc");
> dataSource3.setPassword("ypmc");
> dataSource3.setDriverClassName("oracle.jdbc.OracleDriver");
> rootSchema.add("ypmc", JdbcSchema.create(rootSchema, "ypmc", dataSource3, null, null)); HikariDataSource dataSource4 = new HikariDataSource();
> dataSource4.setJdbcUrl("jdbc:oracle:thin:@192.168.75.38:1521:orcl");
> dataSource4.setUsername("TEST");
> dataSource4.setPassword("TEST");
> dataSource4.setDriverClassName("oracle.jdbc.OracleDriver");
> rootSchema.add("test", JdbcSchema.create(rootSchema, "test", dataSource4, null, null));
> String sql1 = "SELECT COUNT(*) FROM (SELECT ID, USER_CODE, USER_NAME FROM ypmc.T_D_RS_MEMORY) AS t LEFT JOIN test.RECORD u ON t.USER_CODE = u.PRODUCT_CODE"; long start = System.nanoTime();
> FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
> .parserConfig(
> SqlParser.config()
> .withLex(Lex.JAVA)
> .withCaseSensitive(false)
> )
> .defaultSchema(rootSchema)
> .build();
> // -------------------------------------------------------------
> Planner planner = Frameworks.getPlanner(frameworkConfig);
> final SqlNode sqlNode = planner.parse(sql1);
> final SqlNode validatedSql = planner.validate(sqlNode);
> RelRoot relRoot = planner.rel(validatedSql);
> RelNode relNode = relRoot.rel;
> VolcanoPlanner planners = (VolcanoPlanner) relNode.getCluster().getPlanner();
> // 获取期望的RelTraiset，这里是将Convention.None替换成EnumerableConvention
> RelTraitSet desired = relNode.getTraitSet().replace(EnumerableConvention.INSTANCE).simplify();
> // 设置根节点，会从根节点开始迭代将所有子节点也注册到planner中
> planners.setRoot(planners.changeTraits(relNode, desired));
> RelNode result = planners.chooseDelegate().findBestExp(); RelRunner runner = connection.unwrap(RelRunner.class);
> PreparedStatement ps = runner.prepareStatement(result);
> ResultSet resultSet = ps.executeQuery();
> long elapsedTime = TimeUnit.MILLISECONDS.convert(System.nanoTime() - start, TimeUnit.NANOSECONDS);
> long printStart = System.nanoTime();
> System.out.println("计算开始");
> printRs(resultSet);
> long elapsedTimes = TimeUnit.MILLISECONDS.convert(System.nanoTime() - printStart, TimeUnit.NANOSECONDS);
> System.out.println("执行时间" + elapsedTime);
> System.out.println("打印时间" + elapsedTimes);
> ps.close();
> connection.close();
> } public static void printRs(ResultSet rs) throws Exception {
> ResultSetMetaData rsmd = rs.getMetaData();
> int count = rsmd.getColumnCount(); for(int i = 1; i <= count; i++){
> System.out.print(rsmd.getColumnName(i)+"\t");
> }
> System.out.println(); while(rs.next()){
> for(int i = 1; i <= count; i++){
> System.out.print(rs.getString(i)+"\t");
> }
> System.out.println();
> }
> }
> } {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)