/// <summary> /// Calculate the number used for adjusting the SQL Server estimation in the downsize function. /// </summary> /// <param name="metaData"></param> /// <param name="joinCondition"></param> /// <param name="candidateJoinUnit"></param> /// <param name="nodeAlias"></param> /// <param name="affectedSqlEstimatedSize"></param> private static WTableReference AdjustEstimation(CandidateJoinUnit candidateJoinUnit, string nodeAlias, GraphMetaData metaData, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize) { const int sizeFactor = 10; int estimateFactor = 0; double size = candidateJoinUnit.EdgeDegrees; double estimatedSize = candidateJoinUnit.SqlEstimatedEdgeDegrees; double shrinkSize = candidateJoinUnit.TreeRoot.EstimatedRows; WTableReference tableReference = candidateJoinUnit.ToTableReference(nodeAlias, metaData); affectedSqlEstimatedSize = 1.0; joinCondition = null; if (size > sizeFactor * estimatedSize) { estimateFactor = (int)Math.Ceiling(size / estimatedSize); } else if (sizeFactor * size < estimatedSize) { shrinkSize = 1.0 / (1 - Math.Pow((1 - 1.0 / shrinkSize), 1.5)); affectedSqlEstimatedSize /= shrinkSize; estimatedSize /= shrinkSize; estimateFactor = (int)Math.Ceiling(size / estimatedSize); joinCondition = ConstructDownSizeJoinCondition(nodeAlias); } if (estimateFactor > 1) { double affectedUpSize; tableReference = ConstructUpSizeTableReference(tableReference, estimateFactor, out affectedUpSize); affectedSqlEstimatedSize *= affectedUpSize; } return(tableReference); }
public override WTableReference ToTableReference() { List <WScalarExpression> parameters = new List <WScalarExpression>(); if (SourceVariableProperty != null) { parameters.Add(SourceVariableProperty.ToScalarExpression()); } if (SinkVariableProperty != null) { parameters.Add(SinkVariableProperty.ToScalarExpression()); } foreach (var property in ProjectedProperties) { parameters.Add(SqlUtil.GetValueExpr(property)); } WTableReference tableRef = null; if (SourceVariableProperty != null && SinkVariableProperty != null) { //BothV tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.BothV, parameters, GetVariableName()); } else { tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.EtoV, parameters, GetVariableName()); } return(SqlUtil.GetCrossApplyTableReference(tableRef)); }
internal static WUnqualifiedJoin GetCrossApplyTableReference(WTableReference firstTableRef, WTableReference secondTableRef) { return(new WUnqualifiedJoin() { FirstTableRef = firstTableRef, SecondTableRef = secondTableRef, UnqualifiedJoinType = UnqualifiedJoinType.CrossApply }); }
public override WTableReference ToTableReference() { List <WScalarExpression> parameters = new List <WScalarExpression>(); parameters.AddRange(this.ProjectedProperties.Select(SqlUtil.GetValueExpr)); WTableReference tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.V, parameters, this.GetVariableName()); return(SqlUtil.GetCrossApplyTableReference(tableRef)); }
/// <summary> /// Span the table given the edge using cross apply /// </summary> /// <param name="tableRef"></param> /// <param name="edge"></param> /// <param name="nodeAlias"></param> /// <returns></returns> public static WTableReference SpanTableRef(WTableReference tableRef, MatchEdge edge, string nodeAlias) { tableRef = new WUnqualifiedJoin { FirstTableRef = tableRef, SecondTableRef = EdgeToTableReference(edge, nodeAlias), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }; return(tableRef); }
/// <summary> /// Span the table given the edge using cross apply /// </summary> /// <param name="tableRef"></param> /// <param name="edge"></param> /// <param name="nodeAlias"></param> /// <returns></returns> public WTableReference SpanTableRef(WTableReference tableRef, MatchEdge edge, string nodeAlias, GraphMetaData metaData) { tableRef = new WUnqualifiedJoin { FirstTableRef = tableRef, SecondTableRef = edge.ToSchemaObjectFunction(nodeAlias, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }; return(tableRef); }
public MatchComponent(MatchNode node) : this() { Nodes.Add(node); MaterializedNodeSplitCount[node] = 0; //SinkNodeStatisticsDict[node] = new Statistics (); Cardinality *= node.EstimatedRows; SqlEstimatedSize *= node.EstimatedRows; TableRef = new WNamedTableReference { Alias = new Identifier { Value = node.RefAlias }, TableObjectName = node.NodeTableObjectName }; }
public override WTableReference ToTableReference() { List <WScalarExpression> parameters = new List <WScalarExpression>(); WTableReference tableRef = null; if (this.PropertyKeys.Count == 0) { parameters.Add(this.ProjectVariable.DefaultProjection().ToScalarExpression()); tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.AllValues, parameters, GetVariableName()); } else { parameters.AddRange(this.PropertyKeys.Select(property => this.ProjectVariable.GetVariableProperty(property).ToScalarExpression())); tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.Values, parameters, GetVariableName()); } return(SqlUtil.GetCrossApplyTableReference(tableRef)); }
public MatchComponent(MatchNode node, List <MatchEdge> populatedEdges, GraphMetaData metaData) : this(node) { foreach (var edge in populatedEdges) { TableRef = SpanTableRef(TableRef, edge, node.RefAlias, metaData); EdgeMaterilizedDict[edge] = true; SinkNodeStatisticsDict[edge.SinkNode] = edge.Statistics; var edgeList = UnmaterializedNodeMapping.GetOrCreate(edge.SinkNode); edgeList.Add(edge); if (!Nodes.Contains(edge.SinkNode)) { Nodes.Add(edge.SinkNode); } Cardinality *= edge.AverageDegree; SqlEstimatedSize *= 1000; } }
public override WTableReference ToTableReference() { List <WScalarExpression> PropertyKeys = new List <WScalarExpression>(); if (SourceVariableProperty != null) { PropertyKeys.Add(SourceVariableProperty.ToScalarExpression()); } if (SinkVariableProperty != null) { PropertyKeys.Add(SinkVariableProperty.ToScalarExpression()); } foreach (var property in ProjectedProperties) { PropertyKeys.Add(SqlUtil.GetValueExpr(property)); } WTableReference secondTableRef = null; if (SourceVariableProperty != null && SinkVariableProperty != null) { //BothV secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.BothV, PropertyKeys, this, GetVariableName()); } switch (InputEdgeType) { case WEdgeType.BothEdge: secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.OtherV, PropertyKeys, this, GetVariableName()); break; case WEdgeType.InEdge: secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.OutV, PropertyKeys, this, GetVariableName()); break; case WEdgeType.OutEdge: secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.InV, PropertyKeys, this, GetVariableName()); break; case WEdgeType.Undefined: secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.OutV, PropertyKeys, this, GetVariableName()); break; } return(SqlUtil.GetCrossApplyTableReference(null, secondTableRef)); }
public override WTableReference ToTableReference() { List <WScalarExpression> PropertyKeys = new List <WScalarExpression>(); if (SourceVertexVariableProperty != null) { PropertyKeys.Add(SourceVertexVariableProperty.ToScalarExpression()); } if (AdjEdgeVariableProperty != null) { PropertyKeys.Add(AdjEdgeVariableProperty.ToScalarExpression()); } if (RevAdjEdgeVariableProperty != null) { PropertyKeys.Add(RevAdjEdgeVariableProperty.ToScalarExpression()); } if (LabelVariableProperty != null) { PropertyKeys.Add(LabelVariableProperty.ToScalarExpression()); } foreach (var property in ProjectedProperties) { PropertyKeys.Add(SqlUtil.GetValueExpr(property)); } WTableReference secondTableRef = null; switch (EdgeType) { case WEdgeType.BothEdge: secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.BothE, PropertyKeys, this, GetVariableName()); break; case WEdgeType.InEdge: secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.InE, PropertyKeys, this, GetVariableName()); break; case WEdgeType.OutEdge: secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.OutE, PropertyKeys, this, GetVariableName()); break; } return(SqlUtil.GetCrossApplyTableReference(null, secondTableRef)); }
public WCommonTableExpression Invoke(WSqlFragment node, string tempTableName, string sourceTableName, out WTableReference sinkTable, out List <WScalarExpression> edgeProperties) { _edgeProperties = new List <WScalarExpression>(); _tempTableName = tempTableName; _sourceTableName = sourceTableName; node.Accept(this); sinkTable = _sinkTable; _edgeProperties.Insert(0, new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier { Value = "sink" }) } ); edgeProperties = _edgeProperties; return(_result); }
public override WTableReference ToTableReference() { List <WScalarExpression> parameters = new List <WScalarExpression>(); WTableReference tableRef = null; if (PropertyKeys.Count == 0) { parameters.Add(ProjectVariable.GetVariableProperty(GremlinKeyword.Star).ToScalarExpression()); tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.AllValues, parameters, GetVariableName()); } else { foreach (var property in PropertyKeys) { parameters.Add(ProjectVariable.GetVariableProperty(property).ToScalarExpression()); } tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.Values, parameters, GetVariableName()); } return(SqlUtil.GetCrossApplyTableReference(tableRef)); }
/// <summary> /// Calculate the number used for adjusting the SQL Server estimation in the downsize function. /// </summary> /// <param name="component"></param> /// <param name="joinCondition"></param> private static WTableReference AdjustEstimation(MatchComponent component, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize) { const int sizeFactor = 10; int estimateFactor = 0; double size = component.Cardinality; double estimatedSize = component.SqlEstimatedSize; double shrinkSize = component.RightestTableRefSize; WTableReference tableReference = component.TableRef; affectedSqlEstimatedSize = 1.0; joinCondition = null; if (size > sizeFactor * estimatedSize) { estimateFactor = (int)Math.Ceiling(size / estimatedSize); } else if (sizeFactor * size < estimatedSize) { shrinkSize = 1.0 / (1 - Math.Pow((1 - 1.0 / shrinkSize), 1.5)); if (estimatedSize < shrinkSize) { affectedSqlEstimatedSize /= estimatedSize; estimatedSize = 1; } else { affectedSqlEstimatedSize /= shrinkSize; estimatedSize /= shrinkSize; } estimateFactor = (int)Math.Ceiling(size / estimatedSize); joinCondition = ConstructDownSizeJoinCondition(component.RightestTableAlias); } if (estimateFactor > 1) { double affectedUpSize; tableReference = ConstructUpSizeTableReference(tableReference, estimateFactor, out affectedUpSize); affectedSqlEstimatedSize *= affectedUpSize; } return(tableReference); }
/// <summary> /// Deep Copy /// </summary> /// <param name="component"></param> public MatchComponent(MatchComponent component) { Nodes = new HashSet <MatchNode>(component.Nodes); EdgeMaterilizedDict = new Dictionary <MatchEdge, bool>(component.EdgeMaterilizedDict); MaterializedNodeSplitCount = new Dictionary <MatchNode, int>(component.MaterializedNodeSplitCount); UnmaterializedNodeMapping = new Dictionary <MatchNode, List <MatchEdge> >(); foreach (var nodeMapping in component.UnmaterializedNodeMapping) { UnmaterializedNodeMapping[nodeMapping.Key] = new List <MatchEdge>(nodeMapping.Value); } SinkNodeStatisticsDict = new Dictionary <MatchNode, Statistics>(component.SinkNodeStatisticsDict); TableRef = component.TableRef; Cardinality = component.Cardinality; Cost = component.Cost; DeltaMemory = component.DeltaMemory; TotalMemory = component.TotalMemory; SqlEstimatedDeltaMemory = component.SqlEstimatedDeltaMemory; SqlEstimatedTotalMemory = component.SqlEstimatedTotalMemory; SqlEstimatedSize = component.SqlEstimatedSize; RightestTableAlias = component.RightestTableAlias; RightestTableRefSize = component.RightestTableRefSize; }
private static WTableReference ConstructUpSizeTableReference(WTableReference tableRef, double upSizeScalar, out double affectedEstimatedSize) { affectedEstimatedSize = 1.0; int pow = (int)(Math.Floor(Math.Log(upSizeScalar, 1000)) + 1); int adjustValue = (int)Math.Pow(upSizeScalar, 1.0 / pow); while (pow > 0) { tableRef = new WUnqualifiedJoin { FirstTableRef = tableRef, SecondTableRef = new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, new Identifier { Value = "UpSizeFunction" }), Parameters = new List <WScalarExpression> { new WValueExpression { Value = adjustValue.ToString() } }, Alias = new Identifier { Value = Path.GetRandomFileName().Replace(".", "").Substring(0, 8), } }, UnqualifiedJoinType = UnqualifiedJoinType.CrossApply }; pow--; affectedEstimatedSize *= adjustValue; } return(tableRef); }
public MatchComponent(MatchNode node, List<MatchEdge> populatedEdges,GraphMetaData metaData) : this(node) { foreach (var edge in populatedEdges) { TableRef = SpanTableRef(TableRef, edge, node.RefAlias, metaData); EdgeMaterilizedDict[edge] = true; SinkNodeStatisticsDict[edge.SinkNode] = edge.Statistics; var edgeList = UnmaterializedNodeMapping.GetOrCreate(edge.SinkNode); edgeList.Add(edge); if (!Nodes.Contains(edge.SinkNode)) Nodes.Add(edge.SinkNode); Cardinality *= edge.AverageDegree; SqlEstimatedSize *= 1000; } }
/// <summary> /// Transit from current component to the new component in the next state given the Node Unit /// </summary> /// <param name="candidateTree"></param> /// <param name="densityDict"></param> /// <param name="subGraph"></param> /// <param name="statisticsCalculator"></param> /// <returns></returns> public MatchComponent GetNextState( OneHeightTree candidateTree, Dictionary <string, double> densityDict, IMatchJoinStatisticsCalculator statisticsCalculator) { var newComponent = new MatchComponent(this); var root = candidateTree.TreeRoot; WBooleanExpression joinCondition = null; string nodeName = ""; // Update Nodes if (newComponent.MaterializedNodeSplitCount.ContainsKey(root)) { newComponent.MaterializedNodeSplitCount[root]++; nodeName = newComponent.GetNodeRefName(root); joinCondition = new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = root.RefAlias }, new Identifier { Value = "GlobalNodeId" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ), }, ComparisonType = BooleanComparisonType.Equals }; } else { nodeName = root.RefAlias; newComponent.Nodes.Add(root); newComponent.MaterializedNodeSplitCount[root] = 0; newComponent.StatisticsDict[root] = new ColumnStatistics { Selectivity = 1.0 / root.TableRowCount }; } // Constructs table reference WTableReference nodeTable = new WNamedTableReference { Alias = new Identifier { Value = nodeName }, TableObjectName = root.TableObjectName }; WTableReference compTable = newComponent.TableRef; // Updates join conditions double selectivity = 1.0; double degrees = 1.0; var DensityCount = new Dictionary <string, int>(StringComparer.CurrentCultureIgnoreCase); List <MatchEdge> inEdges; if (newComponent.UnmaterializedNodeMapping.TryGetValue(root, out inEdges)) { var firstEdge = inEdges.First(); bool materialized = newComponent.EdgeMaterilizedDict[firstEdge]; newComponent.UnmaterializedNodeMapping.Remove(root); selectivity *= 1.0 / root.TableRowCount; // Component materialized edge to root if (materialized) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); //var statistics = ColumnStatistics.UpdateHistogram(newComponent.StatisticsDict[root], // new ColumnStatistics {Selectivity = 1.0/root.TableRowCount}); //selectivity *= statistics.Selectivity; //newComponent.StatisticsDict[root] = statistics; if (DensityCount.ContainsKey(root.TableObjectName.ToString())) { DensityCount[root.TableObjectName.ToString()]++; } else { DensityCount[root.TableObjectName.ToString()] = 1; } } // Component unmaterialized edge to root else { ColumnStatistics statistics = null; foreach (var edge in inEdges) { // Update component table compTable = SpanTableRef(compTable, edge, newComponent.GetNodeRefName(edge.SourceNode)); newComponent.EdgeMaterilizedDict[edge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = edge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); statistics = ColumnStatistics.UpdateHistogram(statistics, newComponent.Context.GetEdgeStatistics(edge)); selectivity *= statistics.Selectivity; } newComponent.StatisticsDict[root] = statistics; if (DensityCount.ContainsKey(root.TableObjectName.ToString())) { DensityCount[root.TableObjectName.ToString()] += inEdges.Count; } else { DensityCount[root.TableObjectName.ToString()] = inEdges.Count; } } } var jointEdges = candidateTree.MaterializedEdges; int sinkToSinkCount = 0; foreach (var jointEdge in jointEdges) { // Update node table nodeTable = SpanTableRef(nodeTable, jointEdge, nodeName); degrees *= jointEdge.AverageDegree; newComponent.EdgeMaterilizedDict[jointEdge] = true; var sinkNode = jointEdge.SinkNode; // Leaf to component materialized node if (newComponent.MaterializedNodeSplitCount.ContainsKey(sinkNode)) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = sinkNode.RefAlias }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); var statistics = ColumnStatistics.UpdateHistogram(newComponent.StatisticsDict[sinkNode], newComponent.Context.GetEdgeStatistics(jointEdge)); selectivity *= statistics.Selectivity; newComponent.StatisticsDict[sinkNode] = statistics; if (DensityCount.ContainsKey(sinkNode.TableObjectName.ToString())) { DensityCount[sinkNode.TableObjectName.ToString()]++; } else { DensityCount[sinkNode.TableObjectName.ToString()] = 1; } } // Leaf to component unmaterialized node else { inEdges = newComponent.UnmaterializedNodeMapping[sinkNode]; var firstEdge = inEdges.First(); bool materlizedEdge = newComponent.EdgeMaterilizedDict[firstEdge]; // Leaf to materialized leaf if (materlizedEdge) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); sinkToSinkCount++; var statistics = ColumnStatistics.UpdateHistogram(newComponent.StatisticsDict[sinkNode], newComponent.Context.GetEdgeStatistics(jointEdge)); selectivity *= statistics.Selectivity; newComponent.StatisticsDict[sinkNode] = statistics; } // Leaf to unmaterialized leaf else { ColumnStatistics compSinkNodeStatistics = null; foreach (var inEdge in inEdges) { compTable = SpanTableRef(compTable, inEdge, newComponent.GetNodeRefName(inEdge.SourceNode)); newComponent.EdgeMaterilizedDict[inEdge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = inEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); sinkToSinkCount++; var leafToLeafStatistics = statisticsCalculator.GetLeafToLeafStatistics(jointEdge, inEdge); selectivity *= leafToLeafStatistics.Selectivity; compSinkNodeStatistics = ColumnStatistics.UpdateHistogram(compSinkNodeStatistics, newComponent.Context.GetEdgeStatistics(inEdge)); } newComponent.StatisticsDict[sinkNode] = compSinkNodeStatistics; } } } var unmatEdges = candidateTree.UnmaterializedEdges; foreach (var unmatEdge in unmatEdges) { newComponent.EdgeMaterilizedDict[unmatEdge] = false; newComponent.Nodes.Add(unmatEdge.SinkNode); var sinkNodeInEdges = newComponent.UnmaterializedNodeMapping.GetOrCreate(unmatEdge.SinkNode); sinkNodeInEdges.Add(unmatEdge); degrees *= unmatEdge.AverageDegree; } // Calculate Estimated Join Selectivity & Estimated Node Size double estimatedSelectity = 1.0; int count = 0; bool sinkJoin = false; foreach (var item in densityDict.Where(e => DensityCount.ContainsKey(e.Key))) { var density = item.Value; var curJoinCount = DensityCount[item.Key]; var curJoinSelectitivy = Math.Pow(density, 2 - Math.Pow(2, 1 - curJoinCount)); if (!sinkJoin && ColumnStatistics.DefaultDensity < density) { var curSinkJoinSelectivity = Math.Pow(ColumnStatistics.DefaultDensity, 2 - Math.Pow(2, 1 - sinkToSinkCount)); estimatedSelectity *= Math.Pow(curSinkJoinSelectivity, Math.Pow(2, -count)); count += sinkToSinkCount; sinkJoin = true; } estimatedSelectity *= Math.Pow(curJoinSelectitivy, Math.Pow(2, -count)); count += curJoinCount; } var estimatedNodeUnitSize = root.EstimatedRows * Math.Pow(1000, candidateTree.MaterializedEdges.Count + candidateTree.UnmaterializedEdges.Count); // Update Table Reference newComponent.TableRef = GetPlanAndUpdateCost(candidateTree, newComponent, nodeTable, compTable, joinCondition, degrees, selectivity, estimatedNodeUnitSize, estimatedSelectity); return(newComponent); }
/// <summary> /// Calculate the number used for adjusting the SQL Server estimation in the downsize function. /// </summary> /// <param name="component"></param> /// <param name="tablfRef"></param> /// <param name="joinTable"></param> /// <param name="size"></param> /// <param name="estimatedSize"></param> /// <param name="shrinkSize"></param> /// <param name="joinTableTuple"></param> private static void AdjustEstimation( MatchComponent component, WTableReference tablfRef, WQualifiedJoin joinTable, double size, double estimatedSize, double shrinkSize, Tuple <WQualifiedJoin, String> joinTableTuple) { const int sizeFactor = 10; int estimateFactor = 0; if (size > sizeFactor * estimatedSize) { estimateFactor = (int)Math.Ceiling(size / estimatedSize); } else if (sizeFactor * size < estimatedSize) { estimatedSize /= shrinkSize; component.EstimateSize /= shrinkSize; component.FatherListofDownSizeTable.Add(joinTableTuple); estimateFactor = (int)Math.Ceiling(size / estimatedSize); } if (estimateFactor > 1) { WTableReference crossApplyTable = tablfRef; int pow = (int)(Math.Floor(Math.Log(estimateFactor, 1000)) + 1); int adjustValue = (int)Math.Pow(estimateFactor, 1.0 / pow); while (pow > 0) { crossApplyTable = new WUnqualifiedJoin { FirstTableRef = crossApplyTable, SecondTableRef = new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, new Identifier { Value = "UpSizeFunction" }), Parameters = new List <WScalarExpression> { new WValueExpression { Value = adjustValue.ToString() } }, Alias = new Identifier { Value = Path.GetRandomFileName().Replace(".", "").Substring(0, 8), } }, UnqualifiedJoinType = UnqualifiedJoinType.CrossApply }; pow--; component.EstimateSize *= adjustValue; } joinTable.FirstTableRef = crossApplyTable; } }
/// <summary> /// Calculate join costs and update components using optimal join method & order /// </summary> /// <param name="nodeUnitCandidate"></param> /// <param name="component"></param> /// <param name="nodeTable"></param> /// <param name="componentTable"></param> /// <param name="joinCondition"></param> /// <param name="nodeDegrees"></param> /// <param name="estimatedNodeUnitSize"></param> /// <param name="estimatedSelectivity"></param> /// <returns></returns> private static WTableReference GetPlanAndUpdateCost( OneHeightTree nodeUnitCandidate, MatchComponent component, WTableReference nodeTable, WTableReference componentTable, WBooleanExpression joinCondition, double nodeDegrees, double joinSelectivity, double estimatedNodeUnitSize, double estimatedSelectivity) { var nodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeDegrees; var componentSize = component.Size; var estimatedCompSize = component.EstimateSize; var cost = nodeUnitSize + componentSize; //var joinSelectivity = // nodeWithJoinMapping.SelectivityProduct; //nodeWithJoinMapping.ExponentialSelevtivityProduct; WQualifiedJoin joinTable = new WQualifiedJoin { FirstTableRef = componentTable, SecondTableRef = nodeTable, JoinCondition = joinCondition, QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Hash }; var node = nodeUnitCandidate.TreeRoot; // If the node is already in the component, then only multiply the degree to get the size double nodeUnitActualSize; if (component.MaterializedNodeSplitCount[node] > 0) { nodeUnitActualSize = nodeDegrees; var cEstEdge = Math.Pow(1000, component.EdgeMaterilizedDict.Count(e => !e.Value)); var cSize = component.EstimateSize / cEstEdge; var nSize = node.EstimatedRows; if (nSize > cSize) { component.EstimateSize = estimatedNodeUnitSize * cEstEdge * estimatedSelectivity; } else { component.EstimateSize = component.EstimateSize * Math.Pow(1000, nodeUnitCandidate.UnmaterializedEdges.Count) * estimatedSelectivity; } } else { nodeUnitActualSize = nodeUnitSize; component.EstimateSize *= estimatedNodeUnitSize * estimatedSelectivity; } //Update Size component.Size *= nodeUnitActualSize * joinSelectivity; // Update Cost component.Cost += cost; // Debug #if DEBUG //Trace.Write(component.NodeUnits.Count+" "); //foreach (var n in component.NodeUnits.Where(e => e.Key != node.Node.ExposedName)) //{ // Trace.Write(n.Value.NodeRefName); //} //Trace.Write(component.NodeUnits[node.Node.ExposedName].NodeRefName+" "); //Trace.Write(" "+(long)component.Cost+" "+(long)component.Size); //Trace.Write(" "); //foreach (var item in component.PopulatedEdgesName) //{ // Trace.Write(item + " "); //} //Trace.Write("; "); //foreach (var unpopulatedEdge in component.UnpopulatedEdges) //{ // Trace.Write(unpopulatedEdge.Alias + " "); //} //Trace.WriteLine(""); #endif // Update TableRef // Only consider the size in the first join if (component.MaterializedNodeSplitCount.Count == 2 && component.MaterializedNodeSplitCount.All(e => e.Value == 0)) { var nodeInComp = component.MaterializedNodeSplitCount.Keys.First(e => e != node); if (nodeUnitSize < componentSize) { joinTable.FirstTableRef = nodeTable; joinTable.SecondTableRef = componentTable; component.TotalMemory = component.DeltaMemory = nodeUnitSize; component.EstimateTotalMemory = component.EstimateDeltaMemory = estimatedNodeUnitSize; component.RightestTableRefSize = nodeInComp.EstimatedRows; component.FatherOfRightestTableRef = new Tuple <WQualifiedJoin, String>(joinTable, component.GetNodeRefName(nodeInComp)); AdjustEstimation(component, nodeTable, joinTable, nodeUnitSize, estimatedNodeUnitSize, nodeUnitCandidate.TreeRoot.EstimatedRows, new Tuple <WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node))); } else { component.TotalMemory = component.DeltaMemory = componentSize; component.EstimateTotalMemory = component.EstimateDeltaMemory = component.EstimateSize; component.RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; component.FatherOfRightestTableRef = new Tuple <WQualifiedJoin, String>(joinTable, component.GetNodeRefName(node)); AdjustEstimation(component, componentTable, joinTable, componentSize, estimatedCompSize, nodeInComp.EstimatedRows, new Tuple <WQualifiedJoin, string>(joinTable, component.GetNodeRefName(nodeInComp))); } } else { double sizeFactor = 5;//1000; double maxMemory = 1e8; double loopJoinInnerThreshold = 10000; double loopJoinOuterThreshold = 1000000; // Left Deep if (componentSize * sizeFactor < nodeUnitSize) { var curDeltaMemory = componentSize; component.TotalMemory = component.DeltaMemory + curDeltaMemory; component.DeltaMemory = curDeltaMemory; var curDeltaEstimateMemory = component.EstimateSize; component.EstimateTotalMemory = component.EstimateDeltaMemory + curDeltaEstimateMemory; component.EstimateDeltaMemory = curDeltaEstimateMemory; // Adjust estimation in sql server AdjustEstimation(component, componentTable, joinTable, componentSize, estimatedCompSize, component.RightestTableRefSize, component.FatherOfRightestTableRef); component.FatherOfRightestTableRef = new Tuple <WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node)); component.RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; } else { // Loop Join if ( //((nodeUnitSize < loopJoinInnerThreshold /*&& componentSize < loopJoinOuterThreshold*/) || component.DeltaMemory + componentSize > maxMemory / 100) && ((nodeUnitSize < loopJoinInnerThreshold && componentSize < loopJoinOuterThreshold) || component.DeltaMemory + componentSize > maxMemory) && nodeUnitCandidate.MaterializedEdges.Count == 0) { component.TotalMemory = component.DeltaMemory; component.EstimateTotalMemory = component.EstimateDeltaMemory; joinTable.JoinHint = JoinHint.Loop; component.EstimateSize = estimatedCompSize * estimatedNodeUnitSize / nodeUnitCandidate.TreeRoot.TableRowCount; } // Right Deep else { joinTable.FirstTableRef = nodeTable; joinTable.SecondTableRef = componentTable; AdjustEstimation(component, nodeTable, joinTable, nodeUnitSize, estimatedNodeUnitSize, node.EstimatedRows, new Tuple <WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node))); component.TotalMemory += nodeUnitSize; component.DeltaMemory = component.TotalMemory; component.EstimateTotalMemory += estimatedNodeUnitSize; component.EstimateDeltaMemory = component.EstimateTotalMemory; } } } return(new WParenthesisTableReference { Table = joinTable }); }
/// <summary> /// Span the table given the edge using cross apply /// </summary> /// <param name="tableRef"></param> /// <param name="edge"></param> /// <param name="nodeAlias"></param> /// <param name="dumbNode"></param> /// <returns></returns> public WTableReference SpanTableRef(WTableReference tableRef, MatchEdge edge, string nodeAlias, string dumbNode, GraphMetaData metaData) { tableRef = new WUnqualifiedJoin { FirstTableRef = tableRef, SecondTableRef = edge.ToSchemaObjectFunction(nodeAlias, dumbNode, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }; return tableRef; }
private static WTableReference ConstructUpSizeTableReference(WTableReference tableRef, double upSizeScalar, string dumb, DumbType dumbType, out double affectedEstimatedSize) { affectedEstimatedSize = 1.0; int pow = (int)(Math.Floor(Math.Log(upSizeScalar, 1000)) + 1); int adjustValue = (int)Math.Pow(upSizeScalar, 1.0 / pow); while (pow > 0) { tableRef = new WUnqualifiedJoin { FirstTableRef = tableRef, SecondTableRef = new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, new Identifier { Value = "UpSizeFunction2" }), Parameters = new List<WScalarExpression> { new WValueExpression {Value = adjustValue.ToString()}, new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier( new Identifier {Value = dumb}, new Identifier {Value = dumbType == DumbType.Node ? "GlobalNodeId" : "Sink"}) } }, Alias = new Identifier { Value = Path.GetRandomFileName().Replace(".", "").Substring(0, 8), } }, UnqualifiedJoinType = UnqualifiedJoinType.CrossApply }; pow--; affectedEstimatedSize *= adjustValue; } return tableRef; }
private WMultiCommonTableExpression ConstructDeleteEdgeSelect(WSelectQueryBlock node, WEdgeColumnReferenceExpression edgeCol, string tempTableName ,out WTableReference sinkTable, out WTableReference sourceTable, ref bool hasReversedEdge) { sourceTable = null; sinkTable = null; string sourceTableName = null; string sinkTableName = null; var sourceTableScalar = node.SelectElements[0] as WSelectScalarExpression; if (sourceTableScalar == null) return null; var sourceTableColumn = sourceTableScalar.SelectExpr as WColumnReferenceExpression; if (sourceTableColumn == null) return null; sourceTableName = sourceTableColumn.MultiPartIdentifier.Identifiers.Last().Value; var sourceNodeIdExpr = new WColumnReferenceExpression(); foreach (var identifier in sourceTableColumn.MultiPartIdentifier.Identifiers) sourceNodeIdExpr.Add(identifier); sourceNodeIdExpr.AddIdentifier("GlobalNodeId"); var collectVarVisitor = new CollectVariableVisitor(); var context = collectVarVisitor.Invoke(node); if (!context.NodeTableDictionary.Any()) { throw new GraphViewException("Missing From Clause in Delete Edge statement"); } WColumnReferenceExpression sinkNodeIdExpr = null; sourceTable = context[sourceTableName]; var groupByParams = new List<WScalarExpression>(); for (var index = 1; index < node.SelectElements.Count; ++index) { var element = node.SelectElements[index] as WSelectScalarExpression; if (element == null) return null; //sink table if (index == 1) { var sinkTableColumn = element.SelectExpr as WColumnReferenceExpression; if (sinkTableColumn == null) return null; sinkTableName = sinkTableColumn.MultiPartIdentifier.Identifiers.Last().Value; sinkTable = context[sinkTableName]; sinkNodeIdExpr = new WColumnReferenceExpression(); foreach (var identifier in sinkTableColumn.MultiPartIdentifier.Identifiers) sinkNodeIdExpr.Add(identifier); sinkNodeIdExpr.AddIdentifier("GlobalNodeId"); } } var sourceNodeId = new WSelectScalarExpression { SelectExpr = sourceNodeIdExpr, ColumnName = "src", }; var sinkNodeId = new WSelectScalarExpression { SelectExpr = sinkNodeIdExpr, ColumnName = "sink" }; var edgeId = new WSelectScalarExpression { SelectExpr = new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier( new Identifier[] { new Identifier {Value = edgeCol.Alias??string.Format("{0}_{1}_{2}",sourceTableName,edgeCol.MultiPartIdentifier.Identifiers.Last().Value,sinkTableName)}, new Identifier {Value = "Edgeid"} }) }, ColumnName = "edgeid" }; var sourceTableNameRef = sourceTable as WNamedTableReference; if (sourceTableNameRef == null) throw new GraphViewException("Source table of DELETE EDGE statement should be a named table reference."); var sinkTableNameRef = sinkTable as WNamedTableReference; if (sinkTableNameRef == null) throw new GraphViewException("Sink table of DELETE EDGE statement should be a named table reference."); int compare = string.CompareOrdinal(sourceTableNameRef.TableObjectName.ToString(), sinkTableNameRef.TableObjectName.ToString()); using (var command = Tx.Connection.CreateCommand()) { command.Transaction = Tx; command.Parameters.AddWithValue("@schema", WNamedTableReference.SchemaNameToTuple(sourceTableNameRef.TableObjectName).Item1); command.Parameters.AddWithValue("@tableName", sourceTableNameRef.TableObjectName.Identifiers.Last().Value); command.Parameters.AddWithValue("@colName", edgeCol.MultiPartIdentifier.Identifiers.Last().Value); command.Parameters.AddWithValue("@role", WNodeTableColumnRole.Edge); command.CommandText = string.Format(@" SELECT NodeColumn.HasReversedEdge FROM [{0}] AS NodeColumn WHERE NodeColumn.TableSchema = @schema and NodeColumn.TableName = @tableName and NodeColumn.ColumnName = @colName and NodeColumn.ColumnRole = @role", GraphViewConnection.MetadataTables[1]); using (var reader = command.ExecuteReader()) { if (reader.Read()) { hasReversedEdge = bool.Parse(reader[0].ToString()); } } } var reversedEdgeName = sourceTableNameRef.TableObjectName.Identifiers.Last().Value + "_" + edgeCol.MultiPartIdentifier.Identifiers.Last().Value + "Reversed"; var reversedEdgeId = new WSelectScalarExpression { SelectExpr = new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier( new Identifier[] { new Identifier {Value = edgeCol.Alias??string.Format("{0}_{1}_{2}",sourceTableName,edgeCol.MultiPartIdentifier.Identifiers.Last().Value,sinkTableName)}, new Identifier {Value = "Edgeid"} }) }, ColumnName = "edgeid" }; var elements = new List<WSelectElement> { sourceNodeId, sinkNodeId, edgeId }; var reversedElements = new List<WSelectElement> { sourceNodeId, sinkNodeId, reversedEdgeId, }; return new WMultiCommonTableExpression { WCommonTableExpressions = new List<WCommonTableExpression>() { new WCommonTableExpression { ExpressionName = new Identifier { Value = hasReversedEdge && compare == 0 ? tempTableName + "_1" : tempTableName }, QueryExpression = new WSelectQueryBlock { FromClause = node.FromClause, WhereClause = new WWhereClause { SearchCondition = node.WhereClause.SearchCondition }, HavingClause = node.HavingClause, OrderByClause = node.OrderByClause, TopRowFilter = node.TopRowFilter, UniqueRowFilter = node.UniqueRowFilter, SelectElements = elements, MatchClause = node.MatchClause, } }, new WCommonTableExpression { ExpressionName = new Identifier { Value = hasReversedEdge && compare == 0 ? tempTableName + "_2" : tempTableName }, QueryExpression = new WSelectQueryBlock { FromClause = node.FromClause, WhereClause = hasReversedEdge ? new WWhereClause { SearchCondition = ObjectExtensions.Copy(node.WhereClause.SearchCondition), } : node.WhereClause, HavingClause = node.HavingClause, OrderByClause = node.OrderByClause, TopRowFilter = node.TopRowFilter, UniqueRowFilter = node.UniqueRowFilter, SelectElements = hasReversedEdge ? reversedElements : elements, MatchClause = hasReversedEdge? ConstructReversedMatchClause(node, context) : node.MatchClause, } }, } }; }
/// <summary> /// Calculate join costs and update components using optimal join method & order /// </summary> /// <param name="nodeUnitCandidate"></param> /// <param name="joinCondition"></param> /// <param name="joinSelectivity"></param> /// <param name="estimatedSelectivity"></param> /// <returns></returns> private void ConstructPhysicalJoinAndUpdateCost( CandidateJoinUnit nodeUnitCandidate, WBooleanExpression joinCondition, double joinSelectivity, double estimatedSelectivity, GraphMetaData metaData) { var nodeDegrees = nodeUnitCandidate.EdgeDegrees; var nodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeDegrees; var estimatedNodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeUnitCandidate.SqlEstimatedEdgeDegrees; var componentSize = Cardinality; var estimatedCompSize = SqlEstimatedSize; var node = nodeUnitCandidate.TreeRoot; // If the node is already in the component, then only multiply the degree to get the size double nodeUnitActualSize; double newCompEstSize; if (MaterializedNodeSplitCount[node] > 0) { nodeUnitActualSize = nodeDegrees; var cEstEdge = Math.Pow(1000, EdgeMaterilizedDict.Count(e => !e.Value)); var cSize = SqlEstimatedSize / cEstEdge; var nSize = node.EstimatedRows; if (nSize > cSize) { newCompEstSize = estimatedNodeUnitSize * cEstEdge * estimatedSelectivity; } else { newCompEstSize = SqlEstimatedSize * Math.Pow(1000, nodeUnitCandidate.UnmaterializedEdges.Count) * estimatedSelectivity; } } else { nodeUnitActualSize = nodeUnitSize; newCompEstSize = SqlEstimatedSize * estimatedNodeUnitSize * estimatedSelectivity; } newCompEstSize = newCompEstSize < 1.0 ? 1.0 : newCompEstSize; bool firstJoin = MaterializedNodeSplitCount.Count == 2 && MaterializedNodeSplitCount.All(e => e.Value == 0); // Update TableRef double loopJoinOuterThreshold = 1e4; //1e6; double sizeFactor = 5; //1000; double maxMemory = 1e8; double loopCost = componentSize * Math.Log(nodeUnitCandidate.TreeRoot.EstimatedRows, 512) * 0.20; double hashCost = componentSize + nodeUnitSize; double cost; // Loop Join if ( nodeUnitCandidate.MaterializedEdges.Count == 0 && // the joins are purely leaf to sink join ( //componentSize < loopJoinOuterThreshold || // the outer table is relatively small loopCost < hashCost || (DeltaMemory + componentSize > maxMemory && DeltaMemory + nodeUnitSize > maxMemory) // memory is in pressure ) ) { if (firstJoin) { RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; RightestTableAlias = GetNodeRefName(node); } TotalMemory = DeltaMemory; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory; //joinTable.JoinHint = JoinHint.Loop; SqlEstimatedSize = estimatedCompSize * estimatedNodeUnitSize / nodeUnitCandidate.TreeRoot.TableRowCount; cost = loopCost; //componentSize*Math.Log(nodeUnitCandidate.TreeRoot.EstimatedRows, 512); TableRef = new WParenthesisTableReference { Table = new WQualifiedJoin { FirstTableRef = TableRef, SecondTableRef = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData), JoinCondition = joinCondition, QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Loop } }; } // Hash Join else { cost = hashCost;//componentSize + nodeUnitSize; WBooleanExpression adjustedJoincondition; double adjustedSqlEstimatedSize; WTableReference buildTableReference; WTableReference probeTableReference; if (firstJoin) { var nodeInComp = MaterializedNodeSplitCount.Keys.First(e => e != node); if (nodeUnitSize < componentSize) { buildTableReference = AdjustEstimation(nodeUnitCandidate, GetNodeRefName(node), metaData, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = TableRef; TotalMemory = DeltaMemory = nodeUnitSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory = estimatedNodeUnitSize; RightestTableRefSize = nodeInComp.EstimatedRows; RightestTableAlias = GetNodeRefName(nodeInComp); } else { RightestTableRefSize = nodeInComp.EstimatedRows; RightestTableAlias = GetNodeRefName(nodeInComp); buildTableReference = AdjustEstimation(this, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData); TotalMemory = DeltaMemory = componentSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory = SqlEstimatedSize; RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; RightestTableAlias = GetNodeRefName(node); } } // Left Deep else if (componentSize * sizeFactor < nodeUnitSize) { // Adjust estimation in sql server buildTableReference = AdjustEstimation(this, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData); var curDeltaMemory = componentSize; TotalMemory = DeltaMemory + curDeltaMemory; DeltaMemory = curDeltaMemory; var curDeltaEstimateMemory = SqlEstimatedSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory + curDeltaEstimateMemory; SqlEstimatedDeltaMemory = curDeltaEstimateMemory; RightestTableAlias = GetNodeRefName(node); RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; } // Right Deep else { buildTableReference = AdjustEstimation(nodeUnitCandidate, GetNodeRefName(node), metaData, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = TableRef; TotalMemory += nodeUnitSize; DeltaMemory = TotalMemory; SqlEstimatedTotalMemory += estimatedNodeUnitSize; SqlEstimatedDeltaMemory = SqlEstimatedTotalMemory; } newCompEstSize *= adjustedSqlEstimatedSize; TableRef = new WParenthesisTableReference { Table = new WQualifiedJoin { FirstTableRef = buildTableReference, SecondTableRef = probeTableReference, JoinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, adjustedJoincondition), QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Hash } }; SqlEstimatedSize = newCompEstSize < 1.0 ? 1.0 : newCompEstSize; } //Update Size Cardinality *= nodeUnitActualSize * joinSelectivity; // Debug #if DEBUG //foreach (var item in MaterializedNodeSplitCount.Where(e => e.Key != node)) //{ // Trace.Write(item.Key.RefAlias + ","); //} //Trace.Write(node.RefAlias); //Trace.Write(" Size:" + Cardinality + " Cost:" + cost); //Trace.Write(" Method:" + ((TableRef as WParenthesisTableReference).Table as WQualifiedJoin).JoinHint); //Trace.WriteLine(" --> Total Cost:" + Cost); #endif // Update Cost Cost += cost; }
/// <summary> /// Calculate the number used for adjusting the SQL Server estimation in the downsize function. /// </summary> /// <param name="component"></param> /// <param name="tablfRef"></param> /// <param name="joinTable"></param> /// <param name="size"></param> /// <param name="estimatedSize"></param> /// <param name="shrinkSize"></param> /// <param name="joinTableTuple"></param> private static void AdjustEstimation( MatchComponent component, WTableReference tablfRef, WQualifiedJoin joinTable, double size, double estimatedSize, double shrinkSize, Tuple<WQualifiedJoin,String> joinTableTuple) { const int sizeFactor = 10; int estimateFactor = 0; if (size > sizeFactor*estimatedSize) { estimateFactor = (int)Math.Ceiling(size / estimatedSize); } else if (sizeFactor*size < estimatedSize) { estimatedSize /= shrinkSize; component.EstimateSize /= shrinkSize; component.FatherListofDownSizeTable.Add(joinTableTuple); estimateFactor = (int) Math.Ceiling(size/estimatedSize); } if (estimateFactor > 1) { WTableReference crossApplyTable = tablfRef; int pow = (int) (Math.Floor(Math.Log(estimateFactor, 1000)) + 1); int adjustValue = (int) Math.Pow(estimateFactor, 1.0/pow); while (pow > 0) { crossApplyTable = new WUnqualifiedJoin { FirstTableRef = crossApplyTable, SecondTableRef = new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier {Value = "dbo"}, new Identifier {Value = "UpSizeFunction"}), Parameters = new List<WScalarExpression> { new WValueExpression {Value = adjustValue.ToString()} }, Alias = new Identifier { Value = Path.GetRandomFileName().Replace(".", "").Substring(0, 8), } }, UnqualifiedJoinType = UnqualifiedJoinType.CrossApply }; pow--; component.EstimateSize *= adjustValue; } joinTable.FirstTableRef = crossApplyTable; } }
/// <summary> /// Calculate join costs and update components using optimal join method & order /// </summary> /// <param name="nodeUnitCandidate"></param> /// <param name="component"></param> /// <param name="nodeTable"></param> /// <param name="componentTable"></param> /// <param name="joinCondition"></param> /// <param name="nodeDegrees"></param> /// <param name="estimatedNodeUnitSize"></param> /// <param name="estimatedSelectivity"></param> /// <returns></returns> private static WTableReference GetPlanAndUpdateCost( OneHeightTree nodeUnitCandidate, MatchComponent component, WTableReference nodeTable, WTableReference componentTable, WBooleanExpression joinCondition, double nodeDegrees, double joinSelectivity, double estimatedNodeUnitSize, double estimatedSelectivity) { var nodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeDegrees; var componentSize = component.Size; var estimatedCompSize = component.EstimateSize; var cost = nodeUnitSize + componentSize; //var joinSelectivity = // nodeWithJoinMapping.SelectivityProduct; //nodeWithJoinMapping.ExponentialSelevtivityProduct; WQualifiedJoin joinTable = new WQualifiedJoin { FirstTableRef = componentTable, SecondTableRef = nodeTable, JoinCondition = joinCondition, QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Hash }; var node = nodeUnitCandidate.TreeRoot; // If the node is already in the component, then only multiply the degree to get the size double nodeUnitActualSize; if (component.MaterializedNodeSplitCount[node] > 0) { nodeUnitActualSize = nodeDegrees; var cEstEdge = Math.Pow(1000, component.EdgeMaterilizedDict.Count(e=>!e.Value)); var cSize = component.EstimateSize/cEstEdge; var nSize = node.EstimatedRows; if (nSize > cSize) { component.EstimateSize = estimatedNodeUnitSize*cEstEdge*estimatedSelectivity; } else { component.EstimateSize = component.EstimateSize*Math.Pow(1000, nodeUnitCandidate.UnmaterializedEdges.Count)* estimatedSelectivity; } } else { nodeUnitActualSize = nodeUnitSize; component.EstimateSize *= estimatedNodeUnitSize * estimatedSelectivity; } //Update Size component.Size *= nodeUnitActualSize * joinSelectivity; // Update Cost component.Cost += cost; // Debug #if DEBUG //Trace.Write(component.NodeUnits.Count+" "); //foreach (var n in component.NodeUnits.Where(e => e.Key != node.Node.ExposedName)) //{ // Trace.Write(n.Value.NodeRefName); //} //Trace.Write(component.NodeUnits[node.Node.ExposedName].NodeRefName+" "); //Trace.Write(" "+(long)component.Cost+" "+(long)component.Size); //Trace.Write(" "); //foreach (var item in component.PopulatedEdgesName) //{ // Trace.Write(item + " "); //} //Trace.Write("; "); //foreach (var unpopulatedEdge in component.UnpopulatedEdges) //{ // Trace.Write(unpopulatedEdge.Alias + " "); //} //Trace.WriteLine(""); #endif // Update TableRef // Only consider the size in the first join if (component.MaterializedNodeSplitCount.Count == 2 && component.MaterializedNodeSplitCount.All(e => e.Value == 0)) { var nodeInComp = component.MaterializedNodeSplitCount.Keys.First(e => e != node); if (nodeUnitSize < componentSize) { joinTable.FirstTableRef = nodeTable; joinTable.SecondTableRef = componentTable; component.TotalMemory = component.DeltaMemory = nodeUnitSize; component.EstimateTotalMemory = component.EstimateDeltaMemory = estimatedNodeUnitSize; component.RightestTableRefSize = nodeInComp.EstimatedRows; component.FatherOfRightestTableRef = new Tuple<WQualifiedJoin, String>(joinTable, component.GetNodeRefName(nodeInComp)); AdjustEstimation(component, nodeTable, joinTable, nodeUnitSize, estimatedNodeUnitSize, nodeUnitCandidate.TreeRoot.EstimatedRows, new Tuple<WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node))); } else { component.TotalMemory = component.DeltaMemory = componentSize; component.EstimateTotalMemory = component.EstimateDeltaMemory = component.EstimateSize; component.RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; component.FatherOfRightestTableRef = new Tuple<WQualifiedJoin, String>(joinTable, component.GetNodeRefName(node)); AdjustEstimation(component, componentTable, joinTable, componentSize, estimatedCompSize, nodeInComp.EstimatedRows, new Tuple<WQualifiedJoin, string>(joinTable, component.GetNodeRefName(nodeInComp))); } } else { double sizeFactor = 5;//1000; double maxMemory = 1e8; double loopJoinInnerThreshold = 10000; double loopJoinOuterThreshold = 1000000; // Left Deep if (componentSize*sizeFactor < nodeUnitSize) { var curDeltaMemory = componentSize; component.TotalMemory = component.DeltaMemory + curDeltaMemory; component.DeltaMemory = curDeltaMemory; var curDeltaEstimateMemory = component.EstimateSize; component.EstimateTotalMemory = component.EstimateDeltaMemory + curDeltaEstimateMemory; component.EstimateDeltaMemory = curDeltaEstimateMemory; // Adjust estimation in sql server AdjustEstimation(component, componentTable, joinTable, componentSize, estimatedCompSize, component.RightestTableRefSize, component.FatherOfRightestTableRef); component.FatherOfRightestTableRef = new Tuple<WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node)); component.RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; } else { // Loop Join if ( //((nodeUnitSize < loopJoinInnerThreshold /*&& componentSize < loopJoinOuterThreshold*/) || component.DeltaMemory + componentSize > maxMemory / 100) && ((nodeUnitSize < loopJoinInnerThreshold && componentSize < loopJoinOuterThreshold) || component.DeltaMemory + componentSize > maxMemory) && nodeUnitCandidate.MaterializedEdges.Count==0) { component.TotalMemory = component.DeltaMemory; component.EstimateTotalMemory = component.EstimateDeltaMemory; joinTable.JoinHint = JoinHint.Loop; component.EstimateSize = estimatedCompSize*estimatedNodeUnitSize/ nodeUnitCandidate.TreeRoot.TableRowCount; } // Right Deep else { joinTable.FirstTableRef = nodeTable; joinTable.SecondTableRef = componentTable; AdjustEstimation(component, nodeTable, joinTable, nodeUnitSize, estimatedNodeUnitSize, node.EstimatedRows, new Tuple<WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node))); component.TotalMemory += nodeUnitSize; component.DeltaMemory = component.TotalMemory; component.EstimateTotalMemory += estimatedNodeUnitSize; component.EstimateDeltaMemory = component.EstimateTotalMemory; } } } return new WParenthesisTableReference { Table = joinTable }; }
public override void Visit(WSelectQueryBlock node) { if (node.SelectElements == null || node.SelectElements.Count < 2) { throw new GraphViewException("Numbers of select elements mismatch."); } var sourceTableScalar = node.SelectElements[0] as WSelectScalarExpression; if (sourceTableScalar == null) throw new GraphViewException("Source node id should be a scalar expression."); var sourceTableColumn = sourceTableScalar.SelectExpr as WColumnReferenceExpression; if (sourceTableColumn == null) throw new GraphViewException("Source node id column should be a column reference expression."); var sourceTableName = sourceTableColumn.MultiPartIdentifier.Identifiers.Last().Value; var sourceNodeIdExpr = new WColumnReferenceExpression(); foreach (var identifier in sourceTableColumn.MultiPartIdentifier.Identifiers) sourceNodeIdExpr.Add(identifier); sourceNodeIdExpr.AddIdentifier("GlobalNodeId"); if (node.SelectElements.Count < 2) throw new GraphViewException("Source and Sink tables should be specified in select elements."); if (node.GroupByClause != null) { throw new GraphViewException("GROUP BY clause is not allowed in INSERT EDGE statement."); } var collectVarVisitor = new CollectVariableVisitor(); var context = collectVarVisitor.Invoke(node); WColumnReferenceExpression sinkNodeIdExpr = null; for (var index = 1; index < node.SelectElements.Count; ++index) { var element = node.SelectElements[index] as WSelectScalarExpression; if (element == null) throw new GraphViewException("Edge property should be a scalar expression."); //sink table if (index == 1) { var sinkTableColumn = element.SelectExpr as WColumnReferenceExpression; if (sinkTableColumn == null) throw new GraphViewException("Sink node id column should be a column reference expression."); var sinkTableName = sinkTableColumn.MultiPartIdentifier.Identifiers.Last().Value; _sinkTable = context[sinkTableName]; sinkNodeIdExpr = new WColumnReferenceExpression(); foreach (var identifier in sinkTableColumn.MultiPartIdentifier.Identifiers) sinkNodeIdExpr.Add(identifier); sinkNodeIdExpr.AddIdentifier("GlobalNodeId"); } else { _edgeProperties.Add(element.SelectExpr); } } var sourceTable = context[sourceTableName] as WNamedTableReference; if (sourceTable == null) { throw new GraphViewException("Source table of INSERT EDGE statement should be a named table reference."); } var sourceNodeId = new WSelectScalarExpression { SelectExpr = sourceNodeIdExpr, ColumnName = "src", }; var sinkNodeId = new WSelectScalarExpression { SelectExpr = sinkNodeIdExpr, ColumnName = "sink" }; var elements = new List<WSelectElement> { sourceNodeId, sinkNodeId }; var result = new WCommonTableExpression { ExpressionName = new Identifier {Value = _tempTableName}, QueryExpression = new WSelectQueryBlock { FromClause = node.FromClause, WhereClause = new WWhereClause { SearchCondition = node.WhereClause.SearchCondition }, HavingClause = node.HavingClause, OrderByClause = node.OrderByClause, TopRowFilter = node.TopRowFilter, UniqueRowFilter = node.UniqueRowFilter, SelectElements = elements, MatchClause = node.MatchClause } }; _result = result; }
private WCommonTableExpression ConstructDeleteEdgeSelect(WSelectQueryBlock node, string edgeAlias, string tempTableName ,out WTableReference sinkTable, out WTableReference sourceTable) { sourceTable = null; sinkTable = null; var sourceTableScalar = node.SelectElements[0] as WSelectScalarExpression; if (sourceTableScalar == null) return null; var sourceTableColumn = sourceTableScalar.SelectExpr as WColumnReferenceExpression; if (sourceTableColumn == null) return null; var sourceTableName = sourceTableColumn.MultiPartIdentifier.Identifiers.Last().Value; var sourceNodeIdExpr = new WColumnReferenceExpression(); foreach (var identifier in sourceTableColumn.MultiPartIdentifier.Identifiers) sourceNodeIdExpr.Add(identifier); sourceNodeIdExpr.AddIdentifier("GlobalNodeId"); var collectVarVisitor = new CollectVariableVisitor(); var context = collectVarVisitor.Invoke(node); if (!context.NodeTableDictionary.Any()) { throw new GraphViewException("Missing From Clause in Delete Edge statement"); } WColumnReferenceExpression sinkNodeIdExpr = null; sourceTable = context[sourceTableName]; var groupByParams = new List<WScalarExpression>(); for (var index = 1; index < node.SelectElements.Count; ++index) { var element = node.SelectElements[index] as WSelectScalarExpression; if (element == null) return null; //sink table if (index == 1) { var sinkTableColumn = element.SelectExpr as WColumnReferenceExpression; if (sinkTableColumn == null) return null; var sinkTableName = sinkTableColumn.MultiPartIdentifier.Identifiers.Last().Value; sinkTable = context[sinkTableName]; sinkNodeIdExpr = new WColumnReferenceExpression(); foreach (var identifier in sinkTableColumn.MultiPartIdentifier.Identifiers) sinkNodeIdExpr.Add(identifier); sinkNodeIdExpr.AddIdentifier("GlobalNodeId"); } } var sourceNodeId = new WSelectScalarExpression { SelectExpr = sourceNodeIdExpr, ColumnName = "src", }; var sinkNodeId = new WSelectScalarExpression { SelectExpr = sinkNodeIdExpr, ColumnName = "sink" }; var edgeId = new WSelectScalarExpression { SelectExpr = new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier( new Identifier[] { new Identifier {Value = edgeAlias}, new Identifier {Value = "Edgeid"} } ) }, ColumnName = "edgeid" }; var elements = new List<WSelectElement> { sourceNodeId, sinkNodeId, edgeId }; return new WCommonTableExpression { ExpressionName = new Identifier { Value = tempTableName }, QueryExpression = new WSelectQueryBlock { FromClause = node.FromClause, WhereClause = new WWhereClause { SearchCondition = node.WhereClause.SearchCondition }, HavingClause = node.HavingClause, OrderByClause = node.OrderByClause, TopRowFilter = node.TopRowFilter, UniqueRowFilter = node.UniqueRowFilter, SelectElements = elements, MatchClause = node.MatchClause, } }; }
/// <summary> /// Span the table given the edge using cross apply /// </summary> /// <param name="tableRef"></param> /// <param name="edge"></param> /// <param name="nodeAlias"></param> /// <returns></returns> public static WTableReference SpanTableRef(WTableReference tableRef, MatchEdge edge, string nodeAlias) { tableRef = new WUnqualifiedJoin { FirstTableRef = tableRef, SecondTableRef = EdgeToTableReference(edge, nodeAlias), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }; return tableRef; }
/// <summary> /// Calculate join costs and update components using optimal join method & order /// </summary> /// <param name="nodeUnitCandidate"></param> /// <param name="joinCondition"></param> /// <param name="joinSelectivity"></param> /// <param name="estimatedSelectivity"></param> /// <returns></returns> private void ConstructPhysicalJoinAndUpdateCost( CandidateJoinUnit nodeUnitCandidate, WBooleanExpression joinCondition, double joinSelectivity, double estimatedSelectivity, GraphMetaData metaData) { var nodeDegrees = nodeUnitCandidate.EdgeDegrees; var nodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeDegrees; var estimatedNodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows* nodeUnitCandidate.SqlEstimatedEdgeDegrees; var componentSize = Cardinality; var estimatedCompSize = SqlEstimatedSize; var node = nodeUnitCandidate.TreeRoot; // If the node is already in the component, then only multiply the degree to get the size double nodeUnitActualSize; double newCompEstSize; if (MaterializedNodeSplitCount[node] > 0) { nodeUnitActualSize = nodeDegrees; var cEstEdge = Math.Pow(1000, EdgeMaterilizedDict.Count(e => !e.Value)); var cSize = SqlEstimatedSize / cEstEdge; var nSize = node.EstimatedRows; if (nSize > cSize) { newCompEstSize = estimatedNodeUnitSize * cEstEdge * estimatedSelectivity; } else { newCompEstSize = SqlEstimatedSize * Math.Pow(1000, nodeUnitCandidate.UnmaterializedEdges.Count) * estimatedSelectivity; } } else { nodeUnitActualSize = nodeUnitSize; newCompEstSize = SqlEstimatedSize * estimatedNodeUnitSize * estimatedSelectivity; } newCompEstSize = newCompEstSize < 1.0 ? 1.0 : newCompEstSize; bool firstJoin = MaterializedNodeSplitCount.Count == 2 && MaterializedNodeSplitCount.All(e => e.Value == 0); // Update TableRef double loopJoinOuterThreshold = 1e4;//1e6; double sizeFactor = 5;//1000; double maxMemory = 1e8; double loopCost = componentSize*Math.Log(nodeUnitCandidate.TreeRoot.EstimatedRows, 512); double hashCost = componentSize + nodeUnitSize; double cost; // Loop Join if ( nodeUnitCandidate.MaterializedEdges.Count == 0 && // the joins are purely leaf to sink join ( //componentSize < loopJoinOuterThreshold || // the outer table is relatively small loopCost < hashCost || (DeltaMemory + componentSize > maxMemory && DeltaMemory + nodeUnitSize > maxMemory) // memory is in pressure ) ) { if (firstJoin) { RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; RightestTableAlias = GetNodeRefName(node); } TotalMemory = DeltaMemory; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory; //joinTable.JoinHint = JoinHint.Loop; SqlEstimatedSize = estimatedCompSize * estimatedNodeUnitSize / nodeUnitCandidate.TreeRoot.TableRowCount; cost = loopCost; //componentSize*Math.Log(nodeUnitCandidate.TreeRoot.EstimatedRows, 512); TableRef = new WParenthesisTableReference { Table = new WQualifiedJoin { FirstTableRef = TableRef, SecondTableRef = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData), JoinCondition = joinCondition, QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Loop } }; } // Hash Join else { cost = hashCost;//componentSize + nodeUnitSize; WBooleanExpression adjustedJoincondition; double adjustedSqlEstimatedSize; WTableReference buildTableReference; WTableReference probeTableReference; if (firstJoin) { var nodeInComp = MaterializedNodeSplitCount.Keys.First(e => e != node); if (nodeUnitSize < componentSize) { buildTableReference = AdjustEstimation(nodeUnitCandidate, GetNodeRefName(node), metaData, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = TableRef; TotalMemory = DeltaMemory = nodeUnitSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory = estimatedNodeUnitSize; RightestTableRefSize = nodeInComp.EstimatedRows; RightestTableAlias = GetNodeRefName(nodeInComp); } else { RightestTableRefSize = nodeInComp.EstimatedRows; RightestTableAlias = GetNodeRefName(nodeInComp); buildTableReference = AdjustEstimation(this, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData); TotalMemory = DeltaMemory = componentSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory = SqlEstimatedSize; RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; RightestTableAlias = GetNodeRefName(node); } } // Left Deep else if (componentSize*sizeFactor < nodeUnitSize) { // Adjust estimation in sql server buildTableReference = AdjustEstimation(this, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData); var curDeltaMemory = componentSize; TotalMemory = DeltaMemory + curDeltaMemory; DeltaMemory = curDeltaMemory; var curDeltaEstimateMemory = SqlEstimatedSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory + curDeltaEstimateMemory; SqlEstimatedDeltaMemory = curDeltaEstimateMemory; RightestTableAlias = GetNodeRefName(node); RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; } // Right Deep else { buildTableReference = AdjustEstimation(nodeUnitCandidate, GetNodeRefName(node), metaData, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = TableRef; TotalMemory += nodeUnitSize; DeltaMemory = TotalMemory; SqlEstimatedTotalMemory += estimatedNodeUnitSize; SqlEstimatedDeltaMemory = SqlEstimatedTotalMemory; } newCompEstSize *= adjustedSqlEstimatedSize; TableRef = new WParenthesisTableReference { Table = new WQualifiedJoin { FirstTableRef = buildTableReference, SecondTableRef = probeTableReference, JoinCondition = WBooleanBinaryExpression.Conjunction(joinCondition,adjustedJoincondition), QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Hash } }; SqlEstimatedSize = newCompEstSize < 1.0 ? 1.0 : newCompEstSize; } //Update Size Cardinality *= nodeUnitActualSize * joinSelectivity; // Debug #if DEBUG //foreach (var item in MaterializedNodeSplitCount.Where(e => e.Key != node)) //{ // Trace.Write(item.Key.RefAlias + ","); //} //Trace.Write(node.RefAlias); //Trace.Write(" Size:" + Cardinality + " Cost:" + cost); //Trace.Write(" Method:" + ((TableRef as WParenthesisTableReference).Table as WQualifiedJoin).JoinHint); //Trace.WriteLine(" --> Total Cost:" + Cost); #endif // Update Cost Cost += cost; }
private WBooleanExpression ConstructJoinCondition( CandidateJoinUnit candidateTree, IMatchJoinStatisticsCalculator statisticsCalculator, GraphMetaData metaData, out double joinSelectivity, out double sqlEstimatedJoinSelectivity) { joinSelectivity = 1.0; sqlEstimatedJoinSelectivity = 1.0; var root = candidateTree.TreeRoot; WBooleanExpression joinCondition = null; string nodeName = ""; // Update Nodes if (MaterializedNodeSplitCount.ContainsKey(root)) { MaterializedNodeSplitCount[root]++; nodeName = GetNodeRefName(root); joinCondition = new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = root.RefAlias }, new Identifier { Value = "GlobalNodeId" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ), }, ComparisonType = BooleanComparisonType.Equals }; } else { nodeName = root.RefAlias; if (!Nodes.Contains(root)) Nodes.Add(root); MaterializedNodeSplitCount[root] = 0; } List<double> densityList = new List<double>(); List<MatchEdge> inEdges; if (UnmaterializedNodeMapping.TryGetValue(root, out inEdges)) { var firstEdge = inEdges.First(); bool materialized = EdgeMaterilizedDict[firstEdge]; UnmaterializedNodeMapping.Remove(root); joinSelectivity *= 1.0 / root.TableRowCount; // Component materialized edge to root if (materialized) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(root.GlobalNodeIdDensity); } // Component unmaterialized edge to root else { Statistics statistics = null; foreach (var edge in inEdges) { // Update component table TableRef = SpanTableRef(TableRef, edge, GetNodeRefName(edge.SourceNode),metaData); EdgeMaterilizedDict[edge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = edge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); double selectivity; statistics = Statistics.UpdateHistogram(statistics, edge.Statistics, out selectivity); joinSelectivity *= selectivity; densityList.Add(root.GlobalNodeIdDensity); } SinkNodeStatisticsDict[root] = statistics; } } var jointEdges = candidateTree.MaterializedEdges; foreach (var jointEdge in jointEdges) { EdgeMaterilizedDict[jointEdge] = true; var sinkNode = jointEdge.SinkNode; // Leaf to component materialized node if (MaterializedNodeSplitCount.ContainsKey(sinkNode)) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = sinkNode.RefAlias }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); Statistics sinkNodeStatistics; if (!SinkNodeStatisticsDict.TryGetValue(sinkNode, out sinkNodeStatistics)) { sinkNodeStatistics = null; joinSelectivity *= 1.0 / sinkNode.TableRowCount; } double selectivity; var statistics = Statistics.UpdateHistogram(sinkNodeStatistics, jointEdge.Statistics, out selectivity); joinSelectivity *= selectivity; SinkNodeStatisticsDict[sinkNode] = statistics; densityList.Add(sinkNode.GlobalNodeIdDensity); } // Leaf to component unmaterialized node else { inEdges = UnmaterializedNodeMapping[sinkNode]; var firstEdge = inEdges.First(); bool materlizedEdge = EdgeMaterilizedDict[firstEdge]; // Leaf to materialized leaf if (materlizedEdge) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(Statistics.DefaultDensity); double selectivity; var statistics = Statistics.UpdateHistogram(SinkNodeStatisticsDict[sinkNode], jointEdge.Statistics, out selectivity); joinSelectivity *= selectivity; SinkNodeStatisticsDict[sinkNode] = statistics; } // Leaf to unmaterialized leaf else { Statistics compSinkNodeStatistics = null; foreach (var inEdge in inEdges) { TableRef = SpanTableRef(TableRef, inEdge, GetNodeRefName(inEdge.SourceNode),metaData); EdgeMaterilizedDict[inEdge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = inEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(Statistics.DefaultDensity); double selectivity; var leafToLeafStatistics = statisticsCalculator.GetLeafToLeafStatistics(jointEdge, inEdge, out selectivity); joinSelectivity *= selectivity; compSinkNodeStatistics = Statistics.UpdateHistogram(compSinkNodeStatistics, inEdge.Statistics, out selectivity); } SinkNodeStatisticsDict[sinkNode] = compSinkNodeStatistics; } } } var unmatEdges = candidateTree.UnmaterializedEdges; foreach (var unmatEdge in unmatEdges) { EdgeMaterilizedDict[unmatEdge] = false; if (!Nodes.Contains(unmatEdge.SinkNode)) Nodes.Add(unmatEdge.SinkNode); var sinkNodeInEdges = UnmaterializedNodeMapping.GetOrCreate(unmatEdge.SinkNode); sinkNodeInEdges.Add(unmatEdge); } // Calculate Estimated Join Selectivity & Estimated Node Size densityList.Sort(); for (int i = densityList.Count - 1; i >= 0; i--) { sqlEstimatedJoinSelectivity *= Math.Sqrt(sqlEstimatedJoinSelectivity) * densityList[i]; } return joinCondition; }
public virtual void Visit(WTableReference node) { node.AcceptChildren(this); }
private WBooleanExpression ConstructJoinCondition( CandidateJoinUnit candidateTree, IMatchJoinStatisticsCalculator statisticsCalculator, GraphMetaData metaData, out double joinSelectivity, out double sqlEstimatedJoinSelectivity) { joinSelectivity = 1.0; sqlEstimatedJoinSelectivity = 1.0; var root = candidateTree.TreeRoot; WBooleanExpression joinCondition = null; string nodeName = ""; // Update Nodes if (MaterializedNodeSplitCount.ContainsKey(root)) { MaterializedNodeSplitCount[root]++; nodeName = GetNodeRefName(root); joinCondition = new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = root.RefAlias }, new Identifier { Value = "GlobalNodeId" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ), }, ComparisonType = BooleanComparisonType.Equals }; } else { nodeName = root.RefAlias; if (!Nodes.Contains(root)) { Nodes.Add(root); } MaterializedNodeSplitCount[root] = 0; } List <double> densityList = new List <double>(); List <MatchEdge> inEdges; if (UnmaterializedNodeMapping.TryGetValue(root, out inEdges)) { var firstEdge = inEdges.First(); bool materialized = EdgeMaterilizedDict[firstEdge]; UnmaterializedNodeMapping.Remove(root); joinSelectivity *= 1.0 / root.TableRowCount; // Component materialized edge to root if (materialized) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(root.GlobalNodeIdDensity); } // Component unmaterialized edge to root else { Statistics statistics = null; foreach (var edge in inEdges) { // Update component table TableRef = SpanTableRef(TableRef, edge, GetNodeRefName(edge.SourceNode), metaData); EdgeMaterilizedDict[edge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = edge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); double selectivity; statistics = Statistics.UpdateHistogram(statistics, edge.Statistics, out selectivity); joinSelectivity *= selectivity; densityList.Add(root.GlobalNodeIdDensity); } SinkNodeStatisticsDict[root] = statistics; } } var jointEdges = candidateTree.MaterializedEdges; foreach (var jointEdge in jointEdges) { EdgeMaterilizedDict[jointEdge] = true; var sinkNode = jointEdge.SinkNode; // Leaf to component materialized node if (MaterializedNodeSplitCount.ContainsKey(sinkNode)) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = sinkNode.RefAlias }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); Statistics sinkNodeStatistics; if (!SinkNodeStatisticsDict.TryGetValue(sinkNode, out sinkNodeStatistics)) { sinkNodeStatistics = null; joinSelectivity *= 1.0 / sinkNode.TableRowCount; } double selectivity; var statistics = Statistics.UpdateHistogram(sinkNodeStatistics, jointEdge.Statistics, out selectivity); joinSelectivity *= selectivity; SinkNodeStatisticsDict[sinkNode] = statistics; densityList.Add(sinkNode.GlobalNodeIdDensity); } // Leaf to component unmaterialized node else { inEdges = UnmaterializedNodeMapping[sinkNode]; var firstEdge = inEdges.First(); bool materlizedEdge = EdgeMaterilizedDict[firstEdge]; // Leaf to materialized leaf if (materlizedEdge) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(Statistics.DefaultDensity); double selectivity; var statistics = Statistics.UpdateHistogram(SinkNodeStatisticsDict[sinkNode], jointEdge.Statistics, out selectivity); joinSelectivity *= selectivity; SinkNodeStatisticsDict[sinkNode] = statistics; } // Leaf to unmaterialized leaf else { Statistics compSinkNodeStatistics = null; foreach (var inEdge in inEdges) { TableRef = SpanTableRef(TableRef, inEdge, GetNodeRefName(inEdge.SourceNode), metaData); EdgeMaterilizedDict[inEdge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = inEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(Statistics.DefaultDensity); double selectivity; var leafToLeafStatistics = statisticsCalculator.GetLeafToLeafStatistics(jointEdge, inEdge, out selectivity); joinSelectivity *= selectivity; compSinkNodeStatistics = Statistics.UpdateHistogram(compSinkNodeStatistics, inEdge.Statistics, out selectivity); } SinkNodeStatisticsDict[sinkNode] = compSinkNodeStatistics; } } } var unmatEdges = candidateTree.UnmaterializedEdges; foreach (var unmatEdge in unmatEdges) { EdgeMaterilizedDict[unmatEdge] = false; if (!Nodes.Contains(unmatEdge.SinkNode)) { Nodes.Add(unmatEdge.SinkNode); } var sinkNodeInEdges = UnmaterializedNodeMapping.GetOrCreate(unmatEdge.SinkNode); sinkNodeInEdges.Add(unmatEdge); } // Calculate Estimated Join Selectivity & Estimated Node Size densityList.Sort(); for (int i = densityList.Count - 1; i >= 0; i--) { sqlEstimatedJoinSelectivity *= Math.Sqrt(sqlEstimatedJoinSelectivity) * densityList[i]; } return(joinCondition); }
public WCommonTableExpression Invoke(WSqlFragment node, string tempTableName, out WTableReference sinkTable, out List<WScalarExpression> edgeProperties) { _edgeProperties = new List<WScalarExpression> { new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "sink"}) } }; _tempTableName = tempTableName; node.Accept(this); sinkTable = _sinkTable; edgeProperties = _edgeProperties; return _result; }
public MatchComponent(MatchNode node):this() { Nodes.Add(node); MaterializedNodeSplitCount[node] = 0; //SinkNodeStatisticsDict[node] = new Statistics (); Cardinality *= node.EstimatedRows; SqlEstimatedSize *= node.EstimatedRows; TableRef = new WNamedTableReference { Alias = new Identifier { Value = node.RefAlias}, TableObjectName = node.NodeTableObjectName }; }
/// <summary> /// Deep Copy /// </summary> /// <param name="component"></param> public MatchComponent(MatchComponent component) { Nodes = new HashSet<MatchNode>(component.Nodes); EdgeMaterilizedDict = new Dictionary<MatchEdge, bool>(component.EdgeMaterilizedDict); MaterializedNodeSplitCount = new Dictionary<MatchNode, int>(component.MaterializedNodeSplitCount); UnmaterializedNodeMapping = new Dictionary<MatchNode, List<MatchEdge>>(); foreach (var nodeMapping in component.UnmaterializedNodeMapping) { UnmaterializedNodeMapping[nodeMapping.Key] = new List<MatchEdge>(nodeMapping.Value); } SinkNodeStatisticsDict = new Dictionary<MatchNode, Statistics>(component.SinkNodeStatisticsDict); TableRef = component.TableRef; Cardinality = component.Cardinality; Cost = component.Cost; DeltaMemory = component.DeltaMemory; TotalMemory = component.TotalMemory; SqlEstimatedDeltaMemory = component.SqlEstimatedDeltaMemory; SqlEstimatedTotalMemory = component.SqlEstimatedTotalMemory; SqlEstimatedSize = component.SqlEstimatedSize; RightestTableAlias = component.RightestTableAlias; RightestTableRefSize = component.RightestTableRefSize; }