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);
}
});
});
}