[feature] some optimization about Execute Script

[fuchanghai]

你的功能要求与某个问题相关吗？ the interface path is “/data-provider/execute/test” Data View Module

When I only use sql statement to query, and the data volume is several million, the backend execution is very slow and an error will be reported. It is found that a layer of temporary table DATART_VTABLE will be wrapped and then paginated

接口地址是 “/data-provider/execute/test” 数据视图模块 当我仅仅使用sql语句查询时，数据量几百万时，后端执行很慢，还会报错。发现会包一层临时表DATART_VTABLE 然后再分页

形容一下你想要的解决方案

When it is found that the front end only transmits sql statements, do not include a layer of temporary table DATART_VTABLE 当发现前端仅传sql时 不要包一层临时表DATART_VTABLE

描述一下你考虑过的备选方案 清晰简洁地描述任意你考虑过的备选方案和功能

其他内容 能帮忙看看会不会对其他功能有影响吗 Can you help to see if it will affect other functions?

It has been tested and seem like has no effect on the data view module. Modify the code as follows 已测试好像对数据视图模块无影响。修改代码如下

• Modify the build method of the SqlBuilder class

 public String build() throws SqlParseException {

        if (executeParam == null) {
            return srcSql;
        }

        final SqlNodeList selectList = new SqlNodeList(SqlParserPos.ZERO);

        final SqlNodeList orderBy = new SqlNodeList(SqlParserPos.ZERO);

        final SqlNodeList groupBy = new SqlNodeList(SqlParserPos.ZERO);

        SqlNode where = null;

        SqlNode having = null;

        HashMap<String, String> columnAlias = new HashMap<>();

        //function columns
        if (!CollectionUtils.isEmpty(executeParam.getFunctionColumns())) {
            for (FunctionColumn functionColumn : executeParam.getFunctionColumns()) {
                functionColumnMap.put(functionColumn.getAlias(), parseSnippet(functionColumn, T, true));
            }
        }

        //columns
        if (!CollectionUtils.isEmpty(executeParam.getColumns())) {
            for (String column : executeParam.getColumns()) {
                if (functionColumnMap.containsKey(column)) {
                    selectList.add(SqlNodeUtils.createAliasNode(functionColumnMap.get(column), column));
                } else {
                    selectList.add(SqlNodeUtils.createAliasNode(SqlNodeUtils.createSqlIdentifier(column, T), column));
                }
            }
        }

        // filters
        if (!CollectionUtils.isEmpty(executeParam.getFilters())) {
            for (FilterOperator filter : executeParam.getFilters()) {
                SqlNode filterSqlNode = filterSqlNode(filter);
                if (filter.getAggOperator() != null) {
                    if (having == null) {
                        having = filterSqlNode;
                    } else {
                        having = new SqlBasicCall(SqlStdOperatorTable.AND, new SqlNode[]{having, filterSqlNode}, SqlParserPos.ZERO);
                    }
                } else {
                    if (where == null) {
                        where = filterSqlNode;
                    } else {
                        where = new SqlBasicCall(SqlStdOperatorTable.AND, new SqlNode[]{where, filterSqlNode}, SqlParserPos.ZERO);
                    }
                }
            }
        }

        //group by
        if (!CollectionUtils.isEmpty(executeParam.getGroups())) {
            for (GroupByOperator group : executeParam.getGroups()) {
                SqlNode sqlNode = null;
                if (functionColumnMap.containsKey(group.getColumn())) {
                    sqlNode = functionColumnMap.get(group.getColumn());
                    selectList.add(SqlNodeUtils.createAliasNode(sqlNode, group.getColumn()));
                } else {
                    sqlNode = SqlNodeUtils.createSqlIdentifier(group.getColumn(), T);
                    selectList.add(sqlNode);
                }
                groupBy.add(sqlNode);
            }
        }

        // aggregators
        if (!CollectionUtils.isEmpty(executeParam.getAggregators())) {
            for (AggregateOperator aggregator : executeParam.getAggregators()) {
                String alias;
                if (aggregator.getSqlOperator() == null) {
                    alias = aggregator.getColumn();
                } else {
                    alias = aggregator.getSqlOperator().name() + "(" + aggregator.getColumn() + ")";
                }
                columnAlias.put(aggregator.getColumn(), alias);
                selectList.add(createAggNode(aggregator.getSqlOperator(), aggregator.getColumn(), alias));
            }
        }

        //order
        if (!CollectionUtils.isEmpty(executeParam.getOrders())) {
            for (OrderOperator order : executeParam.getOrders()) {
//                String columnName = columnAlias.containsKey(order.getColumn()) ? columnAlias.get(order.getColumn()) : order.getColumn();
                orderBy.add(createOrderNode(order));
            }
        }

        //keywords
        SqlNodeList keywordList = new SqlNodeList(SqlParserPos.ZERO);
        if (!CollectionUtils.isEmpty(executeParam.getKeywords())) {
            for (SelectKeyword keyword : executeParam.getKeywords()) {
                keywordList.add(SqlLiteral.createSymbol(SqlSelectKeyword.valueOf(keyword.name()), SqlParserPos.ZERO));
            }
        }

        SqlNode from = new SqlBasicCall(SqlStdOperatorTable.AS
                , new SqlNode[]{new SqlFragment("(" + srcSql + ")"), new SqlIdentifier(T, SqlParserPos.ZERO.withQuoting(true))}
                , SqlParserPos.ZERO);

        if (selectList.size() == 0) {
            selectList.add(SqlIdentifier.star(SqlParserPos.ZERO));
        }

        SqlNode fetch = null;
        SqlNode offset = null;
        if (withPage && executeParam.getPageInfo() != null) {
            fetch = SqlLiteral.createExactNumeric(Math.min(executeParam.getPageInfo().getPageSize(), Integer.MAX_VALUE) + "", SqlParserPos.ZERO);
            offset = SqlLiteral.createExactNumeric(Math.min((executeParam.getPageInfo().getPageNo() - 1) * executeParam.getPageInfo().getPageSize(), Integer.MAX_VALUE) + "", SqlParserPos.ZERO);
        }
        if (onlySql()){
            SqlNode simpleSqlNode = SqlParserUtils.createParser(srcSql, this.dialect).parseQuery();
            SqlPrettyWriter sqlPrettyWriter = new SqlPrettyWriter(this.dialect);
            sqlPrettyWriter.startList(SqlWriter.FrameTypeEnum.SELECT);
            sqlPrettyWriter.fetchOffset(fetch,offset);
            return SqlNodeUtils.toSql(simpleSqlNode, this.dialect, quoteIdentifiers)+sqlPrettyWriter.toSqlString();
        }else {

            SqlSelect sqlSelect = new SqlSelect(SqlParserPos.ZERO,
                    keywordList,
                    selectList,
                    from,
                    where,
                    groupBy.size() > 0 ? groupBy : null,
                    having,
                    null,
                    orderBy.size() > 0 ? orderBy : null,
                    offset,
                    fetch,
                    null);
            return SqlNodeUtils.toSql(sqlSelect, this.dialect, quoteIdentifiers);
        }
    }

