private Expression CostAnalysisAndTransform(SelectExpression selectExpression)
{
Expression joinGraph = selectExpression.Join;
Expression filterGraph = selectExpression.Filter;
// The required final ordering of the select expression if necessary.
// This is either the 'group by' ordering for an aggregate statement,
// or 'order by' if it's not an aggregate.
// Are we an aggregate?
Expression[] resultOrderExps;
bool[] resultOrderAsc;
Expression sortComposite = null;
bool aggregateExpression = false;
if (selectExpression.IsAggregated) {
// Yes, do we have group by clause?
int groupbyCount = selectExpression.GroupBy.Count;
resultOrderExps = new Expression[groupbyCount];
resultOrderAsc = new bool[groupbyCount];
for (int i = 0; i < groupbyCount; ++i) {
resultOrderExps[i] = selectExpression.GroupBy[i];
resultOrderAsc[i] = true; // All group by ordering is ascending
}
// Note the aggregate,
aggregateExpression = true;
} else {
// Not an aggregate statement, do we have a order by clause?
int orderbyCount = selectExpression.OrderBy.Count;
resultOrderExps = new Expression[orderbyCount];
resultOrderAsc = new bool[orderbyCount];
for (int i = 0; i < orderbyCount; ++i) {
resultOrderExps[i] = selectExpression.OrderBy[i].Expression;
resultOrderAsc[i] = selectExpression.OrderBy[i].IsAscending;
}
}
// The sort composite
if (resultOrderExps.Length > 0) {
sortComposite = FunctionExpression.Composite(resultOrderExps, resultOrderAsc);
}
// Create a new query transform object
QueryPlanner planner = new QueryPlanner(transaction);
planner.SetFilterGraph(filterGraph);
planner.SetJoinGraph(joinGraph);
planner.SetResultSortComposite(sortComposite);
Expression cheapResolution = planner.FindCheapResolution();
// If this is an aggregate query, we apply the aggregate filter to the
// query plan.
if (aggregateExpression) {
FilterExpression aggregateFilter =
new FilterExpression("aggregate", cheapResolution, sortComposite);
cheapResolution = aggregateFilter;
// Is there a having clause?
Expression havingExp = selectExpression.Having;
if (havingExp != null) {
FilterExpression havingFilter =
new FilterExpression("single_filter", cheapResolution, havingExp);
cheapResolution = havingFilter;
}
// Is there an order by clause?
int orderbyCount = selectExpression.OrderBy.Count;
if (orderbyCount > 0) {
Expression[] orderExps = new Expression[orderbyCount];
bool[] order_asc = new bool[orderbyCount];
for (int i = 0; i < orderbyCount; ++i) {
orderExps[i] = selectExpression.OrderBy[i].Expression;
order_asc[i] = selectExpression.OrderBy[i].IsAscending;
}
Expression aggrSortComposite = FunctionExpression.Composite(orderExps, order_asc);
FilterExpression sortFilter = new FilterExpression("sort", cheapResolution, aggrSortComposite);
cheapResolution = sortFilter;
}
}
// cheap_resolution is the best plan found, now add decoration such as
// filter terms, etc
int outCount = selectExpression.Output.Count;
FunctionExpression outFunction = new FunctionExpression("table_out");
for (int i = 0; i < outCount; ++i) {
outFunction.Parameters.Add(selectExpression.Output[i].Expression);
}
// Set the filter,
Expression outFilter = new FilterExpression("expression_table", cheapResolution, outFunction);
QueryCostModel costModel = new QueryCostModel(transaction);
costModel.ClearGraph(outFilter);
costModel.Cost(outFilter, Double.PositiveInfinity, new int[1]);
return outFilter;
}
private Expression CreateRandomQueryPlan(IList<QueryPredicate> expressions, IList<TableName> sourceList, IList<Expression> sourceListExps, Expression joinGraph)
{
Expression static_expression = null;
// For each input expression
foreach (QueryPredicate expr in expressions) {
// Find all static expressions (not dependant on terms in the current
// query context) and make a single static expression to resolve it.
int depend_on_count = expr.dependant_on.Count;
// No dependants
if (depend_on_count == 0) {
// Create the static expression if there is one,
static_expression = static_expression == null
? expr.expression
: new FunctionExpression("@and_sql", new Expression[] {static_expression, expr.expression});
}
// Mark up index information
MarkUpIndexCandidates(expr.expression);
// Mark up fact information
if (FactStatistics.CanBeFact(expr.expression)) {
Expression derefExp = QueryCostModel.DereferenceExpression(joinGraph, expr.expression);
if (derefExp != null) {
expr.expression.SetArgument("fact_id", FactStatistics.ToFactId(derefExp));
}
}
}
// Create the sort function
Expression sortFunction = null;
if (sortComposite != null) {
sortFunction = (Expression) sortComposite.Clone();
// Mark up any index information on the composite
MarkUpIndexCandidates(sortFunction);
}
// Create the cost model
QueryCostModel costModel = new QueryCostModel(transaction);
// The list of the best plans in the current iteration
List<PlanState> plans1 = new List<PlanState>();
int[] walkIteration = new int[1];
long plan_seed = DateTime.Now.Ticks; // Arbitary starting seed value
int cost_iterations = 0;
// Randomly schedule
for (int i = 0; i < 64; ++i) {
// Produce a random plan
IList<QueryPredicate> predOrder = ShufflePredicateOrder(expressions, 1.0d);
Expression result = ProduceRandomPlan(plan_seed, predOrder, sourceListExps, joinGraph, sortFunction,
static_expression);
// Cost it out
costModel.ClearGraph(result);
costModel.Cost(result, Double.PositiveInfinity, walkIteration);
++cost_iterations;
double currentCostTime = result.CostTime;
if (plans1.Count < 8 ||
plans1[plans1.Count - 1].cost > currentCostTime) {
// If it's a good plan, add it to the plan list
PlanState state1 = new PlanState(plan_seed, predOrder, currentCostTime);
int pos = plans1.BinarySearch(state1);
if (pos < 0) {
pos = -(pos + 1);
}
plans1.Insert(pos, state1);
}
// Ensure the list of good plans isn't more than 48
if (plans1.Count > 48) {
plans1.RemoveAt(plans1.Count - 1);
}
// Increment the plan seed
plan_seed += 500000;
}
// Now go through the list from the end to the start and shuffle the
// predicates. If a better plan is found we insert it back into the list.
for (int i = plans1.Count - 1; i >= 0; --i) {
int tryCount;
int graphMessChance;
if (i <= 2) {
tryCount = 32;
graphMessChance = 120;
} else if (i <= 3) {
tryCount = 32;
graphMessChance = 120;
} else if (i <= 5) {
tryCount = 24;
graphMessChance = 60;
} else if (i <= 8) {
tryCount = 18;
graphMessChance = 30;
} else if (i <= 16) {
tryCount = 18;
graphMessChance = 10;
} else {
tryCount = 12;
graphMessChance = 1;
}
int worse_plans_count = 0;
for (int n = 0; n < tryCount; ++n) {
PlanState curState = plans1[i];
plan_seed = curState.seed;
int bestI = System.Math.Min(plans1.Count - 1, 4);
double costToBeat = plans1[bestI].cost;
// Shuffle the predicate order of this plan
IList<QueryPredicate> predOrder = ShufflePredicateOrder(curState.predicate_order, 0.012d);
// 10% chance that we change the seed also,
if (i > 14 ||
new Random().Next(graphMessChance) == 0) {
plan_seed = plan_seed + 1;
}
Expression result = ProduceRandomPlan(plan_seed, predOrder, sourceListExps, joinGraph, sortFunction,
static_expression);
// Cost it out
costModel.ClearGraph(result);
costModel.Cost(result, costToBeat, walkIteration);
++cost_iterations;
if (result.IsCostSet) {
double currentCostTime = result.CostTime;
// If it's a better plan, feed it back into the list
if (currentCostTime < curState.cost &&
currentCostTime < costToBeat) {
// If it's a good plan, add it to the plan list
PlanState state1 = new PlanState(plan_seed, predOrder, currentCostTime);
// NOTE; this doesn't add the entry to the list if there exists
// an entry that's the same cost and seed.
int pos = plans1.BinarySearch(state1);
if (pos < 0) {
pos = -(pos + 1);
plans1.Insert(pos, state1);
++i;
}
} else {
if (currentCostTime > costToBeat) {
++worse_plans_count;
}
}
}
}
// Remove all plans down to i
for (int n = plans1.Count - 1; n >= System.Math.Max(i, 8); --n) {
plans1.RemoveAt(n);
}
}
// Make up the best plan from the seed,
PlanState state = plans1[0];
Expression bestExp = ProduceRandomPlan(state.seed, state.predicate_order, sourceListExps, joinGraph, sortFunction,
static_expression);
// Make sure the cost information is correct for this graph before printing
// it out
costModel.ClearGraph(bestExp);
costModel.Cost(bestExp, Double.PositiveInfinity, new int[1]);
return bestExp;
}
