private static bool ContainsTable(ISqlTableSource table, IQueryElement sql) { return(null != QueryVisitor.FindFirstOrDefault <IQueryExpression>( sql, e => e == table || e.ElementType == EQueryElementType.SqlField && table == ((ISqlField)e).Table || e.ElementType == EQueryElementType.Column && table == ((IColumn)e).Parent)); }
private static QueryData GetQueryData(ISelectQuery selectQuery) { var data = new QueryData { Query = selectQuery }; QueryVisitor.FindParentFirst( selectQuery, e => { switch (e.ElementType) { case EQueryElementType.SqlField: { var field = (ISqlField)e; if (field.Name.Length != 1 || field.Name[0] != '*') { data.Fields.Add(field); } break; } case EQueryElementType.SqlQuery: { if (e != selectQuery) { data.Queries.Add(GetQueryData((ISelectQuery)e)); return(null); } break; } case EQueryElementType.Column: return(((IColumn)e).Parent == selectQuery ? e : null); case EQueryElementType.SqlTable: return(null); } return(e); }); return(data); }
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 static void ResolveFields(QueryData data) { if (data.Queries.Count == 0) { return; } var dic = new Dictionary <IQueryExpression, IQueryExpression>(); foreach (ISqlField field in data.Fields) { if (dic.ContainsKey(field)) { continue; } var found = false; foreach (var table in data.Query.From.Tables) { found = FindField(field, table) != null; if (found) { break; } } if (!found) { var expr = GetColumn(data, field); if (expr != null) { dic.Add(field, expr); } } } if (dic.Count > 0) { QueryVisitor.FindParentFirst( data.Query, e => { IQueryExpression ex; switch (e.ElementType) { case EQueryElementType.SqlQuery: return(e == data.Query ? e : null); case EQueryElementType.SqlFunction: { var parms = ((ISqlFunction)e).Parameters; for (var i = 0; i < parms.Length; i++) { if (dic.TryGetValue(parms[i], out ex)) { parms[i] = ex; } } break; } case EQueryElementType.SqlExpression: { var parms = ((ISqlExpression)e).Parameters; for (var i = 0; i < parms.Length; i++) { if (dic.TryGetValue(parms[i], out ex)) { parms[i] = ex; } } break; } case EQueryElementType.SqlBinaryExpression: { var expr = (ISqlBinaryExpression)e; if (dic.TryGetValue(expr.Expr1, out ex)) { expr.Expr1 = ex; } if (dic.TryGetValue(expr.Expr2, out ex)) { expr.Expr2 = ex; } break; } case EQueryElementType.ExprPredicate: case EQueryElementType.NotExprPredicate: case EQueryElementType.IsNullPredicate: case EQueryElementType.InSubQueryPredicate: { var expr = (IExpr)e; if (dic.TryGetValue(expr.Expr1, out ex)) { expr.Expr1 = ex; } break; } case EQueryElementType.ExprExprPredicate: { var expr = (IExprExpr)e; if (dic.TryGetValue(expr.Expr1, out ex)) { expr.Expr1 = ex; } if (dic.TryGetValue(expr.Expr2, out ex)) { expr.Expr2 = ex; } break; } case EQueryElementType.LikePredicate: { var expr = (ILike)e; if (dic.TryGetValue(expr.Expr1, out ex)) { expr.Expr1 = ex; } if (dic.TryGetValue(expr.Expr2, out ex)) { expr.Expr2 = ex; } if (dic.TryGetValue(expr.Escape, out ex)) { expr.Escape = ex; } break; } case EQueryElementType.HierarhicalPredicate: { var expr = (IHierarhicalPredicate)e; if (dic.TryGetValue(expr.Expr1, out ex)) { expr.Expr1 = ex; } if (dic.TryGetValue(expr.Expr2, out ex)) { expr.Expr2 = ex; } break; } case EQueryElementType.BetweenPredicate: { var expr = (IBetween)e; if (dic.TryGetValue(expr.Expr1, out ex)) { expr.Expr1 = ex; } if (dic.TryGetValue(expr.Expr2, out ex)) { expr.Expr2 = ex; } if (dic.TryGetValue(expr.Expr3, out ex)) { expr.Expr3 = ex; } break; } case EQueryElementType.InListPredicate: { var expr = (IInList)e; if (dic.TryGetValue(expr.Expr1, out ex)) { expr.Expr1 = ex; } for (var i = 0; i < expr.Values.Count; i++) { if (dic.TryGetValue(expr.Values[i], out ex)) { expr.Values[i] = ex; } } break; } case EQueryElementType.Column: { var expr = (IColumn)e; if (expr.Parent != data.Query) { return(null); } if (dic.TryGetValue(expr.Expression, out ex)) { expr.Expression = ex; } break; } case EQueryElementType.SetExpression: { var expr = (ISetExpression)e; if (dic.TryGetValue(expr.Expression, out ex)) { expr.Expression = ex; } break; } case EQueryElementType.GroupByClause: { var expr = (IGroupByClause)e; expr.Items.ForEach( node => { if (dic.TryGetValue(node.Value, out ex)) { node.Value = ex; } }); break; } case EQueryElementType.OrderByItem: { var expr = (IOrderByItem)e; if (dic.TryGetValue(expr.Expression, out ex)) { expr.Expression = ex; } break; } } return(e); }); } foreach (var query in data.Queries) { if (query.Queries.Count > 0) { ResolveFields(query); } } }
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); } }); }); }