Exemple #1
0
        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;
        }
Exemple #2
0
        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;
        }