private WTableReference ParseTableReference(TableReference tabRef) { if (tabRef == null) { return null; } var tabRefWithAlias = tabRef as TableReferenceWithAlias; if (tabRefWithAlias!=null && tabRefWithAlias.Alias!=null && GraphViewKeywords._keywords.Contains(tabRefWithAlias.Alias.Value)) { var token = _tokens[tabRefWithAlias.Alias.FirstTokenIndex]; throw new SyntaxErrorException(token.Line, tabRefWithAlias.Alias.Value, "System restricted Name cannot be used"); } switch (tabRef.GetType().Name) { case "NamedTableReference": { var oref = tabRef as NamedTableReference; if (oref.SchemaObject.BaseIdentifier.QuoteType == QuoteType.NotQuoted && (oref.SchemaObject.BaseIdentifier.Value[0] == '@' || oref.SchemaObject.BaseIdentifier.Value[0] == '#')) { var pref = new WSpecialNamedTableReference { Alias = oref.Alias, TableHints = new List<WTableHint>(), FirstTokenIndex = oref.FirstTokenIndex, LastTokenIndex = oref.LastTokenIndex, TableObjectName = ParseSchemaObjectName(oref.SchemaObject), }; if (oref.TableHints != null) { foreach (var hint in oref.TableHints) pref.TableHints.Add(ParseTableHint(hint)); } return pref; } else { var pref = new WNamedTableReference { Alias = oref.Alias, TableHints = new List<WTableHint>(), FirstTokenIndex = oref.FirstTokenIndex, LastTokenIndex = oref.LastTokenIndex, TableObjectName = ParseSchemaObjectName(oref.SchemaObject), }; if (oref.TableHints != null) { foreach (var hint in oref.TableHints) pref.TableHints.Add(ParseTableHint(hint)); } return pref; } } case "QueryDerivedTable": { var oref = tabRef as QueryDerivedTable; var pref = new WQueryDerivedTable { QueryExpr = ParseSelectQueryStatement(oref.QueryExpression), Alias = oref.Alias, Columns = oref.Columns, FirstTokenIndex = oref.FirstTokenIndex, LastTokenIndex = oref.LastTokenIndex, }; return pref; } case "SchemaObjectFunctionTableReference": { var oref = tabRef as SchemaObjectFunctionTableReference; var pref = new WSchemaObjectFunctionTableReference { Alias = oref.Alias, Columns = oref.Columns, SchemaObject = ParseSchemaObjectName(oref.SchemaObject), FirstTokenIndex = oref.FirstTokenIndex, LastTokenIndex = oref.LastTokenIndex }; if (oref.Parameters == null) return pref; pref.Parameters = new List<WScalarExpression>(); foreach (var param in oref.Parameters) pref.Parameters.Add(ParseScalarExpression(param)); return pref; } case "QualifiedJoin": { var oref = tabRef as QualifiedJoin; var pref = new WQualifiedJoin { FirstTableRef = ParseTableReference(oref.FirstTableReference), SecondTableRef = ParseTableReference(oref.SecondTableReference), QualifiedJoinType = oref.QualifiedJoinType, JoinHint = oref.JoinHint, JoinCondition = ParseBooleanExpression(oref.SearchCondition), FirstTokenIndex = oref.FirstTokenIndex, LastTokenIndex = oref.LastTokenIndex, }; return pref; } case "UnqualifiedJoin": { var oref = tabRef as UnqualifiedJoin; var pref = new WUnqualifiedJoin { FirstTableRef = ParseTableReference(oref.FirstTableReference), SecondTableRef = ParseTableReference(oref.SecondTableReference), UnqualifiedJoinType = oref.UnqualifiedJoinType, FirstTokenIndex = oref.FirstTokenIndex, LastTokenIndex = oref.LastTokenIndex, }; return pref; } case "JoinParenthesisTableReference": { var ptab = tabRef as JoinParenthesisTableReference; var wptab = new WParenthesisTableReference { Table = ParseTableReference(ptab.Join), FirstTokenIndex = ptab.FirstTokenIndex, LastTokenIndex = ptab.LastTokenIndex, }; return wptab; } default: return null; } }
public virtual void Visit(WQualifiedJoin node) { node.AcceptChildren(this); }
/// <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> /// 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="joinCondition"></param> /// <param name="preJoinSelectivity"></param> /// <param name="postJoinSelectivity"></param> /// <param name="estimatedSelectivity"></param> /// <param name="metaData"></param> /// <param name="isExecutable"></param> private void ConstructPhysicalJoinAndUpdateCost( CandidateJoinUnit nodeUnitCandidate, WBooleanExpression joinCondition, double preJoinSelectivity, double postJoinSelectivity, double estimatedSelectivity, GraphMetaData metaData) { const double scaleFactor = 1.5; const int sqlInPreMatEdgeSelectivityThreshold = 5; var firstJoin = MaterializedNodeSplitCount.Count == 2; var inPreMatEdges = nodeUnitCandidate.PreMatIncomingEdges; var inPostMatEdges = nodeUnitCandidate.PostMatIncomingEdges; var outPreMatEdges = nodeUnitCandidate.PreMatOutgoingEdges; var outPostMatEdges = nodeUnitCandidate.PostMatOutgoingEdges; var postMatEdges = inPostMatEdges.Select(e => new Tuple<MatchEdge, EdgeDir>(e, EdgeDir.In)) .Union( outPostMatEdges.Select(e => new Tuple<MatchEdge, EdgeDir>(e, EdgeDir.Out))) .OrderBy(t => t.Item1.AverageDegree) .ToList(); var compDegrees = inPreMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur*next); var nodeDegrees = outPreMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next); var root = nodeUnitCandidate.TreeRoot; var componentSize = Cardinality; double sizeFactor = 5; // 1000; var loopCost = nodeUnitCandidate.JoinHint == JoinHint.Loop ? componentSize * compDegrees * Math.Log(root.EstimatedRows, 512) : double.MaxValue; // only calc the size of table used to join var matCompSizeWhenJoin = componentSize*compDegrees; var matUnitSizeWhenJoin = root.EstimatedRows*nodeDegrees; var hashCost = matCompSizeWhenJoin + matUnitSizeWhenJoin; double loopJoinOuterThreshold = 1e4;//1e6; double maxMemory = 1e8; double cost; // loop join if (nodeUnitCandidate.JoinHint == JoinHint.Loop //inPreMatEdges.Any() && !outPreMatEdges.Any() && //( // //componentSize < loopJoinOuterThreshold || // the outer table is relatively small // loopCost < hashCost || // (DeltaMemory + matCompSizeWhenJoin > maxMemory && DeltaMemory + matUnitSizeWhenJoin > maxMemory) // // memory is in pressure //) ) { if (firstJoin) { RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; RightestTableAlias = root.RefAlias; LastTable = Nodes.First(n => n.NodeAlias != root.NodeAlias); //LastTableAlias = LastTable.RefAlias; LastJoinHint = JoinHint.Loop; LastJoinSqlEstCardinality = LastTable.TableRowCount; } cost = loopCost; var sqlInPreMatEdgesSelectivity = 1.0; for (var i = 1; i < inPreMatEdges.Count && i <= sqlInPreMatEdgeSelectivityThreshold; ++i) sqlInPreMatEdgesSelectivity = Math.Sqrt(sqlInPreMatEdgesSelectivity)/10; var sqlEstPreJoinEdgeSize = Math.Pow(100, LastJoinPostMatEdgesCount)*Math.Pow(1000, inPreMatEdges.Count)* sqlInPreMatEdgesSelectivity; var sqlEstPreJoinInputSize = LastJoinSqlEstCardinality * (LastJoinHint == JoinHint.Loop ? LastTable.EstimatedRows / LastTable.TableRowCount : 1.0) * sqlEstPreJoinEdgeSize; var estimateFactor = 0; if (matCompSizeWhenJoin >= sqlEstPreJoinInputSize * scaleFactor) estimateFactor = (int) Math.Ceiling(matCompSizeWhenJoin/sqlEstPreJoinInputSize); else if (matCompSizeWhenJoin*scaleFactor < sqlEstPreJoinInputSize) estimateFactor = -1; var affectedUpsize = 1.0; if (estimateFactor >= (int) Math.Ceiling(scaleFactor)) { if (LastJoinHint == JoinHint.Loop) TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, LastTableAlias, DumbType.Node, out affectedUpsize); else if (LastJoinPostMatEdgesCount > 0) TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, LastPostMatEdgeAlias, DumbType.Edge, out affectedUpsize); else TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, out affectedUpsize); } else if (estimateFactor == -1 && LastJoinHint == JoinHint.Loop) { sqlEstPreJoinInputSize = LastJoinSqlEstCardinality* Math.Sqrt(LastTable.EstimatedRows/LastTable.TableRowCount)/ LastTable.TableRowCount * 1.5 * sqlEstPreJoinEdgeSize; if (matCompSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor) TableRef = ConstructUpSizeTableReference(TableRef, (int) Math.Ceiling(matCompSizeWhenJoin/sqlEstPreJoinInputSize), LastTableAlias, DumbType.Node, out affectedUpsize); } foreach (var edge in inPreMatEdges) TableRef = SpanTableRef(TableRef, edge, edge.SourceNode.RefAlias, LastTableAlias, metaData); WTableReference table = new WQualifiedJoin { FirstTableRef = TableRef, SecondTableRef = nodeUnitCandidate.ToTableReference(root.RefAlias, root.RefAlias, metaData), JoinCondition = estimateFactor == -1 ? WBooleanBinaryExpression.Conjunction(joinCondition, ConstructDownSizeJoinCondition(LastTableAlias)) : joinCondition, QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Loop }; table = postMatEdges.Aggregate(table, (current, next) => new WUnqualifiedJoin { FirstTableRef = current, SecondTableRef = next.Item1.ToSchemaObjectFunction(next.Item1.SourceNode.RefAlias, next.Item2 == EdgeDir.In ? root.RefAlias : LastTableAlias, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }); TableRef = new WParenthesisTableReference { Table = table, }; LastTable = root; LastTableAlias = root.RefAlias; LastJoinHint = JoinHint.Loop; LastJoinSqlEstCardinality = sqlEstPreJoinInputSize*affectedUpsize; LastJoinPostMatEdgesCount = postMatEdges.Count; LastPostMatEdgeAlias = LastJoinPostMatEdgesCount > 0 ? postMatEdges.Last().Item1.EdgeAlias : null; SqlEstimatedSize = sqlEstPreJoinInputSize*root.EstimatedRows/root.TableRowCount* Math.Pow(100, postMatEdges.Count); SqlEstimatedSize = SqlEstimatedSize < 1.0 ? 1.0 : SqlEstimatedSize; Cardinality = matCompSizeWhenJoin * inPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) * matUnitSizeWhenJoin * outPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) * preJoinSelectivity / root.TableRowCount * postJoinSelectivity; } // hash join else { cost = hashCost; WBooleanExpression adjustedJoincondition = null; WTableReference buildTableReference = nodeUnitCandidate.ToTableReference(root.RefAlias, root.RefAlias, metaData); double affectedUpsize = 1.0, sqlEstPreJoinEdgeSize; int estimateFactor; var sqlEstPreJoinInputSize = root.EstimatedRows; if (firstJoin) { LastTable = Nodes.First(n => n.NodeAlias != root.NodeAlias); //LastTableAlias = LastTable.RefAlias; LastJoinHint = JoinHint.Loop; LastJoinSqlEstCardinality = LastTable.TableRowCount; } // Build table adjustment if (outPreMatEdges.Any()) { var sqlOutPreMatEdgesSelectivity = 1.0; foreach (var group in outPreMatEdges.GroupBy(e => e.SinkNode)) { var selectivity = 1.0; for (var i = 1; i < group.Count() && i <= sqlInPreMatEdgeSelectivityThreshold; ++i) selectivity = Math.Sqrt(selectivity) / 10; sqlOutPreMatEdgesSelectivity *= selectivity; } sqlEstPreJoinEdgeSize = Math.Pow(1000, outPreMatEdges.Count) * sqlOutPreMatEdgesSelectivity; sqlEstPreJoinInputSize *= sqlEstPreJoinEdgeSize; estimateFactor = 0; if (matUnitSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor) estimateFactor = (int) Math.Ceiling(matUnitSizeWhenJoin/sqlEstPreJoinInputSize); else if (matUnitSizeWhenJoin*scaleFactor < sqlEstPreJoinInputSize) { estimateFactor = -1; adjustedJoincondition = ConstructDownSizeJoinCondition(root.RefAlias); } if (estimateFactor >= (int) Math.Ceiling(scaleFactor)) buildTableReference = ConstructUpSizeTableReference(buildTableReference, estimateFactor, root.RefAlias, DumbType.Node, out affectedUpsize); else if (estimateFactor == -1) { sqlEstPreJoinInputSize = Math.Sqrt(root.EstimatedRows/root.TableRowCount)*1.5*sqlEstPreJoinEdgeSize; if (matUnitSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor) buildTableReference = ConstructUpSizeTableReference(buildTableReference, (int)Math.Ceiling(matUnitSizeWhenJoin / sqlEstPreJoinInputSize), root.RefAlias, DumbType.Node, out affectedUpsize); } } sqlEstPreJoinInputSize *= affectedUpsize; // Cardinality update Cardinality = matCompSizeWhenJoin * inPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) * matUnitSizeWhenJoin * outPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) * preJoinSelectivity / root.TableRowCount * postJoinSelectivity; // Output adjustment var postJoinUpsizeFactor = -1; var sqlInPreMatEdgesSelectivity = 1.0; for (var i = 1; i < inPreMatEdges.Count; ++i) sqlInPreMatEdgesSelectivity = Math.Sqrt(sqlInPreMatEdgesSelectivity) / 10; sqlEstPreJoinEdgeSize = Math.Pow(100, LastJoinPostMatEdgesCount) * Math.Pow(1000, inPreMatEdges.Count) * sqlInPreMatEdgesSelectivity; var probeSqlEstCardinality = LastJoinSqlEstCardinality * (LastJoinHint == JoinHint.Loop ? LastTable.EstimatedRows / LastTable.TableRowCount : 1.0) * sqlEstPreJoinEdgeSize; var hashJoinSqlSelectivity = outPreMatEdges.Any() ? Math.Pow(Math.Sqrt(0.001), outPreMatEdges.GroupBy(e => e.SinkNode).Count()) : (root.EstimatedRows/root.TableRowCount) / root.EstimatedRows; var sqlEstHashCardinality = sqlEstPreJoinInputSize*probeSqlEstCardinality*hashJoinSqlSelectivity* Math.Pow(100, postMatEdges.Count); estimateFactor = 0; if (Cardinality >= sqlEstHashCardinality*scaleFactor) estimateFactor = (int) Math.Ceiling(Cardinality/sqlEstHashCardinality); else if (Cardinality*scaleFactor < sqlEstHashCardinality) { estimateFactor = -1; adjustedJoincondition = WBooleanBinaryExpression.Conjunction(adjustedJoincondition, ConstructDownSizeJoinCondition(LastTableAlias)); } if (estimateFactor >= (int) Math.Ceiling(scaleFactor)) postJoinUpsizeFactor = estimateFactor; else if (estimateFactor == -1 && LastJoinHint == JoinHint.Loop) { probeSqlEstCardinality = LastJoinSqlEstCardinality* Math.Sqrt(LastTable.EstimatedRows/LastTable.TableRowCount)/ LastTable.TableRowCount * 1.5 * sqlEstPreJoinEdgeSize; sqlEstHashCardinality = sqlEstPreJoinInputSize*probeSqlEstCardinality*hashJoinSqlSelectivity* Math.Pow(100, postMatEdges.Count); if (Cardinality >= sqlEstHashCardinality*scaleFactor) postJoinUpsizeFactor = (int) Math.Ceiling(Cardinality/sqlEstHashCardinality); } foreach (var edge in inPreMatEdges) TableRef = SpanTableRef(TableRef, edge, edge.SourceNode.RefAlias, LastTableAlias, metaData); WTableReference table = new WQualifiedJoin { FirstTableRef = buildTableReference, SecondTableRef = TableRef, JoinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, adjustedJoincondition), QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Hash }; table = postMatEdges.Aggregate(table, (current, next) => new WUnqualifiedJoin { FirstTableRef = current, SecondTableRef = next.Item1.ToSchemaObjectFunction(next.Item1.SourceNode.RefAlias, next.Item2 == EdgeDir.In ? root.RefAlias : LastTableAlias, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }); if (postJoinUpsizeFactor != -1) { if (postMatEdges.Any()) table = ConstructUpSizeTableReference(table, postJoinUpsizeFactor, postMatEdges.Last().Item1.EdgeAlias, DumbType.Edge, out affectedUpsize); else table = ConstructUpSizeTableReference(table, postJoinUpsizeFactor, out affectedUpsize); } TableRef = new WParenthesisTableReference { Table = table, }; LastJoinHint = JoinHint.Hash; LastJoinSqlEstCardinality = sqlEstHashCardinality * affectedUpsize; LastJoinPostMatEdgesCount = 0; LastPostMatEdgeAlias = null; SqlEstimatedSize = LastJoinSqlEstCardinality; SqlEstimatedSize = SqlEstimatedSize < 1.0 ? 1.0 : SqlEstimatedSize; } Cost += cost; // Debug #if DEBUG //foreach (var item in MaterializedNodeSplitCount.Where(e => e.Key != node)) //{ // Trace.Write(item.Key.RefAlias + ","); //} //Trace.Write(root.RefAlias); //Trace.Write(" Size:" + Cardinality + " Cost:" + cost); //Trace.Write(" Method:" + ((TableRef as WParenthesisTableReference).Table as WQualifiedJoin).JoinHint); //Trace.WriteLine(" --> Total Cost:" + Cost); #endif }
/// <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> /// 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; } }