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() { using (var scope = new DatabaseScope(context.Database.Name)) { virtualIndicesRepository.DestroyAll(); foreach (var index in context.IndicesDesignData.PossibleIndices.All) { Dictionary <AttributeData, List <string> > possibleFilteredAttributeValues = new Dictionary <AttributeData, List <string> >(); foreach (var a in index.Attributes) { List <string> mostSignificantValues = new List <string>(); List <string> leastSignificantValues = new List <string>(); if (a.MostCommonValuesFrequencies != null && a.MostCommonValuesFrequencies.Length >= 2)// we need at least two values { decimal frequenciesSum = 0; for (int i = 0; i < Math.Min(a.MostCommonValuesFrequencies.Length - 1, MOST_COMMON_VALUES_MAX_COUNT); i++) { if (a.MostCommonValuesFrequencies[i] >= MOST_COMMON_VALUE_MIN_FREQUENCY) { var sqlStringValue = toSqlValueStringConverter.ConvertStringRepresentation(a.DbType, a.MostCommonValues[i]); if (sqlStringValue != null) { mostSignificantValues.Add(sqlStringValue); frequenciesSum += a.MostCommonValuesFrequencies[i]; } } } if (frequenciesSum < MOST_COMMON_VALUES_MIN_FREQUENCIES_SUM || frequenciesSum > MOST_COMMON_VALUES_MAX_FREQUENCIES_SUM) { mostSignificantValues.Clear(); } frequenciesSum = 0; for (int i = a.MostCommonValuesFrequencies.Length - 1; i >= Math.Max(1, a.MostCommonValuesFrequencies.Length - MOST_COMMON_VALUES_MAX_COUNT + 1); i--) { var sqlStringValue = toSqlValueStringConverter.ConvertStringRepresentation(a.DbType, a.MostCommonValues[i]); if (sqlStringValue != null) { leastSignificantValues.Add(sqlStringValue); frequenciesSum += a.MostCommonValuesFrequencies[i]; } } if (frequenciesSum > MOST_COMMON_VALUES_MIN_FREQUENCIES_SUM) { leastSignificantValues.Clear(); } } if (mostSignificantValues.Count > 0) { possibleFilteredAttributeValues.Add(a, mostSignificantValues); } else if (leastSignificantValues.Count > 0) { possibleFilteredAttributeValues.Add(a, leastSignificantValues); } } if (possibleFilteredAttributeValues.Count > 0) { string filter = CreateFilterString(possibleFilteredAttributeValues); var targetRelationData = context.RelationsData.GetReplacementOrOriginal(index.Relation.ID); var virtualIndex = virtualIndicesRepository.Create(dbObjectDefinitionGenerator.Generate(index.WithReplacedRelation(targetRelationData), filter)); var size = virtualIndicesRepository.GetVirtualIndexSize(virtualIndex.ID); var filters = new Dictionary <string, long>(); filters.Add(filter, size); context.IndicesDesignData.PossibleIndexFilters.Add(index, filters); } } } }
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(); } } } }