public string Invoke(WBooleanExpression node, Dictionary<string, string> columnToTable) { _tableBind = false; _columnToTable = columnToTable; node.Accept(this); return _tableName; }
public JsonQuery(JsonQuery rhs) { this.NodeProperties = rhs.NodeProperties; this.EdgeProperties = rhs.EdgeProperties; this.RawWhereClause = rhs.RawWhereClause; this.NodeAlias = rhs.NodeAlias; this.EdgeAlias = rhs.EdgeAlias; this.selectDictionary = rhs.selectDictionary; this.FlatProperties = new HashSet <string>(rhs.FlatProperties); this.JoinDictionary = new Dictionary <string, string>(rhs.JoinDictionary); }
protected JsonQuery(SerializationInfo info, StreamingContext context) { this.NodeProperties = GraphViewSerializer.DeserializeList <string>(info, "NodeProperties"); this.EdgeProperties = GraphViewSerializer.DeserializeList <string>(info, "EdgeProperties"); this.NodeAlias = info.GetString("NodeAlias"); this.EdgeAlias = info.GetString("EdgeAlias"); this.selectDictionary = GraphViewSerializer.DeserializeDictionaryList <string, WPrimaryExpression>(info, "selectDictionary", true); this.FlatProperties = GraphViewSerializer.DeserializeHashSet <string>(info, "FlatProperties"); this.JoinDictionary = GraphViewSerializer.DeserializeDictionary <string, string>(info, "JoinDictionary"); this.JsonServerCollectionName = info.GetString("JsonServerCollectionName"); this.dummyQueryString = info.GetString("dummyQuery"); this.RawWhereClause = new WSqlParser().ParseWhereClauseFromSelect(this.dummyQueryString); }
public void WhereConjunction(WBooleanExpression condition, BooleanBinaryExpressionType conjunction) { Debug.Assert(condition != null); if (this.RawWhereClause == null) { this.RawWhereClause = condition; return; } this.RawWhereClause = new WBooleanBinaryExpression { FirstExpr = new WBooleanParenthesisExpression { Expression = this.RawWhereClause }, SecondExpr = new WBooleanParenthesisExpression { Expression = condition }, BooleanExpressionType = conjunction }; }
/// <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 }
private WBooleanExpression ConstructJoinCondition( CandidateJoinUnit candidateTree, IMatchJoinStatisticsCalculator statisticsCalculator, GraphMetaData metaData, Dictionary<Tuple<string, bool>, Statistics> srcNodeStatisticsDict, out double preJoinSelectivity, out double postJoinSelectivity, out double sqlEstimatedJoinSelectivity) { const double sizeThreshold = 1e8; const int loopJoinFactorThreshold = 20; preJoinSelectivity = 1.0; postJoinSelectivity = 1.0; sqlEstimatedJoinSelectivity = 1.0; var firstJoin = MaterializedNodeSplitCount.Count == 1; MatchNode firstNode = null; if (firstJoin) firstNode = Nodes.First(); var root = candidateTree.TreeRoot; WBooleanExpression joinCondition = null; WBooleanExpression whereCondition = null; string nodeName = root.RefAlias; if (!Nodes.Contains(root)) Nodes.Add(root); MaterializedNodeSplitCount[root] = 0; var inEdges = candidateTree.PreMatIncomingEdges.Select( e => new Tuple<MaterializedOrder, MatchEdge>(MaterializedOrder.Pre, e)) .Union( candidateTree.PostMatIncomingEdges.Select( e => new Tuple<MaterializedOrder, MatchEdge>(MaterializedOrder.Post, e))) .ToList(); var outEdges = candidateTree.PreMatOutgoingEdges.Select( e => new Tuple<MaterializedOrder, MatchEdge>(MaterializedOrder.Pre, e)) .Union( candidateTree.PostMatOutgoingEdges.Select( e => new Tuple<MaterializedOrder, MatchEdge>(MaterializedOrder.Post, e))) .ToList(); var densityList = new List<double>(); var inPostCount = 0; var outPostCount = 0; if (inEdges.Any()) { UnmaterializedNodeMapping.Remove(root); //joinSelectivity *= 1.0 / root.TableRowCount; Statistics statistics = null; Statistics srcNodeStat = null; foreach (var t in inEdges) { var order = t.Item1; var edge = t.Item2; var globalNodeIdRef = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier {Value = nodeName}, new Identifier {Value = "GlobalNodeId"} ) }; var newCondition = new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier {Value = edge.EdgeAlias}, new Identifier {Value = "Sink"} ), }, SecondExpr = order == MaterializedOrder.Post && inPostCount > 0 ? new WBinaryExpression { ExpressionType = BinaryExpressionType.Add, FirstExpr = globalNodeIdRef, SecondExpr = new WValueExpression { SingleQuoted = false, Value = "0", } } : (WScalarExpression)globalNodeIdRef, ComparisonType = BooleanComparisonType.Equals }; EdgeMaterilizedDict[edge] = true; double selectivity; statistics = Statistics.UpdateHistogram(statistics, edge.Statistics, out selectivity); if (order == MaterializedOrder.Pre) { preJoinSelectivity *= selectivity; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, newCondition); } else { ++inPostCount; postJoinSelectivity *= selectivity; whereCondition = WBooleanBinaryExpression.Conjunction(whereCondition, newCondition); } if (firstJoin) { double srcNodeSelectivity; srcNodeStat = Statistics.UpdateHistogram(srcNodeStat, srcNodeStatisticsDict[new Tuple<string, bool>(edge.EdgeAlias, edge.IsReversedEdge)], out srcNodeSelectivity); } densityList.Add(root.GlobalNodeIdDensity); } if (firstJoin) SinkNodeStatisticsDict[firstNode] = srcNodeStat; SinkNodeStatisticsDict[root] = statistics; } if (candidateTree.JoinHint == JoinHint.Loop) { var size = Cardinality*candidateTree.PreMatIncomingEdges.Select(e => e.AverageDegree) .Aggregate(1.0, (cur, next) => cur*next)*preJoinSelectivity; if (size >= sizeThreshold && size > root.EstimatedRows * loopJoinFactorThreshold) candidateTree.JoinHint = JoinHint.Hash; } if (outEdges.Any()) { foreach (var t in outEdges) { var order = t.Item1; var edge = t.Item2; var sinkNode = edge.SinkNode; var globalNodeIdRef = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier {Value = sinkNode.RefAlias}, new Identifier {Value = "GlobalNodeId"} ) }; var newCondition = new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier {Value = edge.EdgeAlias}, new Identifier {Value = "Sink"} ), }, SecondExpr = order == MaterializedOrder.Post && outPostCount > 0 ? new WBinaryExpression { ExpressionType = BinaryExpressionType.Add, FirstExpr = globalNodeIdRef, SecondExpr = new WValueExpression { SingleQuoted = false, Value = "0", } } : (WScalarExpression)globalNodeIdRef, ComparisonType = BooleanComparisonType.Equals }; EdgeMaterilizedDict[edge] = true; Statistics sinkNodeStatistics; if (!SinkNodeStatisticsDict.TryGetValue(sinkNode, out sinkNodeStatistics)) { sinkNodeStatistics = null; //joinSelectivity *= 1.0 / sinkNode.TableRowCount; } double selectivity; var statistics = Statistics.UpdateHistogram(sinkNodeStatistics, edge.Statistics, out selectivity); if (order == MaterializedOrder.Pre) { preJoinSelectivity *= selectivity; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, newCondition); } else { ++outPostCount; postJoinSelectivity *= selectivity; whereCondition = WBooleanBinaryExpression.Conjunction(whereCondition, newCondition); } SinkNodeStatisticsDict[sinkNode] = statistics; densityList.Add(sinkNode.GlobalNodeIdDensity); } } var unmatEdges = candidateTree.UnmaterializedEdges; foreach (var unmatEdge in unmatEdges) { EdgeMaterilizedDict[unmatEdge] = false;; var unmatNodeInEdges = UnmaterializedNodeMapping.GetOrCreate(unmatEdge.SinkNode); unmatNodeInEdges.Add(unmatEdge); } densityList.Sort(); for (int i = densityList.Count - 1; i >= 0; i--) { sqlEstimatedJoinSelectivity *= Math.Sqrt(sqlEstimatedJoinSelectivity) * densityList[i]; } WhereCondition = WBooleanBinaryExpression.Conjunction(WhereCondition, whereCondition); return joinCondition; }
/// <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; //} private static WTableReference AdjustEstimation(CandidateJoinUnit candidateJoinUnit, string nodeAlias, GraphMetaData metaData, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize) { const int sizeFactor = 10; int estimateFactor = 0; List<MatchEdge> matEdges = candidateJoinUnit.PreMatOutgoingEdges; double size = matEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next); double estimatedSize = Math.Pow(1000, matEdges.Count); double shrinkSize = candidateJoinUnit.TreeRoot.EstimatedRows; WTableReference tableReference = candidateJoinUnit.ToTableReference(nodeAlias, 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; }
/// <summary> /// Calculate the number used for adjusting the SQL Server estimation in the downsize function. /// </summary> /// <param name="component"></param> /// <param name="matEdges"></param> /// <param name="joinCondition"></param> private static WTableReference AdjustEstimation(MatchComponent component, List<MatchEdge> matEdges, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize) { const int sizeFactor = 10; int estimateFactor = 0; double size = component.Cardinality* matEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur*next); double estimatedSize = component.SqlEstimatedSize * Math.Pow(1000, matEdges.Count); 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; }
public string ToJsonServerString() { // SELECT clause Debug.Assert(this.selectDictionary.Any(), "There is nothing to be selected!"); // TODO: using StringConcat() function when it is available string selectClauseString; if (this.selectDictionary.Count == 1 && this.selectDictionary.ContainsKey("*")) { Debug.Assert(this.selectDictionary["*"] == null, "`*` can't be used with `AS`"); selectClauseString = $"Doc({this.NodeAlias ?? this.EdgeAlias})"; } else { List <string> elements = new List <string>(); foreach (KeyValuePair <string, List <WPrimaryExpression> > kvp in this.selectDictionary) { if (kvp.Value != null && kvp.Value.Any()) { var attributes = new List <string>(); foreach (WPrimaryExpression expression in kvp.Value) { var valueExp = expression as WValueExpression; if (valueExp != null) { attributes.Add($"'{valueExp}'"); continue; } var columnExp = expression as WColumnReferenceExpression; if (columnExp != null) { if (columnExp.ColumnName == "*") { attributes.Add($"Doc({columnExp.TableReference})"); } else if (columnExp.ColumnName[0] == '[') { // NOTE: JsonServer accepts path like: aaa.["bbb"].["ccc"] // TODO: Deal with ["aaa"]["bbb"]["ccc"] --> .["aaa"].["bbb"].["ccc"] attributes.Add($"{columnExp.TableReference}{columnExp.ColumnName.Replace("[", ".[")}"); } else { attributes.Add($"{columnExp.TableReference}.{columnExp.ColumnName}"); } continue; } throw new QueryExecutionException("Un-supported type of SELECT clause expression"); } elements.Add($"StringConcatenate({string.Join(", ", attributes)}) AS {kvp.Key}"); } else if (kvp.Key == "*") { throw new QueryExecutionException("`*` can only be used with no one in SELECT clause."); } else { elements.Add($"Doc({kvp.Key}) AS {kvp.Key}"); } } selectClauseString = $"{string.Join(", ", elements)}"; } // Join clause ( in JsonServer, it is called `FOR`, but you know, `JOIN` is what it really do) // if you get a better name, please refactor it. var joinStrBuilder = new StringBuilder(); foreach (KeyValuePair <string, string> pair in JoinDictionary) { joinStrBuilder.AppendFormat("FOR {0} IN {1}.*\n", pair.Key, pair.Value); } string joinClauseString = joinStrBuilder.ToString(); // Where clause WBooleanExpression whereClauseCopy = this.RawWhereClause.Copy(); // N_18.age --> N_18.age.*._value var jsonServerStringArrayUnfoldVisitor = new JsonServerStringArrayUnfoldVisitor(this.FlatProperties); jsonServerStringArrayUnfoldVisitor.AddSkipTableName(this.EdgeAlias); jsonServerStringArrayUnfoldVisitor.Invoke(whereClauseCopy); ToJsonServerStringVisitor whereVisitor = new ToJsonServerStringVisitor(); whereVisitor.Invoke(whereClauseCopy); string whereClauseString = whereVisitor.GetString().Replace("[", ".["); // TODO: refactor! Debug.Assert(this.JsonServerCollectionName != null, "ToJsonServerString needs the collection name."); return($"FOR {this.NodeAlias ?? this.EdgeAlias} IN ('{this.JsonServerCollectionName}')\n" + $"{joinClauseString}" + $"WHERE {whereClauseString}\n" + $"SELECT {selectClauseString}"); }
public void UpdateWherClause(WWhereClause whereClause, WBooleanExpression node) { if (whereClause.SearchCondition == null) whereClause.SearchCondition = node; else { whereClause.SearchCondition = new WBooleanBinaryExpression { FirstExpr = whereClause.SearchCondition, SecondExpr = node, BooleanExpressionType = BooleanBinaryExpressionType.And }; } }
private void Attach(WBooleanExpression expr) { var table = _bindTableVisitor.Invoke(expr,_columnTableMapping); MatchEdge edge; MatchNode node; if (_graph.TryGetEdge(table,out edge)) { if (edge.Predicates == null) { edge.Predicates = new List<WBooleanExpression>(); } edge.Predicates.Add(expr); } else if (_graph.TryGetNode(table,out node)) { if (node.Predicates == null) { node.Predicates = new List<WBooleanExpression>(); } node.Predicates.Add(expr); } }
/// <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 virtual void Visit(WBooleanExpression node) { node.AcceptChildren(this); }
public string ToDocDbString() { // construct select clause Debug.Assert(this.selectDictionary.Any(), "There is nothing to be selected!"); List <string> elements = new List <string>(); foreach (KeyValuePair <string, List <WPrimaryExpression> > kvp in this.selectDictionary) { Debug.Assert(kvp.Key != "*" || kvp.Value == null, "`*` can't be used with `AS`"); if (kvp.Value != null && kvp.Value.Any()) { var sb = new StringBuilder(); foreach (WPrimaryExpression expression in kvp.Value) { var valueExp = expression as WValueExpression; if (valueExp != null) { sb.Append(valueExp); continue; } var columnExp = expression as WColumnReferenceExpression; if (columnExp != null) { if (columnExp.ColumnName == "*") { sb.Append($"{columnExp.TableReference}"); } else if (columnExp.ColumnName[0] == '[') { // TODO: Refactor, case like doc["partionKey"], try to use AddIdentifier() function of WColumnRefExp. sb.Append($"{columnExp.TableReference}{columnExp.ColumnName}"); } else { sb.Append($"{columnExp.TableReference}.{columnExp.ColumnName}"); } continue; } throw new QueryExecutionException("Un-supported type of SELECT clause expression"); } elements.Add($"{sb} AS {kvp.Key}"); } else { elements.Add($"{kvp.Key}"); } } string selectClauseString = $"SELECT {string.Join(", ", elements)}"; // cpmstruct FROM clause with the first element of SelectAlias var fromStrBuilder = new StringBuilder(); fromStrBuilder.AppendFormat("FROM {0}", this.NodeAlias ?? this.EdgeAlias); // TODO: double check here string fromClauseString = fromStrBuilder.ToString(); // construct JOIN clause, because the order of replacement is not matter, // so use Dictinaty to store it(JoinDictionary). WBooleanExpression whereClauseCopy = this.RawWhereClause.Copy(); // True --> true var booleanWValueExpressionVisitor = new BooleanWValueExpressionVisitor(); booleanWValueExpressionVisitor.Invoke(whereClauseCopy); var normalizeNodePredicatesColumnReferenceExpressionVisitor = new NormalizeNodePredicatesWColumnReferenceExpressionVisitor(null); normalizeNodePredicatesColumnReferenceExpressionVisitor.AddFlatProperties(this.FlatProperties); normalizeNodePredicatesColumnReferenceExpressionVisitor.AddSkipTableName(this.EdgeAlias); Dictionary <string, string> referencedProperties = normalizeNodePredicatesColumnReferenceExpressionVisitor.Invoke(whereClauseCopy); var joinStrBuilder = new StringBuilder(); foreach (KeyValuePair <string, string> referencedProperty in referencedProperties) { joinStrBuilder.AppendFormat(" JOIN {0} IN {1}['{2}'] ", referencedProperty.Key, this.NodeAlias, referencedProperty.Value); } foreach (KeyValuePair <string, string> pair in JoinDictionary) { joinStrBuilder.AppendFormat(" JOIN {0} IN {1} ", pair.Key, pair.Value); } string joinClauseString = joinStrBuilder.ToString(); // WHERE clause // convert some E_6.label --> E_6["label"] if needed(Add 'E_6' to visitor.NeedsConvertion before invoke the visitor). if (this.EdgeAlias != null) { var normalizeEdgePredicatesColumnReferenceExpressionVisitor = new DMultiPartIdentifierVisitor(); normalizeEdgePredicatesColumnReferenceExpressionVisitor.NeedsConvertion.Add(this.EdgeAlias); normalizeEdgePredicatesColumnReferenceExpressionVisitor.Invoke(whereClauseCopy); } // construct where clause string. var docDbStringVisitor = new ToDocDbStringVisitor(); docDbStringVisitor.Invoke(whereClauseCopy); string whereClauseString = $"WHERE ({docDbStringVisitor.GetString()})"; return($"{selectClauseString}\n" + $"{fromClauseString} {joinClauseString}\n" + $"{whereClauseString}"); }
private void Attach(WBooleanExpression expr) { var table = _bindTableVisitor.Invoke(expr,_columnTableMapping); MatchEdge edge, revEdge; MatchNode node; if (_graph.TryGetEdge(table,out edge)) { if (edge.Predicates == null) { edge.Predicates = new List<WBooleanExpression>(); } edge.Predicates.Add(expr); if (_graph.ReversedEdgeDict.TryGetValue(edge.EdgeAlias, out revEdge)) { if (revEdge.Predicates == null) { revEdge.Predicates = new List<WBooleanExpression>(); } // take care when an edge is using a default alias var revExpr = ObjectExtensions.Copy(expr); var compExpr = revExpr as WBooleanComparisonExpression; if (compExpr != null) { var columnExpr = compExpr.FirstExpr as WColumnReferenceExpression ?? compExpr.SecondExpr as WColumnReferenceExpression; if (columnExpr != null) { var column = columnExpr.MultiPartIdentifier.Identifiers; if (column.Count >= 2) { column[column.Count - 2].Value = revEdge.EdgeAlias; } } } revEdge.Predicates.Add(revExpr); } } else if (_graph.TryGetNode(table,out node)) { if (node.Predicates == null) { node.Predicates = new List<WBooleanExpression>(); } node.Predicates.Add(expr); } }
/// <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; }
public bool Invoke(WBooleanExpression node) { _referencedByNodeAndEdge = true; node.Accept(this); return _referencedByNodeAndEdge; }
/// <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; }
/// <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; }
/// <summary> /// Conjuncts two boolean expressions /// </summary> /// <param name="joinCondition">The first boolean expression</param> /// <param name="newCondition">The second boolean expression</param> /// <returns>The conjunctive boolean expression</returns> public static WBooleanExpression Conjunction(WBooleanExpression joinCondition, WBooleanExpression newCondition) { if (joinCondition == null) { return newCondition; } if (newCondition == null) { return joinCondition; } return new WBooleanBinaryExpression { FirstExpr = joinCondition, SecondExpr = newCondition, BooleanExpressionType = BooleanBinaryExpressionType.And }; }