private void OptimizeColumns() { _selectQuery.Select.Walk( false, expr => { var query = expr as ISelectQuery; if (query != null && query.From.Tables.Count == 0 && query.Select.Columns.Count == 1) { QueryVisitor.FindOnce <ISelectQuery>(query.Select.Columns[0].Expression).ForEach( node => { if (node.Value.ParentSelect == query) { node.Value.ParentSelect = query.ParentSelect; } }); return(query.Select.Columns[0].Expression); } return(expr); }); }
private static bool CheckColumn(IColumn column, IQueryExpression expr, ISelectQuery query, bool optimizeValues, bool optimizeColumns) { if (expr is ISqlField || expr is IColumn || expr is ISqlParameter) { return(false); } var sqlValue = expr as ISqlValue; if (sqlValue != null) { return(!optimizeValues && 1.Equals(sqlValue.Value)); } var sqlBinaryExpression = expr as ISqlBinaryExpression; if (sqlBinaryExpression != null) { var e = sqlBinaryExpression; var expr1 = e.Expr1 as ISqlValue; if (e.Operation == "*" && expr1 != null) { if (expr1.Value is int && (int)expr1.Value == -1) { return(CheckColumn(column, e.Expr2, query, optimizeValues, optimizeColumns)); } } } if (optimizeColumns && QueryVisitor.FindFirstOrDefault <ISelectQuery>(expr, IsAggregationFunction) == null) { var q = query.ParentSelect ?? query; var count = QueryVisitor.FindOnce <IColumn>(q).Count(e => e == column); return(count > 2); } return(true); }
private void FinalizeAndValidateInternal(bool isApplySupported, bool optimizeColumns, HashSet <ISqlTableSource> tables) { OptimizeSearchCondition(_selectQuery.Where.Search); OptimizeSearchCondition(_selectQuery.Having.Search); QueryVisitor.FindOnce <IJoinedTable>(_selectQuery).ForEach( joinTable => { OptimizeSearchCondition(joinTable.Value.Condition); }); QueryVisitor.FindDownTo <ISelectQuery>(_selectQuery).ForEach( node => { var query = node.Value; if (query == _selectQuery) { return; } query.ParentSelect = _selectQuery; new SelectQueryOptimizer(_flags, query).FinalizeAndValidateInternal(isApplySupported, optimizeColumns, tables); if (query.IsParameterDependent) { _selectQuery.IsParameterDependent = true; } }); ResolveWeakJoins(tables); OptimizeColumns(); OptimizeApplies(isApplySupported, optimizeColumns); OptimizeSubQueries(isApplySupported, optimizeColumns); OptimizeApplies(isApplySupported, optimizeColumns); }
public void FinalizeAndValidate(bool isApplySupported, bool optimizeColumns) { OptimizeUnions(); FinalizeAndValidateInternal(isApplySupported, optimizeColumns, new HashSet <ISqlTableSource>()); QueryVisitor.FindOnce <ISelectQuery>(_selectQuery).ForEach( node => { var item = node.Value; if (item != _selectQuery) { RemoveOrderBy(item); } }); ResolveFields(); _selectQuery.SetAliases(); //#if DEBUG // sqlText = _selectQuery.SqlText; //#endif }
private ITableSource RemoveSubQuery(ITableSource childSource, bool concatWhere, bool allColumns, bool optimizeValues, bool optimizeColumns, IJoinedTable parentJoin) { var query = (ISelectQuery)childSource.Source; var isQueryOK = query.From.Tables.Count == 1; isQueryOK = isQueryOK && (concatWhere || query.Where.IsEmpty && query.Having.IsEmpty); isQueryOK = isQueryOK && !query.HasUnion && query.GroupBy.IsEmpty && !query.Select.HasModifier; //isQueryOK = isQueryOK && (_flags.IsDistinctOrderBySupported || query.Select.IsDistinct ); if (!isQueryOK) { return(childSource); } var isColumnsOK = (allColumns && !query.Select.Columns.Any(c => IsAggregationFunction(c.Expression))) || !query.Select.Columns.Any( c => CheckColumn(c, c.Expression, query, optimizeValues, optimizeColumns)); if (!isColumnsOK) { return(childSource); } var top = _selectQuery; while (top.ParentSelect != null) { top = top.ParentSelect; } var columns = new HashSet <IColumn>(query.Select.Columns); top.Walk( false, expr => { var col = expr as IColumn; if (col == null || !columns.Contains(col)) { return(expr); } return(col.Expression); }); QueryVisitor.FindOnce <IInList>(top).ForEach( node => { if (node.Value.Expr1 == query) { node.Value.Expr1 = query.From.Tables.First.Value; } }); childSource.Joins.ForEach(node => query.From.Tables.First.Value.Joins.AddLast(node.Value)); if (query.From.Tables.First.Value.Alias == null) { query.From.Tables.First.Value.Alias = childSource.Alias; } if (!query.Where.IsEmpty) { if (parentJoin != null && parentJoin.JoinType == EJoinType.Left) { ConcatSearchCondition(parentJoin.Condition, query.Where.Search); } else { ConcatSearchCondition(_selectQuery.Where.Search, query.Where.Search); } } if (!query.Having.IsEmpty) { ConcatSearchCondition(_selectQuery.Having.Search, query.Having.Search); } QueryVisitor.FindOnce <ISelectQuery>(top).ForEach( node => { if (node.Value.ParentSelect == query) { node.Value.ParentSelect = query.ParentSelect ?? _selectQuery; } }); return(query.From.Tables.First.Value); }
private void OptimizeUnions() { var exprs = new Dictionary <IQueryExpression, IQueryExpression>(); QueryVisitor.FindOnce <ISelectQuery>(_selectQuery).ForEach( elem => { var element = elem.Value; if (element.From.Tables.Count != 1 || !element.IsSimple || element.IsInsert || element.IsUpdate || element.IsDelete) { return; } var table = element.From.Tables.First.Value; var selectQuery = table.Source as ISelectQuery; if (table.Joins.Count != 0 || selectQuery == null) { return; } if (!selectQuery.HasUnion) { return; } var isContinue = false; for (var i = 0; i < element.Select.Columns.Count; i++) { var scol = element.Select.Columns[i]; var ucol = selectQuery.Select.Columns[i]; if (scol.Expression != ucol) { isContinue = true; break; } } if (isContinue) { return; } exprs.Add(selectQuery, element); for (var i = 0; i < element.Select.Columns.Count; i++) { var scol = element.Select.Columns[i]; var ucol = selectQuery.Select.Columns[i]; scol.Expression = ucol.Expression; scol.Alias = ucol.Alias; exprs.Add(ucol, scol); } for (var i = element.Select.Columns.Count; i < selectQuery.Select.Columns.Count; i++) { element.Select.Expr(selectQuery.Select.Columns[i].Expression); } element.From.Tables.Clear(); selectQuery.From.Tables.ForEach(node => element.From.Tables.AddLast(node.Value)); element.Where.Search.Conditions.AddRange(selectQuery.Where.Search.Conditions); element.Having.Search.Conditions.AddRange(selectQuery.Having.Search.Conditions); selectQuery.GroupBy.Items.ForEach(node => element.GroupBy.Items.AddLast(node.Value)); element.OrderBy.Items.AddRange(selectQuery.OrderBy.Items); selectQuery.Unions.Last.ReverseEach(node => element.Unions.AddFirst(node.Value)); }); _selectQuery.Walk( false, expr => { IQueryExpression e; if (exprs.TryGetValue(expr, out e)) { return(e); } return(expr); }); }
internal void ResolveWeakJoins(HashSet <ISqlTableSource> tables) { Func <ITableSource, bool> findTable = null; findTable = table => { if (tables.Contains(table.Source)) { return(true); } var result = table.Joins.ApplyUntilNonDefaultResult( node => { if (findTable(node.Value.Table)) { node.Value.IsWeak = false; return(true); } return(false); }); if (result) { return(true); } var selectQuery = table.Source as ISelectQuery; return(selectQuery != null && selectQuery.From.Tables.Any(t => findTable(t))); }; var areTablesCollected = false; QueryVisitor.FindOnce <ITableSource>(_selectQuery).ForEach( item => { var table = item.Value; table.Joins.ForEach( node => { var join = node.Value; if (!join.IsWeak) { return; } if (!areTablesCollected) { areTablesCollected = true; var items = new LinkedList <IQueryElement>(); items.AddLast(_selectQuery.Select); items.AddLast(_selectQuery.Where); items.AddLast(_selectQuery.GroupBy); items.AddLast(_selectQuery.Having); items.AddLast(_selectQuery.OrderBy); if (_selectQuery.IsInsert) { items.AddLast(_selectQuery.Insert); } if (_selectQuery.IsUpdate) { items.AddLast(_selectQuery.Update); } if (_selectQuery.IsDelete) { items.AddLast(_selectQuery.Delete); } QueryVisitor.FindOnce <ISqlTable>(_selectQuery.From).ForEach( fromTable => { var tableArguments = fromTable.Value.TableArguments; if (tableArguments == null) { return; } items.AddRange(tableArguments); }); QueryVisitor.FindOnce <ISqlField>(items).ForEach( field => { tables.Add(field.Value.Table); }); } if (findTable(join.Table)) { join.IsWeak = false; } else { table.Joins.Remove(join); } }); }); }