Esempio n. 1
0
        protected override void OnExecute()
        {
            using (var scope = new DatabaseScope(context.Database.Name))
            {
                foreach (var env in context.HPartitioningDesignData.Environments)
                {
                    try
                    {
                        virtualHPartitioningsRepository.DestroyAll();
                        bool hPartitioningCreated = false;
                        try
                        {
                            var targetRelationData = context.RelationsData.GetReplacementOrOriginal(env.Partitioning.Relation.ID);
                            virtualHPartitioningsRepository.Create(dbObjectDefinitionGenerator.Generate(env.Partitioning.WithReplacedRelation(targetRelationData)));
                            hPartitioningCreated = true;
                        }
                        catch (Exception ex)
                        {
                            log.Write(ex);
                        }
                        if (hPartitioningCreated)
                        {
                            foreach (var queryPair in context.StatementsData.AllQueriesByRelation[env.Partitioning.Relation.ID])
                            {
                                var statementID = queryPair.NormalizedStatementID;
                                if (!env.StatementsEvaluation.ContainsKey(statementID))
                                {
                                    var            workloadStatement       = context.StatementsData.All[statementID];
                                    var            normalizedStatement     = workloadStatement.NormalizedStatement;
                                    var            representativeStatement = workloadStatement.RepresentativeStatistics.RepresentativeStatement;
                                    var            statementToUse          = RepresentativeStatementReplacementUtility.Provide(normalizedStatement, representativeStatement, context.RelationsData);
                                    var            realPlan      = context.RealExecutionPlansForStatements[statementID].Plan;
                                    IExplainResult explainResult = context.RealExecutionPlansForStatements[statementID];
                                    var            latestPlan    = realPlan;
                                    if (normalizedStatement.CommandType == DAL.Contracts.StatementQueryCommandType.Select ||
                                        normalizedStatement.CommandType == DAL.Contracts.StatementQueryCommandType.Insert)    // hypo pg currently does not support explain for delete/update for h partitioning
                                    {
                                        try
                                        {
                                            explainResult = explainRepository.Eplain(statementToUse);
                                            latestPlan    = explainResult.Plan;
                                        }
                                        catch (Exception ex)
                                        {
                                            log.Write(ex);
                                        }
                                    }

                                    VirtualEnvironmentStatementEvaluation statementEvaluation = new VirtualEnvironmentStatementEvaluation();
                                    statementEvaluation.ExecutionPlan = explainResult;
                                    decimal fromPrice = realPlan.TotalCost;
                                    decimal toPrice   = latestPlan.TotalCost;
                                    decimal divisor   = Math.Abs(Math.Max(fromPrice, toPrice));
                                    if (divisor > 0)
                                    {
                                        statementEvaluation.LocalImprovementRatio = ((toPrice - fromPrice) / divisor) * -1m;
                                    }
                                    decimal statementPortion = context.StatementsData.AllExecutionsCount > 0 ? workloadStatement.TotalExecutionsCount / (decimal)context.StatementsData.AllExecutionsCount : 0m;
                                    statementEvaluation.GlobalImprovementRatio = statementEvaluation.LocalImprovementRatio * statementPortion;
                                    env.StatementsEvaluation.Add(statementID, statementEvaluation);

                                    env.Evaluation.ImprovementRatio += statementEvaluation.GlobalImprovementRatio;
                                }
                            }
                            env.IsImproving = env.Evaluation.ImprovementRatio >= settings.HPartitioningMinTotalImprovementRatio;
                        }
                    }
                    finally
                    {
                        virtualHPartitioningsRepository.DestroyAll();
                    }
                }
            }
        }
        protected override void OnExecute()
        {
            context.IndicesDesignData.PossibleIndexSizes.Clear();
#if DEBUG
            int counter = 0;
#endif
            using (var scope = new DatabaseScope(context.Database.Name))
            {
                foreach (var env in context.IndicesDesignData.Environments)
                {
#if DEBUG
                    if (System.Diagnostics.Debugger.IsAttached)
                    {
                        ++counter;
                        if (counter % 10 == 0)
                        {
                            Console.WriteLine(((counter / (double)context.IndicesDesignData.Environments.Count) * 100).ToString("F2") + "%");
                        }
                    }
#endif
                    try
                    {
                        virtualIndicesRepository.DestroyAll();
                        var  virtualIndicesMapping = new Dictionary <IndexDefinition, IVirtualIndex>();
                        bool indicesCreated        = false;
                        try
                        {
                            foreach (var index in env.PossibleIndices.All)
                            {
                                var targetRelationData = context.RelationsData.GetReplacementOrOriginal(index.Relation.ID);
                                var virtualIndex       = virtualIndicesRepository.Create(dbObjectDefinitionGenerator.Generate(index.WithReplacedRelation(targetRelationData)));
                                if (virtualIndex != null)
                                {
                                    virtualIndicesMapping.Add(index, virtualIndex);
                                }
                                if (!context.IndicesDesignData.PossibleIndexSizes.ContainsKey(index))
                                {
                                    context.IndicesDesignData.PossibleIndexSizes.Add(index, virtualIndicesRepository.GetVirtualIndexSize(virtualIndex.ID));
                                }
                            }
                            indicesCreated = true;
                        }
                        catch (Exception ex)
                        {
                            log.Write(ex);
                        }
                        if (indicesCreated)
                        {
                            foreach (var kv in env.PossibleIndices.AllPerStatement)
                            {
                                var statementID             = kv.Key;
                                var indices                 = kv.Value;
                                var workloadStatement       = context.StatementsData.All[statementID];
                                var normalizedStatement     = workloadStatement.NormalizedStatement;
                                var representativeStatement = workloadStatement.RepresentativeStatistics.RepresentativeStatement;
                                var statementToUse          = RepresentativeStatementReplacementUtility.Provide(normalizedStatement, representativeStatement, context.RelationsData);
                                var explainResult           = explainRepository.Eplain(statementToUse);
                                var latestPlan              = explainResult.Plan;
                                var realPlan                = context.RealExecutionPlansForStatements[statementID].Plan;
                                HashSet <IndexDefinition>             improvingVirtualIndices = new HashSet <IndexDefinition>();
                                VirtualEnvironmentStatementEvaluation statementEvaluation     = new VirtualEnvironmentStatementEvaluation();
                                statementEvaluation.ExecutionPlan = explainResult;
                                decimal fromPrice = realPlan.TotalCost;
                                decimal toPrice   = latestPlan.TotalCost;
                                decimal divisor   = Math.Abs(Math.Max(fromPrice, toPrice));
                                if (divisor > 0)
                                {
                                    statementEvaluation.LocalImprovementRatio = ((toPrice - fromPrice) / divisor) * -1m;
                                }
                                decimal statementPortion = context.StatementsData.AllExecutionsCount > 0 ? workloadStatement.TotalExecutionsCount / (decimal)context.StatementsData.AllExecutionsCount : 0m;
                                statementEvaluation.GlobalImprovementRatio = statementEvaluation.LocalImprovementRatio * statementPortion;
                                env.StatementsEvaluation.Add(statementID, statementEvaluation);


                                foreach (var i in indices)
                                {
                                    var virtualIndex = virtualIndicesMapping[i];
                                    if (!env.IndicesEvaluation.ContainsKey(i))
                                    {
                                        env.IndicesEvaluation.Add(i, new VirtualIndicesEnvironmentIndexEvaluation());
                                    }
                                    if (explainResult.UsedIndexScanIndices.Contains(virtualIndex.Name))
                                    {
                                        statementEvaluation.UsedIndices.Add(i);
                                        statementEvaluation.AffectingIndices.Add(i);
                                        if (latestPlan.TotalCost < realPlan.TotalCost)
                                        {
                                            improvingVirtualIndices.Add(i);
                                        }
                                        env.IndicesEvaluation[i].ImprovementRatio += statementEvaluation.GlobalImprovementRatio;
                                    }
                                    else
                                    {
                                        if (normalizedStatement.CommandType == DAL.Contracts.StatementQueryCommandType.Insert ||
                                            normalizedStatement.CommandType == DAL.Contracts.StatementQueryCommandType.Update ||
                                            normalizedStatement.CommandType == DAL.Contracts.StatementQueryCommandType.Delete)
                                        {
                                            statementEvaluation.AffectingIndices.Add(i);
                                        }
                                    }
                                }
                                foreach (var query in normalizedStatement.StatementDefinition.IndependentQueries)
                                {
                                    var queryKey = new NormalizedStatementQueryPair(statementID, query);
                                    env.ImprovingPossibleIndices.TryAddPossibleIndices(improvingVirtualIndices, normalizedStatement, query);
                                    if (env.PossibleIndices.AllCoveringPerQuery.ContainsKey(queryKey))
                                    {
                                        env.ImprovingPossibleIndices.TryAddPossibleCoveringIndices(env.PossibleIndices.AllCoveringPerQuery[queryKey].Intersect(improvingVirtualIndices), normalizedStatement, query);
                                    }
                                }
                            }
                        }
                    }
                    finally
                    {
                        virtualIndicesRepository.DestroyAll();
                    }
                }
            }
        }