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(WParenthesisTableReference node) { node.AcceptChildren(this); }
/// <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 }