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