private ITableSource OptimizeSubQuery(ITableSource source, bool optimizeWhere, bool allColumns,
bool isApplySupported, bool optimizeValues, bool optimizeColumns, IJoinedTable joinedTable = null)
{
source.Joins.ForEach(
node =>
{
var jt = node.Value;
var table = OptimizeSubQuery(
jt.Table,
jt.JoinType == EJoinType.Inner || jt.JoinType == EJoinType.CrossApply ||
jt.JoinType == EJoinType.Left,
false,
isApplySupported,
jt.JoinType == EJoinType.Inner || jt.JoinType == EJoinType.CrossApply ||
jt.JoinType == EJoinType.Left,
optimizeColumns,
jt
);
if (table != jt.Table)
{
var sql = jt.Table.Source as ISelectQuery;
if (sql != null && sql.OrderBy.Items.Count > 0)
{
foreach (var item in sql.OrderBy.Items)
{
_selectQuery.OrderBy.Expr(item.Expression, item.IsDescending);
}
}
jt.Table = table;
}
});
return(source.Source is ISelectQuery
? RemoveSubQuery(source, optimizeWhere, allColumns&& !isApplySupported, optimizeValues, optimizeColumns,
joinedTable)
: source);
}
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 OptimizeApply(ITableSource tableSource, IJoinedTable joinTable, bool isApplySupported,
bool optimizeColumns)
{
var joinSource = joinTable.Table;
joinSource.Joins.ForEach(
joinNode =>
{
var join = joinNode.Value;
if (join.JoinType == EJoinType.CrossApply || join.JoinType == EJoinType.OuterApply)
{
OptimizeApply(joinSource, join, isApplySupported, optimizeColumns);
}
});
if (isApplySupported && !joinTable.CanConvertApply)
{
return;
}
if (joinSource.Source.ElementType == EQueryElementType.SqlQuery)
{
var sql = (ISelectQuery)joinSource.Source;
var isAgg = sql.Select.Columns.Any(c => IsAggregationFunction(c.Expression));
if (isApplySupported && (isAgg || sql.Select.TakeValue != null || sql.Select.SkipValue != null))
{
return;
}
sql.Where.Search.Conditions.Clear();
if (!ContainsTable(tableSource.Source, sql))
{
joinTable.JoinType = joinTable.JoinType == EJoinType.CrossApply
? EJoinType.Inner
: EJoinType.Left;
joinTable.Condition.Conditions.AddRange(sql.Where.Search.Conditions);
}
else
{
sql.Where.Search.Conditions.AddRange(sql.Where.Search.Conditions);
var table = OptimizeSubQuery(
joinTable.Table,
joinTable.JoinType == EJoinType.Inner || joinTable.JoinType == EJoinType.CrossApply,
joinTable.JoinType == EJoinType.CrossApply,
isApplySupported,
joinTable.JoinType == EJoinType.Inner || joinTable.JoinType == EJoinType.CrossApply,
optimizeColumns);
if (table != joinTable.Table)
{
var q = joinTable.Table.Source as ISelectQuery;
if (q != null && q.OrderBy.Items.Count > 0)
{
foreach (var item in q.OrderBy.Items)
{
_selectQuery.OrderBy.Expr(item.Expression, item.IsDescending);
}
}
joinTable.Table = table;
OptimizeApply(tableSource, joinTable, isApplySupported, optimizeColumns);
}
}
}
else
{
if (!ContainsTable(tableSource.Source, joinSource.Source))
{
joinTable.JoinType = joinTable.JoinType == EJoinType.CrossApply
? EJoinType.Inner
: EJoinType.Left;
}
}
}