• add a method called onlySql

  
  private boolean onlySql() {
      if (CollectionUtils.isEmpty(executeParam.getAggregators()) &&
              CollectionUtils.isEmpty( executeParam.getColumns()) &&
                      CollectionUtils.isEmpty(   executeParam.getFunctionColumns()) &&
                                      CollectionUtils.isEmpty(executeParam.getFilters() )&&
                                              CollectionUtils.isEmpty(  executeParam.getGroups() )&&
                                                      CollectionUtils.isEmpty( executeParam.getKeywords()) &&
                                                              CollectionUtils.isEmpty(executeParam.getOrders())){
          return true;
      }
      return false;
  }

[fuchanghai]

@scottsut

[fuchanghai]

@scottsut so do you agree with me? will my code affect other functions

[fuchanghai]

@scottsut If it's ok, can you assigned this issue to me ? i want to do some contributors for datart. do I need permission to submit a PR?

[tianlu-root]

你使用的什么数据库？去掉这里的子查询仅仅只能在编写SQL的时候在某些数据库上提升响应速度。 在可视化图表的查询中子查询是无法去掉的。另外你说的 数据量大时，还会报错 ，这个不能理解，具体报错原因是什么？

[fuchanghai]

@tianlu-root 在可视化图表的确是无法解决。

业务场景（Business scene）

• 数据库 MySQL （database : MySQL ）
• 数据量 500W+ （The amount of data is more than 500W）
• 不改造的SQL（select from (select from test) as 'DATART_VTABLE' limit 1000 offset 0）
• 出现的问题 ：响应慢，更大的数据会超时 (Problems: slow response or timeout)

改造后之后的sql （after change codes ）

select * from test limit 1000 offset 0 响应速度有提升 (Response speed improved)

我们也知道可视化图表中暂时无法解决，但是想在数据量大的情况下,数据视图模块能够响应，所以修改了这个代码. (We also know that the visual chart cannot be solved for the time being, but we want the data view module to respond when the amount of data is large, so we modified this code)

[tianlu-root]

了解了。目前最新的代码(dev分支)对 datart.data.provider.calcite.SqlBuilder 这个类有修改，你以上的修改目前已经不兼容了。你可以基于最新dev分支代码来修改。修改后可以发起PR到Dev分支。