public WTableReference ToTableReference(string nodeAlias,GraphMetaData metaData) { // Constructs table reference WTableReference nodeTable = new WNamedTableReference { Alias = new Identifier { Value = nodeAlias }, TableObjectName = TreeRoot.NodeTableObjectName }; nodeTable = MaterializedEdges.Aggregate(nodeTable, (current, edge) => new WUnqualifiedJoin { FirstTableRef = current, SecondTableRef = edge.ToSchemaObjectFunction(nodeAlias, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }); return nodeTable; }
public MatchComponent(MatchNode node, List <MatchEdge> populatedEdges, GraphMetaData metaData) : this(node) { foreach (var edge in populatedEdges) { TableRef = SpanTableRef(TableRef, edge, node.RefAlias, metaData); EdgeMaterilizedDict[edge] = true; SinkNodeStatisticsDict[edge.SinkNode] = edge.Statistics; var edgeList = UnmaterializedNodeMapping.GetOrCreate(edge.SinkNode); edgeList.Add(edge); if (!Nodes.Contains(edge.SinkNode)) { Nodes.Add(edge.SinkNode); } Cardinality *= edge.AverageDegree; SqlEstimatedSize *= 1000; } }
public WTableReference ToTableReference(string nodeAlias, GraphMetaData metaData) { // Constructs table reference WTableReference nodeTable = new WNamedTableReference { Alias = new Identifier { Value = nodeAlias }, TableObjectName = TreeRoot.NodeTableObjectName }; nodeTable = MaterializedEdges.Aggregate(nodeTable, (current, edge) => new WUnqualifiedJoin { FirstTableRef = current, SecondTableRef = edge.ToSchemaObjectFunction(nodeAlias, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }); return(nodeTable); }
/// <summary> /// Transit from current component to the new component in the next state given the Node Unit /// </summary> /// <param name="candidateTree"></param> /// <param name="statisticsCalculator"></param> /// <returns></returns> public MatchComponent GetNextState( CandidateJoinUnit candidateTree, IMatchJoinStatisticsCalculator statisticsCalculator, GraphMetaData metaData) { // Deep copy the component var newComponent = new MatchComponent(this); // Constrcuts join conditions and retrieves join selectivity double joinSelectivity; double sqlEstimatedJoinSelectivity; var joinCondition = newComponent.ConstructJoinCondition(candidateTree, statisticsCalculator, metaData, out joinSelectivity, out sqlEstimatedJoinSelectivity); // Constructs physical join method and join table references newComponent.ConstructPhysicalJoinAndUpdateCost(candidateTree, joinCondition, joinSelectivity, sqlEstimatedJoinSelectivity, metaData); return(newComponent); }
private void ConstructSelectGraph() { QueryGraph = GraphViewDocDBCommand.DocDB_ConstructGraph(SelectQueryBlock); NodeTable = QueryGraph.ConnectedSubGraphs[0].Nodes; var AttachPredicateVisitor = new AttachWhereClauseVisitor(); var TableContext = new WSqlTableContext(); var GraphMeta = new GraphMetaData(); var columnTableMapping = TableContext.GetColumnToAliasMapping(GraphMeta.ColumnsOfNodeTables); if (SelectQueryBlock != null) { AttachPredicateVisitor.Invoke(SelectQueryBlock.WhereClause, QueryGraph, columnTableMapping); } int GroupNumber = 0; foreach (var node in NodeTable) { GraphViewDocDBCommand.GetQuery(node.Value); if (!GraphDescription.ContainsKey(node.Value.NodeAlias)) { GraphDescription[node.Value.NodeAlias] = ++GroupNumber; } } }
/// <summary> /// Span the table given the edge using cross apply /// </summary> /// <param name="tableRef"></param> /// <param name="edge"></param> /// <param name="nodeAlias"></param> /// <returns></returns> public WTableReference SpanTableRef(WTableReference tableRef, MatchEdge edge, string nodeAlias, GraphMetaData metaData) { tableRef = new WUnqualifiedJoin { FirstTableRef = tableRef, SecondTableRef = edge.ToSchemaObjectFunction(nodeAlias, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }; return(tableRef); }
private WBooleanExpression ConstructJoinCondition( CandidateJoinUnit candidateTree, IMatchJoinStatisticsCalculator statisticsCalculator, GraphMetaData metaData, out double joinSelectivity, out double sqlEstimatedJoinSelectivity) { joinSelectivity = 1.0; sqlEstimatedJoinSelectivity = 1.0; var root = candidateTree.TreeRoot; WBooleanExpression joinCondition = null; string nodeName = ""; // Update Nodes if (MaterializedNodeSplitCount.ContainsKey(root)) { MaterializedNodeSplitCount[root]++; nodeName = GetNodeRefName(root); joinCondition = new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = root.RefAlias }, new Identifier { Value = "GlobalNodeId" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ), }, ComparisonType = BooleanComparisonType.Equals }; } else { nodeName = root.RefAlias; if (!Nodes.Contains(root)) Nodes.Add(root); MaterializedNodeSplitCount[root] = 0; } List<double> densityList = new List<double>(); List<MatchEdge> inEdges; if (UnmaterializedNodeMapping.TryGetValue(root, out inEdges)) { var firstEdge = inEdges.First(); bool materialized = EdgeMaterilizedDict[firstEdge]; UnmaterializedNodeMapping.Remove(root); joinSelectivity *= 1.0 / root.TableRowCount; // Component materialized edge to root if (materialized) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(root.GlobalNodeIdDensity); } // Component unmaterialized edge to root else { Statistics statistics = null; foreach (var edge in inEdges) { // Update component table TableRef = SpanTableRef(TableRef, edge, GetNodeRefName(edge.SourceNode),metaData); EdgeMaterilizedDict[edge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = edge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); double selectivity; statistics = Statistics.UpdateHistogram(statistics, edge.Statistics, out selectivity); joinSelectivity *= selectivity; densityList.Add(root.GlobalNodeIdDensity); } SinkNodeStatisticsDict[root] = statistics; } } var jointEdges = candidateTree.MaterializedEdges; foreach (var jointEdge in jointEdges) { EdgeMaterilizedDict[jointEdge] = true; var sinkNode = jointEdge.SinkNode; // Leaf to component materialized node if (MaterializedNodeSplitCount.ContainsKey(sinkNode)) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = sinkNode.RefAlias }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); Statistics sinkNodeStatistics; if (!SinkNodeStatisticsDict.TryGetValue(sinkNode, out sinkNodeStatistics)) { sinkNodeStatistics = null; joinSelectivity *= 1.0 / sinkNode.TableRowCount; } double selectivity; var statistics = Statistics.UpdateHistogram(sinkNodeStatistics, jointEdge.Statistics, out selectivity); joinSelectivity *= selectivity; SinkNodeStatisticsDict[sinkNode] = statistics; densityList.Add(sinkNode.GlobalNodeIdDensity); } // Leaf to component unmaterialized node else { inEdges = UnmaterializedNodeMapping[sinkNode]; var firstEdge = inEdges.First(); bool materlizedEdge = EdgeMaterilizedDict[firstEdge]; // Leaf to materialized leaf if (materlizedEdge) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(Statistics.DefaultDensity); double selectivity; var statistics = Statistics.UpdateHistogram(SinkNodeStatisticsDict[sinkNode], jointEdge.Statistics, out selectivity); joinSelectivity *= selectivity; SinkNodeStatisticsDict[sinkNode] = statistics; } // Leaf to unmaterialized leaf else { Statistics compSinkNodeStatistics = null; foreach (var inEdge in inEdges) { TableRef = SpanTableRef(TableRef, inEdge, GetNodeRefName(inEdge.SourceNode),metaData); EdgeMaterilizedDict[inEdge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = inEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(Statistics.DefaultDensity); double selectivity; var leafToLeafStatistics = statisticsCalculator.GetLeafToLeafStatistics(jointEdge, inEdge, out selectivity); joinSelectivity *= selectivity; compSinkNodeStatistics = Statistics.UpdateHistogram(compSinkNodeStatistics, inEdge.Statistics, out selectivity); } SinkNodeStatisticsDict[sinkNode] = compSinkNodeStatistics; } } } var unmatEdges = candidateTree.UnmaterializedEdges; foreach (var unmatEdge in unmatEdges) { EdgeMaterilizedDict[unmatEdge] = false; if (!Nodes.Contains(unmatEdge.SinkNode)) Nodes.Add(unmatEdge.SinkNode); var sinkNodeInEdges = UnmaterializedNodeMapping.GetOrCreate(unmatEdge.SinkNode); sinkNodeInEdges.Add(unmatEdge); } // Calculate Estimated Join Selectivity & Estimated Node Size densityList.Sort(); for (int i = densityList.Count - 1; i >= 0; i--) { sqlEstimatedJoinSelectivity *= Math.Sqrt(sqlEstimatedJoinSelectivity) * densityList[i]; } return joinCondition; }
/// <summary> /// Converts the edge to the table-valued function /// </summary> /// <param name="nodeAlias">Source node alias</param> /// <param name="dumbNode"></param> /// <param name="metaData">Meta data</param> /// <returns>A syntax tree node representing the table-valued function</returns> public override WSchemaObjectFunctionTableReference ToSchemaObjectFunction(string nodeAlias, string dumbNode, GraphMetaData metaData) { var edgeIdentifiers = EdgeColumn.MultiPartIdentifier.Identifiers; var edgeColIdentifier = edgeIdentifiers.Last(); HashSet <string> nodeSet; if (!metaData.NodeViewMapping.TryGetValue( WNamedTableReference.SchemaNameToTuple(SourceNode.NodeTableObjectName), out nodeSet)) { nodeSet = null; } var sourceNodeColumns = metaData.ColumnsOfNodeTables[WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName)]; var edgeInfo = sourceNodeColumns[edgeColIdentifier.Value].EdgeInfo; List <Tuple <string, string> > edgeTuples = edgeInfo.EdgeColumns; var parameters = ConstructEdgeTvfParameters(nodeAlias, null, nodeSet, edgeTuples); Identifier decoderFunction; if (ReferencePathInfo) { decoderFunction = new Identifier { Value = BindNodeTableObjName.SchemaIdentifier.Value + '_' + BindNodeTableObjName.BaseIdentifier.Value + '_' + EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + '_' + "bfsPathWithMessage" }; // Node view if (nodeSet != null) { parameters.Insert(0, new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier() { Value = SourceNode.RefAlias }, new Identifier() { Value = "_NodeId" }) }); parameters.Insert(0, new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier() { Value = SourceNode.RefAlias }, new Identifier() { Value = "_NodeType" }) }); } else { string nodeIdName = sourceNodeColumns.FirstOrDefault(e => e.Value.Role == WNodeTableColumnRole.NodeId).Key; if (string.IsNullOrEmpty(nodeIdName)) { parameters.Insert(0, new WValueExpression { Value = "null" }); } else { parameters.Insert(0, new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier() { Value = SourceNode.RefAlias }, new Identifier() { Value = nodeIdName }) }); } parameters.Insert(0, new WValueExpression { Value = BindNodeTableObjName.BaseIdentifier.Value, SingleQuoted = true }); } } else { decoderFunction = new Identifier { Value = BindNodeTableObjName.SchemaIdentifier.Value + '_' + BindNodeTableObjName.BaseIdentifier.Value + '_' + EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + '_' + "bfsPath" }; } parameters.Insert(0, new WValueExpression { Value = MaxLength.ToString() }); parameters.Insert(0, new WValueExpression { Value = MinLength.ToString() }); parameters.Insert(0, new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new[] { new Identifier { Value = nodeAlias }, new Identifier { Value = "GlobalNodeId" }, }) }); var attributes = edgeInfo.ColumnAttributes; if (AttributeValueDict == null) { WValueExpression nullExpression = new WValueExpression { Value = "null" }; for (int i = 0; i < attributes.Count; i++) { parameters.Add(nullExpression); } } else { foreach (var attribute in attributes) { string value; var valueExpression = new WValueExpression { Value = AttributeValueDict.TryGetValue(attribute, out value) ? value : "null" }; parameters.Add(valueExpression); } } return(new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, decoderFunction), Parameters = parameters, Alias = new Identifier { Value = EdgeAlias, } }); }
/// <summary> /// Retrieve the metadata /// </summary> /// <param name="conn"></param> private void Init() { _graphMetaData = new GraphMetaData(); var columnsOfNodeTables = _graphMetaData.ColumnsOfNodeTables; var nodeViewMapping = _graphMetaData.NodeViewMapping; var revEdgeOrderDict = _graphMetaData.ReversedEdgeOrder; using (var command = Tx.Connection.CreateCommand()) { command.Transaction = Tx; command.CommandText = string.Format( @" SELECT [TableSchema] as [Schema], [TableName] as [Name1], [ColumnName] as [Name2], [ColumnRole] as [Role], [Reference] as [Name3], [HasReversedEdge] as [HasRevEdge], [IsReversedEdge] as [IsRevEdge], [EdgeUdfPrefix] as [UdfPrefix], null as [EdgeViewTable], null as [ColumnId] FROM [{0}] UNION ALL SELECT [TableSchema] as [Schema], [TableName] as [Name1], [ColumnName] as [Name2], -1 as [Role], [AttributeName] as [Name3], 0, 0, null, null, [AttributeId] FROM [{1}] UNION ALL SELECT [NV].[TableSchema] as [Schema], [NV].[TableName] as [Name1], [NT].[TableName] as [Name2], -2 as [Role], null as [Name3], 0, 0, null, null, null FROM [{2}] as [NV_NT_Mapping] JOIN [{3}] as [NV] ON NV_NT_Mapping.NodeViewTableId = NV.TableId JOIN [{3}] as [NT] ON NV_NT_Mapping.TableId = NT.TableId UNION ALL SELECT [EV].[TableSchema] as [Schema], [EV].[ColumnName] as [Name1], [ED].[ColumnName]as [Name2], -3 as [Role], [ED].[TableName] as [Name3], 0, 0, null, [EV].[TableName] as [EdgeViewTable], [ED].[ColumnId] as [ColumnId] FROM [{4}] as [EV_ED_Mapping] JOIN [{0}] as [EV] ON [EV_ED_Mapping].[NodeViewColumnId] = [EV].[ColumnId] and [EV].[ColumnRole] = 3 JOIN [{0}] as [ED] ON [EV_ED_Mapping].[ColumnId] = [ED].[ColumnId] ORDER BY [ColumnId]", GraphViewConnection.MetadataTables[1], GraphViewConnection.MetadataTables[2], GraphViewConnection.MetadataTables[7], GraphViewConnection.MetadataTables[0], GraphViewConnection.MetadataTables[5]); var revEdgeOrder = 0; using (var reader = command.ExecuteReader()) { while (reader.Read()) { int tag = (int) reader["Role"]; string schema = reader["Schema"].ToString().ToLower(CultureInfo.CurrentCulture); string name1 = reader["Name1"].ToString().ToLower(CultureInfo.CurrentCulture); string name2 = reader["Name2"].ToString().ToLower(CultureInfo.CurrentCulture); bool hasRevEdge = reader["HasRevEdge"].ToString().Equals("1"); bool isRevEdge = reader["IsRevEdge"].ToString().Equals("1"); string udfPrefix = reader["UdfPrefix"].ToString().ToLower(CultureInfo.CurrentCulture); // Retrieve columns of node tables var tableTuple = new Tuple<string, string>(schema, name1); if (tag >= 0) { Dictionary<string, NodeColumns> columnDict; if (!columnsOfNodeTables.TryGetValue(tableTuple, out columnDict)) { columnDict = new Dictionary<string, NodeColumns>(StringComparer.OrdinalIgnoreCase); columnsOfNodeTables.Add(tableTuple, columnDict); } var role = (WNodeTableColumnRole) tag; EdgeInfo edgeInfo = null; // Edge column if (role == WNodeTableColumnRole.Edge || role == WNodeTableColumnRole.EdgeView) { edgeInfo = new EdgeInfo { ColumnAttributes = new List<string>(), SinkNodes = role == WNodeTableColumnRole.Edge ? new HashSet<string>(StringComparer.OrdinalIgnoreCase) { reader["Name3"].ToString().ToLower(CultureInfo.CurrentCulture) } : new HashSet<string>(StringComparer.OrdinalIgnoreCase), HasReversedEdge = hasRevEdge, IsReversedEdge = isRevEdge, EdgeUdfPrefix = udfPrefix, }; if (role == WNodeTableColumnRole.Edge) { var tableSchema = reader["Schema"].ToString(); var tableName = reader["Name1"].ToString(); var edgeName = reader["Name2"].ToString(); var refTableName = reader["Name3"].ToString(); var revEdgeName = (tableName + "_" + edgeName + "Reversed").ToLower(); var tuple = new Tuple<string, string>(tableSchema.ToLower(), refTableName.ToLower()); if (!revEdgeOrderDict.ContainsKey(tuple)) revEdgeOrderDict[tuple] = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase); revEdgeOrderDict[tuple][revEdgeName] = revEdgeOrder; ++revEdgeOrder; } } columnDict.Add(name2, new NodeColumns { EdgeInfo = edgeInfo, Role = role, }); } // Retrieve edge attributes else if (tag == -1) { var columnDict = columnsOfNodeTables[tableTuple]; columnDict[name2].EdgeInfo.ColumnAttributes.Add(reader["Name3"].ToString() .ToLower(CultureInfo.CurrentCulture)); } // Retrieve node view mapping else if (tag == -2) { HashSet<string> nodeTableSet; if (!nodeViewMapping.TryGetValue(tableTuple, out nodeTableSet)) { nodeTableSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase); nodeViewMapping.Add(tableTuple, nodeTableSet); } nodeTableSet.Add(name2); } // Retrieve edge view mapping else if (tag == -3) { string edgeViewSourceTableName = reader["EdgeViewTable"].ToString().ToLower(CultureInfo.CurrentCulture); string sourceTableName = reader["Name3"].ToString().ToLower(CultureInfo.CurrentCulture); string sinkTableName = columnsOfNodeTables[new Tuple<string, string>(schema, sourceTableName)][name2] .EdgeInfo.SinkNodes.First(); var edgeViewInfo = columnsOfNodeTables[new Tuple<string, string>(schema, edgeViewSourceTableName)][ name1].EdgeInfo; if (!edgeViewInfo.SinkNodes.Contains(sourceTableName)) edgeViewInfo.SinkNodes.Add(sinkTableName); if (edgeViewInfo.EdgeColumns == null) edgeViewInfo.EdgeColumns = new List<Tuple<string, string>>(); edgeViewInfo.EdgeColumns.Add(new Tuple<string, string>(sourceTableName, name2)); } } // sort reversed edgeViews' EdgeColumn according to the order of their corresponding original edges' column Ids // to match the rule of edgeView's decoder function parameters placing order foreach (var it in columnsOfNodeTables) { foreach (var edge in it.Value) { if (edge.Value.Role == WNodeTableColumnRole.EdgeView && edge.Value.EdgeInfo.IsReversedEdge) { var edgeInfo = edge.Value.EdgeInfo; edgeInfo.EdgeColumns = edgeInfo.EdgeColumns.OrderBy( x => revEdgeOrderDict[new Tuple<string, string>("dbo", x.Item1)][x.Item2]) .ToList(); } } } } } }
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> /// Span the table given the edge using cross apply /// </summary> /// <param name="tableRef"></param> /// <param name="edge"></param> /// <param name="nodeAlias"></param> /// <param name="dumbNode"></param> /// <returns></returns> public WTableReference SpanTableRef(WTableReference tableRef, MatchEdge edge, string nodeAlias, string dumbNode, GraphMetaData metaData) { tableRef = new WUnqualifiedJoin { FirstTableRef = tableRef, SecondTableRef = edge.ToSchemaObjectFunction(nodeAlias, dumbNode, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }; return tableRef; }
//private double _edgeDegrees = -1; //public double EdgeDegrees //{ // get // { // if (_edgeDegrees < 0) // { // double matEdgeDegrees = MaterializedEdges.Aggregate(1.0, (cur, next) => cur*next.AverageDegree); // double unMatEdgeDegrees = UnmaterializedEdges.Aggregate(1.0, (cur, next) => cur * next.AverageDegree); // _edgeDegrees = unMatEdgeDegrees*matEdgeDegrees; // } // return _edgeDegrees; // } //} //private double _sqlEstimatedEdgeDegrees = -1; //public double SqlEstimatedEdgeDegrees //{ // get // { // if (_sqlEstimatedEdgeDegrees < 0) // { // _sqlEstimatedEdgeDegrees = Math.Pow(1000, MaterializedEdges.Count + UnmaterializedEdges.Count); // } // return _sqlEstimatedEdgeDegrees; // } //} //public WTableReference ToTableReference(string nodeAlias, GraphMetaData metaData) //{ // // Constructs table reference // WTableReference nodeTable = new WNamedTableReference // { // Alias = new Identifier { Value = nodeAlias }, // TableObjectName = TreeRoot.NodeTableObjectName // }; // nodeTable = MaterializedEdges.Aggregate(nodeTable, (current, edge) => new WUnqualifiedJoin // { // FirstTableRef = current, // SecondTableRef = edge.ToSchemaObjectFunction(nodeAlias, metaData), // UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, // }); // return nodeTable; //} public WTableReference ToTableReference(string nodeAlias, string dumbNode, GraphMetaData metaData) { // Constructs table reference WTableReference nodeTable = new WNamedTableReference { Alias = new Identifier { Value = nodeAlias }, TableObjectName = TreeRoot.NodeTableObjectName }; if (PreMatOutgoingEdges != null && PreMatOutgoingEdges.Any()) { nodeTable = PreMatOutgoingEdges.Aggregate(nodeTable, (current, edge) => new WUnqualifiedJoin { FirstTableRef = current, SecondTableRef = edge.ToSchemaObjectFunction(nodeAlias, dumbNode, metaData), UnqualifiedJoinType = UnqualifiedJoinType.CrossApply, }); } return nodeTable; }
//public virtual WSchemaObjectFunctionTableReference ToSchemaObjectFunction(string nodeAlias, GraphMetaData metaData) //{ // var edgeIdentifiers = EdgeColumn.MultiPartIdentifier.Identifiers; // var edgeColIdentifier = edgeIdentifiers.Last(); // var edgeColName = edgeColIdentifier.Value; // HashSet<string> nodeSet; // if (!metaData.NodeViewMapping.TryGetValue( // WNamedTableReference.SchemaNameToTuple(SourceNode.NodeTableObjectName), out nodeSet)) // nodeSet = null; // NodeColumns columnInfo = metaData.ColumnsOfNodeTables[WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName)][ // edgeColIdentifier.Value]; // EdgeInfo edgeInfo = columnInfo.EdgeInfo; // List<Tuple<string, string>> edgeTuples = edgeInfo.EdgeColumns; // var parameters = ConstructEdgeTvfParameters(nodeAlias, "", nodeSet, edgeTuples); // //var isRevEdge = columnInfo.IsReversedEdge; // //string refTableName = null, originalEdgeName = null; // //if (isRevEdge) // //{ // // var index = edgeColName.IndexOf('_'); // // refTableName = edgeColName.Substring(0, index); // // originalEdgeName = edgeColName.Substring(index + 1, // // edgeColName.Length - "Reversed".Length - index - 1); // //} // //var decoderSchemaName = isRevEdge ? columnInfo.RefTableSchema : BindNodeTableObjName.SchemaIdentifier.Value; // //var decoderTableName = isRevEdge ? refTableName : BindNodeTableObjName.BaseIdentifier.Value; // //var decoderEdgeName = isRevEdge ? originalEdgeName : EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value; // //var decoderStr = decoderSchemaName + "_" + decoderTableName + "_" + decoderEdgeName + "_Decoder"; // var decoderFunction = new Identifier // { // Value = edgeInfo.EdgeUdfPrefix + "_Decoder", // }; // return new WSchemaObjectFunctionTableReference // { // SchemaObject = new WSchemaObjectName( // new Identifier { Value = "dbo" }, // decoderFunction), // Parameters = parameters, // Alias = new Identifier // { // Value = EdgeAlias, // } // }; //} /// <summary> /// Converts the edge to the table-valued function /// </summary> /// <param name="nodeAlias">Source node alias</param> /// <param name="dumbNode">Dumb node parameter alias</param> /// <param name="metaData">Meta data</param> /// <returns>A syntax tree node representing the table-valued function</returns> public virtual WSchemaObjectFunctionTableReference ToSchemaObjectFunction(string nodeAlias, string dumbNode, GraphMetaData metaData) { var edgeIdentifiers = EdgeColumn.MultiPartIdentifier.Identifiers; var edgeColIdentifier = edgeIdentifiers.Last(); var edgeColName = edgeColIdentifier.Value; var bindNodeTableTuple = WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName); HashSet<string> nodeSet; if (!metaData.NodeViewMapping.TryGetValue( WNamedTableReference.SchemaNameToTuple(SourceNode.NodeTableObjectName), out nodeSet)) nodeSet = null; if (IsEdgeView && IsReversedEdge) { var sinkNodeTableTuple = WNamedTableReference.SchemaNameToTuple(SinkNode.NodeTableObjectName); var index = edgeColName.IndexOf(sinkNodeTableTuple.Item2, StringComparison.OrdinalIgnoreCase); index += sinkNodeTableTuple.Item2.Length; var originalEdgeViewName = edgeColName.Substring(index + 1, edgeColName.Length - "Reversed".Length - index - 1); edgeColName = bindNodeTableTuple.Item2 + "_" + originalEdgeViewName + "Reversed"; } NodeColumns columnInfo = metaData.ColumnsOfNodeTables[WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName)][ edgeColName]; EdgeInfo edgeInfo = columnInfo.EdgeInfo; List<Tuple<string, string>> edgeTuples = edgeInfo.EdgeColumns; var parameters = ConstructEdgeTvfParameters(nodeAlias, dumbNode, nodeSet, edgeTuples); var decoderFunction = new Identifier { Value = edgeInfo.EdgeUdfPrefix + "_Decoder", }; return new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, decoderFunction), Parameters = parameters, Alias = new Identifier { Value = EdgeAlias, } }; }
/// <summary> /// Calculate join costs and update components using optimal join method & order /// </summary> /// <param name="nodeUnitCandidate"></param> /// <param name="joinCondition"></param> /// <param name="joinSelectivity"></param> /// <param name="estimatedSelectivity"></param> /// <returns></returns> private void ConstructPhysicalJoinAndUpdateCost( CandidateJoinUnit nodeUnitCandidate, WBooleanExpression joinCondition, double joinSelectivity, double estimatedSelectivity, GraphMetaData metaData) { var nodeDegrees = nodeUnitCandidate.EdgeDegrees; var nodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeDegrees; var estimatedNodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeUnitCandidate.SqlEstimatedEdgeDegrees; var componentSize = Cardinality; var estimatedCompSize = SqlEstimatedSize; var node = nodeUnitCandidate.TreeRoot; // If the node is already in the component, then only multiply the degree to get the size double nodeUnitActualSize; double newCompEstSize; if (MaterializedNodeSplitCount[node] > 0) { nodeUnitActualSize = nodeDegrees; var cEstEdge = Math.Pow(1000, EdgeMaterilizedDict.Count(e => !e.Value)); var cSize = SqlEstimatedSize / cEstEdge; var nSize = node.EstimatedRows; if (nSize > cSize) { newCompEstSize = estimatedNodeUnitSize * cEstEdge * estimatedSelectivity; } else { newCompEstSize = SqlEstimatedSize * Math.Pow(1000, nodeUnitCandidate.UnmaterializedEdges.Count) * estimatedSelectivity; } } else { nodeUnitActualSize = nodeUnitSize; newCompEstSize = SqlEstimatedSize * estimatedNodeUnitSize * estimatedSelectivity; } newCompEstSize = newCompEstSize < 1.0 ? 1.0 : newCompEstSize; bool firstJoin = MaterializedNodeSplitCount.Count == 2 && MaterializedNodeSplitCount.All(e => e.Value == 0); // Update TableRef double loopJoinOuterThreshold = 1e4; //1e6; double sizeFactor = 5; //1000; double maxMemory = 1e8; double loopCost = componentSize * Math.Log(nodeUnitCandidate.TreeRoot.EstimatedRows, 512) * 0.20; double hashCost = componentSize + nodeUnitSize; double cost; // Loop Join if ( nodeUnitCandidate.MaterializedEdges.Count == 0 && // the joins are purely leaf to sink join ( //componentSize < loopJoinOuterThreshold || // the outer table is relatively small loopCost < hashCost || (DeltaMemory + componentSize > maxMemory && DeltaMemory + nodeUnitSize > maxMemory) // memory is in pressure ) ) { if (firstJoin) { RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; RightestTableAlias = GetNodeRefName(node); } TotalMemory = DeltaMemory; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory; //joinTable.JoinHint = JoinHint.Loop; SqlEstimatedSize = estimatedCompSize * estimatedNodeUnitSize / nodeUnitCandidate.TreeRoot.TableRowCount; cost = loopCost; //componentSize*Math.Log(nodeUnitCandidate.TreeRoot.EstimatedRows, 512); TableRef = new WParenthesisTableReference { Table = new WQualifiedJoin { FirstTableRef = TableRef, SecondTableRef = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData), JoinCondition = joinCondition, QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Loop } }; } // Hash Join else { cost = hashCost;//componentSize + nodeUnitSize; WBooleanExpression adjustedJoincondition; double adjustedSqlEstimatedSize; WTableReference buildTableReference; WTableReference probeTableReference; if (firstJoin) { var nodeInComp = MaterializedNodeSplitCount.Keys.First(e => e != node); if (nodeUnitSize < componentSize) { buildTableReference = AdjustEstimation(nodeUnitCandidate, GetNodeRefName(node), metaData, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = TableRef; TotalMemory = DeltaMemory = nodeUnitSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory = estimatedNodeUnitSize; RightestTableRefSize = nodeInComp.EstimatedRows; RightestTableAlias = GetNodeRefName(nodeInComp); } else { RightestTableRefSize = nodeInComp.EstimatedRows; RightestTableAlias = GetNodeRefName(nodeInComp); buildTableReference = AdjustEstimation(this, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData); TotalMemory = DeltaMemory = componentSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory = SqlEstimatedSize; RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; RightestTableAlias = GetNodeRefName(node); } } // Left Deep else if (componentSize * sizeFactor < nodeUnitSize) { // Adjust estimation in sql server buildTableReference = AdjustEstimation(this, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = nodeUnitCandidate.ToTableReference(GetNodeRefName(nodeUnitCandidate.TreeRoot), metaData); var curDeltaMemory = componentSize; TotalMemory = DeltaMemory + curDeltaMemory; DeltaMemory = curDeltaMemory; var curDeltaEstimateMemory = SqlEstimatedSize; SqlEstimatedTotalMemory = SqlEstimatedDeltaMemory + curDeltaEstimateMemory; SqlEstimatedDeltaMemory = curDeltaEstimateMemory; RightestTableAlias = GetNodeRefName(node); RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows; } // Right Deep else { buildTableReference = AdjustEstimation(nodeUnitCandidate, GetNodeRefName(node), metaData, out adjustedJoincondition, out adjustedSqlEstimatedSize); probeTableReference = TableRef; TotalMemory += nodeUnitSize; DeltaMemory = TotalMemory; SqlEstimatedTotalMemory += estimatedNodeUnitSize; SqlEstimatedDeltaMemory = SqlEstimatedTotalMemory; } newCompEstSize *= adjustedSqlEstimatedSize; TableRef = new WParenthesisTableReference { Table = new WQualifiedJoin { FirstTableRef = buildTableReference, SecondTableRef = probeTableReference, JoinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, adjustedJoincondition), QualifiedJoinType = QualifiedJoinType.Inner, JoinHint = JoinHint.Hash } }; SqlEstimatedSize = newCompEstSize < 1.0 ? 1.0 : newCompEstSize; } //Update Size Cardinality *= nodeUnitActualSize * joinSelectivity; // Debug #if DEBUG //foreach (var item in MaterializedNodeSplitCount.Where(e => e.Key != node)) //{ // Trace.Write(item.Key.RefAlias + ","); //} //Trace.Write(node.RefAlias); //Trace.Write(" Size:" + Cardinality + " Cost:" + cost); //Trace.Write(" Method:" + ((TableRef as WParenthesisTableReference).Table as WQualifiedJoin).JoinHint); //Trace.WriteLine(" --> Total Cost:" + Cost); #endif // Update Cost Cost += cost; }
/// <summary> /// Calculate the number used for adjusting the SQL Server estimation in the downsize function. /// </summary> /// <param name="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> /// Converts the edge to the table-valued function /// </summary> /// <param name="nodeAlias">Source node alias</param> /// <param name="metaData">Meta data</param> /// <returns>A syntax tree node representing the table-valued function</returns> public virtual WSchemaObjectFunctionTableReference ToSchemaObjectFunction(string nodeAlias, GraphMetaData metaData) { var edgeIdentifiers = EdgeColumn.MultiPartIdentifier.Identifiers; var edgeColIdentifier = edgeIdentifiers.Last(); HashSet <string> nodeSet; if (!metaData.NodeViewMapping.TryGetValue( WNamedTableReference.SchemaNameToTuple(SourceNode.NodeTableObjectName), out nodeSet)) { nodeSet = null; } EdgeInfo edgeInfo = metaData.ColumnsOfNodeTables[WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName)][ edgeColIdentifier.Value].EdgeInfo; List <Tuple <string, string> > edgeTuples = edgeInfo.EdgeColumns; var parameters = ConstructEdgeTvfParameters(nodeAlias, nodeSet, edgeTuples); var decoderFunction = new Identifier { Value = BindNodeTableObjName.SchemaIdentifier.Value + '_' + BindNodeTableObjName.BaseIdentifier.Value + '_' + EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + '_' + "Decoder" }; return(new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, decoderFunction), Parameters = parameters, Alias = new Identifier { Value = EdgeAlias, } }); }
/// <summary> /// Bind edge/edge view to node/node view /// </summary> /// <param name="schema"></param> /// <param name="edgeColumn"></param> /// <param name="nodeTable"></param> /// <param name="graphMetaData"></param> /// <returns>Return null when there not exists the binding node/node view, /// otherwise return name of the node/node view bound to the edge/edge view</returns> public string BindEdgeToNode(string schema, string edgeColumn, string nodeTable, GraphMetaData graphMetaData) { var edgeNodeTuple = new Tuple <string, string>(nodeTable, edgeColumn); string resNode; if (_edgeNodeBinding.TryGetValue(edgeNodeTuple, out resNode)) { return(resNode); } var nodeTuple = new Tuple <string, string>(schema, nodeTable); if (graphMetaData.ColumnsOfNodeTables[nodeTuple].ContainsKey(edgeColumn)) { _edgeNodeBinding[edgeNodeTuple] = nodeTable.ToLower(); return(nodeTable); } else if (graphMetaData.NodeViewMapping.ContainsKey(nodeTuple)) { foreach (var node in graphMetaData.NodeViewMapping[nodeTuple]) { if (graphMetaData.ColumnsOfNodeTables[new Tuple <string, string>(schema, node)].ContainsKey(edgeColumn)) { if (string.IsNullOrEmpty(resNode)) { resNode = node; } else { return(null); } } } _edgeNodeBinding[edgeNodeTuple] = resNode; return(resNode); } else { return(null); } }
/// <summary> /// Converts the edge to the table-valued function /// </summary> /// <param name="nodeAlias">Source node alias</param> /// <param name="dumbNode"></param> /// <param name="metaData">Meta data</param> /// <returns>A syntax tree node representing the table-valued function</returns> public override WSchemaObjectFunctionTableReference ToSchemaObjectFunction(string nodeAlias, string dumbNode, GraphMetaData metaData) { var edgeIdentifiers = EdgeColumn.MultiPartIdentifier.Identifiers; var edgeColIdentifier = edgeIdentifiers.Last(); HashSet<string> nodeSet; if (!metaData.NodeViewMapping.TryGetValue( WNamedTableReference.SchemaNameToTuple(SourceNode.NodeTableObjectName), out nodeSet)) nodeSet = null; var sourceNodeColumns = metaData.ColumnsOfNodeTables[WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName)]; var edgeInfo = sourceNodeColumns[edgeColIdentifier.Value].EdgeInfo; List<Tuple<string, string>> edgeTuples = edgeInfo.EdgeColumns; var parameters = ConstructEdgeTvfParameters(nodeAlias, null, nodeSet, edgeTuples); Identifier decoderFunction; if (ReferencePathInfo) { decoderFunction = new Identifier { Value = BindNodeTableObjName.SchemaIdentifier.Value + '_' + BindNodeTableObjName.BaseIdentifier.Value + '_' + EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + '_' + "bfsPathWithMessage" }; // Node view if (nodeSet!=null) { parameters.Insert(0,new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier() { Value = SourceNode.RefAlias }, new Identifier() { Value = "_NodeId" }) }); parameters.Insert(0,new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier() { Value = SourceNode.RefAlias }, new Identifier() { Value = "_NodeType" }) }); } else { string nodeIdName = sourceNodeColumns.FirstOrDefault(e => e.Value.Role == WNodeTableColumnRole.NodeId).Key; if (string.IsNullOrEmpty(nodeIdName)) parameters.Insert(0, new WValueExpression { Value = "null" }); else { parameters.Insert(0, new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier() { Value = SourceNode.RefAlias }, new Identifier() { Value = nodeIdName }) }); } parameters.Insert(0, new WValueExpression { Value = BindNodeTableObjName.BaseIdentifier.Value, SingleQuoted = true }); } } else { decoderFunction = new Identifier { Value = BindNodeTableObjName.SchemaIdentifier.Value + '_' + BindNodeTableObjName.BaseIdentifier.Value + '_' + EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + '_' + "bfsPath" }; } parameters.Insert(0, new WValueExpression { Value = MaxLength.ToString() }); parameters.Insert(0, new WValueExpression { Value = MinLength.ToString() }); parameters.Insert(0, new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new[] { new Identifier { Value = nodeAlias }, new Identifier { Value = "GlobalNodeId" }, }) }); var attributes = edgeInfo.ColumnAttributes; if (AttributeValueDict == null) { WValueExpression nullExpression = new WValueExpression { Value = "null" }; for (int i = 0; i < attributes.Count; i++) parameters.Add(nullExpression); } else { foreach (var attribute in attributes) { string value; var valueExpression = new WValueExpression { Value = AttributeValueDict.TryGetValue(attribute, out value) ? value : "null" }; parameters.Add(valueExpression); } } return new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, decoderFunction), Parameters = parameters, Alias = new Identifier { Value = EdgeAlias, } }; }
private WBooleanExpression ConstructJoinCondition( CandidateJoinUnit candidateTree, IMatchJoinStatisticsCalculator statisticsCalculator, GraphMetaData metaData, out double joinSelectivity, out double sqlEstimatedJoinSelectivity) { joinSelectivity = 1.0; sqlEstimatedJoinSelectivity = 1.0; var root = candidateTree.TreeRoot; WBooleanExpression joinCondition = null; string nodeName = ""; // Update Nodes if (MaterializedNodeSplitCount.ContainsKey(root)) { MaterializedNodeSplitCount[root]++; nodeName = GetNodeRefName(root); joinCondition = new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = root.RefAlias }, new Identifier { Value = "GlobalNodeId" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ), }, ComparisonType = BooleanComparisonType.Equals }; } else { nodeName = root.RefAlias; if (!Nodes.Contains(root)) { Nodes.Add(root); } MaterializedNodeSplitCount[root] = 0; } List <double> densityList = new List <double>(); List <MatchEdge> inEdges; if (UnmaterializedNodeMapping.TryGetValue(root, out inEdges)) { var firstEdge = inEdges.First(); bool materialized = EdgeMaterilizedDict[firstEdge]; UnmaterializedNodeMapping.Remove(root); joinSelectivity *= 1.0 / root.TableRowCount; // Component materialized edge to root if (materialized) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(root.GlobalNodeIdDensity); } // Component unmaterialized edge to root else { Statistics statistics = null; foreach (var edge in inEdges) { // Update component table TableRef = SpanTableRef(TableRef, edge, GetNodeRefName(edge.SourceNode), metaData); EdgeMaterilizedDict[edge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = edge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = nodeName }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); double selectivity; statistics = Statistics.UpdateHistogram(statistics, edge.Statistics, out selectivity); joinSelectivity *= selectivity; densityList.Add(root.GlobalNodeIdDensity); } SinkNodeStatisticsDict[root] = statistics; } } var jointEdges = candidateTree.MaterializedEdges; foreach (var jointEdge in jointEdges) { EdgeMaterilizedDict[jointEdge] = true; var sinkNode = jointEdge.SinkNode; // Leaf to component materialized node if (MaterializedNodeSplitCount.ContainsKey(sinkNode)) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = sinkNode.RefAlias }, new Identifier { Value = "GlobalNodeId" } ) }, ComparisonType = BooleanComparisonType.Equals }); Statistics sinkNodeStatistics; if (!SinkNodeStatisticsDict.TryGetValue(sinkNode, out sinkNodeStatistics)) { sinkNodeStatistics = null; joinSelectivity *= 1.0 / sinkNode.TableRowCount; } double selectivity; var statistics = Statistics.UpdateHistogram(sinkNodeStatistics, jointEdge.Statistics, out selectivity); joinSelectivity *= selectivity; SinkNodeStatisticsDict[sinkNode] = statistics; densityList.Add(sinkNode.GlobalNodeIdDensity); } // Leaf to component unmaterialized node else { inEdges = UnmaterializedNodeMapping[sinkNode]; var firstEdge = inEdges.First(); bool materlizedEdge = EdgeMaterilizedDict[firstEdge]; // Leaf to materialized leaf if (materlizedEdge) { joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = firstEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(Statistics.DefaultDensity); double selectivity; var statistics = Statistics.UpdateHistogram(SinkNodeStatisticsDict[sinkNode], jointEdge.Statistics, out selectivity); joinSelectivity *= selectivity; SinkNodeStatisticsDict[sinkNode] = statistics; } // Leaf to unmaterialized leaf else { Statistics compSinkNodeStatistics = null; foreach (var inEdge in inEdges) { TableRef = SpanTableRef(TableRef, inEdge, GetNodeRefName(inEdge.SourceNode), metaData); EdgeMaterilizedDict[inEdge] = true; joinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, new WBooleanComparisonExpression { FirstExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = jointEdge.EdgeAlias }, new Identifier { Value = "Sink" } ), }, SecondExpr = new WColumnReferenceExpression { ColumnType = ColumnType.Regular, MultiPartIdentifier = new WMultiPartIdentifier( new Identifier { Value = inEdge.EdgeAlias }, new Identifier { Value = "Sink" } ) }, ComparisonType = BooleanComparisonType.Equals }); densityList.Add(Statistics.DefaultDensity); double selectivity; var leafToLeafStatistics = statisticsCalculator.GetLeafToLeafStatistics(jointEdge, inEdge, out selectivity); joinSelectivity *= selectivity; compSinkNodeStatistics = Statistics.UpdateHistogram(compSinkNodeStatistics, inEdge.Statistics, out selectivity); } SinkNodeStatisticsDict[sinkNode] = compSinkNodeStatistics; } } } var unmatEdges = candidateTree.UnmaterializedEdges; foreach (var unmatEdge in unmatEdges) { EdgeMaterilizedDict[unmatEdge] = false; if (!Nodes.Contains(unmatEdge.SinkNode)) { Nodes.Add(unmatEdge.SinkNode); } var sinkNodeInEdges = UnmaterializedNodeMapping.GetOrCreate(unmatEdge.SinkNode); sinkNodeInEdges.Add(unmatEdge); } // Calculate Estimated Join Selectivity & Estimated Node Size densityList.Sort(); for (int i = densityList.Count - 1; i >= 0; i--) { sqlEstimatedJoinSelectivity *= Math.Sqrt(sqlEstimatedJoinSelectivity) * densityList[i]; } return(joinCondition); }
/// <summary> /// Transit from current component to the new component in the next state given the Node Unit /// </summary> /// <param name="candidateTree"></param> /// <param name="statisticsCalculator"></param> /// <param name="metaData"></param> /// <param name="srcNodeStatisticsDict"></param> /// <returns></returns> public MatchComponent GetNextState( CandidateJoinUnit candidateTree, IMatchJoinStatisticsCalculator statisticsCalculator, GraphMetaData metaData, Dictionary<Tuple<string, bool>, Statistics> srcNodeStatisticsDict) { // Deep copy the component var newComponent = new MatchComponent(this); // Constrcuts join conditions and retrieves join selectivity double preJoinSelectivity, postJoinSelectivity, sqlEstimatedJoinSelectivity; var joinCondition = newComponent.ConstructJoinCondition(candidateTree, statisticsCalculator, metaData, srcNodeStatisticsDict, out preJoinSelectivity, out postJoinSelectivity, out sqlEstimatedJoinSelectivity); // Constructs physical join method and join table references newComponent.ConstructPhysicalJoinAndUpdateCost(candidateTree, joinCondition, preJoinSelectivity, postJoinSelectivity, sqlEstimatedJoinSelectivity, metaData); return newComponent; }
/// <summary> /// Converts the edge to the table-valued function /// </summary> /// <param name="nodeAlias">Source node alias</param> /// <param name="metaData">Meta data</param> /// <returns>A syntax tree node representing the table-valued function</returns> public virtual WSchemaObjectFunctionTableReference ToSchemaObjectFunction(string nodeAlias, GraphMetaData metaData) { var edgeIdentifiers = EdgeColumn.MultiPartIdentifier.Identifiers; var edgeColIdentifier = edgeIdentifiers.Last(); HashSet<string> nodeSet; if (!metaData.NodeViewMapping.TryGetValue( WNamedTableReference.SchemaNameToTuple(SourceNode.NodeTableObjectName), out nodeSet)) nodeSet = null; EdgeInfo edgeInfo = metaData.ColumnsOfNodeTables[WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName)][ edgeColIdentifier.Value].EdgeInfo; List<Tuple<string, string>> edgeTuples = edgeInfo.EdgeColumns; var parameters = ConstructEdgeTvfParameters(nodeAlias, nodeSet, edgeTuples); var decoderFunction = new Identifier { Value = BindNodeTableObjName.SchemaIdentifier.Value + '_' + BindNodeTableObjName.BaseIdentifier.Value + '_' + EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + '_' + "Decoder" }; return new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, decoderFunction), Parameters = parameters, Alias = new Identifier { Value = EdgeAlias, } }; }
/// <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; }
public MatchComponent(MatchNode node, List<MatchEdge> populatedEdges,GraphMetaData metaData) : this(node) { foreach (var edge in populatedEdges) { TableRef = SpanTableRef(TableRef, edge, node.RefAlias, metaData); EdgeMaterilizedDict[edge] = true; SinkNodeStatisticsDict[edge.SinkNode] = edge.Statistics; var edgeList = UnmaterializedNodeMapping.GetOrCreate(edge.SinkNode); edgeList.Add(edge); if (!Nodes.Contains(edge.SinkNode)) Nodes.Add(edge.SinkNode); Cardinality *= edge.AverageDegree; SqlEstimatedSize *= 1000; } }
/// <summary> /// 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 the number used for adjusting the SQL Server estimation in the downsize function. /// </summary> /// <param name="metaData"></param> /// <param name="joinCondition"></param> /// <param name="candidateJoinUnit"></param> /// <param name="nodeAlias"></param> /// <param name="affectedSqlEstimatedSize"></param> private static WTableReference AdjustEstimation(CandidateJoinUnit candidateJoinUnit, string nodeAlias, GraphMetaData metaData, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize) { const int sizeFactor = 10; int estimateFactor = 0; double size = candidateJoinUnit.EdgeDegrees; double estimatedSize = candidateJoinUnit.SqlEstimatedEdgeDegrees; double shrinkSize = candidateJoinUnit.TreeRoot.EstimatedRows; WTableReference tableReference = candidateJoinUnit.ToTableReference(nodeAlias, metaData); affectedSqlEstimatedSize = 1.0; joinCondition = null; if (size > sizeFactor * estimatedSize) { estimateFactor = (int)Math.Ceiling(size / estimatedSize); } else if (sizeFactor*size < estimatedSize) { shrinkSize = 1.0/(1 - Math.Pow((1 - 1.0/shrinkSize), 1.5)); affectedSqlEstimatedSize /= shrinkSize; estimatedSize /= shrinkSize; estimateFactor = (int) Math.Ceiling(size/estimatedSize); joinCondition = ConstructDownSizeJoinCondition(nodeAlias); } if (estimateFactor > 1) { double affectedUpSize; tableReference = ConstructUpSizeTableReference(tableReference, estimateFactor, out affectedUpSize); affectedSqlEstimatedSize *= affectedUpSize; } return tableReference; }
//public virtual WSchemaObjectFunctionTableReference ToSchemaObjectFunction(string nodeAlias, GraphMetaData metaData) //{ // var edgeIdentifiers = EdgeColumn.MultiPartIdentifier.Identifiers; // var edgeColIdentifier = edgeIdentifiers.Last(); // var edgeColName = edgeColIdentifier.Value; // HashSet<string> nodeSet; // if (!metaData.NodeViewMapping.TryGetValue( // WNamedTableReference.SchemaNameToTuple(SourceNode.NodeTableObjectName), out nodeSet)) // nodeSet = null; // NodeColumns columnInfo = metaData.ColumnsOfNodeTables[WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName)][ // edgeColIdentifier.Value]; // EdgeInfo edgeInfo = columnInfo.EdgeInfo; // List<Tuple<string, string>> edgeTuples = edgeInfo.EdgeColumns; // var parameters = ConstructEdgeTvfParameters(nodeAlias, "", nodeSet, edgeTuples); // //var isRevEdge = columnInfo.IsReversedEdge; // //string refTableName = null, originalEdgeName = null; // //if (isRevEdge) // //{ // // var index = edgeColName.IndexOf('_'); // // refTableName = edgeColName.Substring(0, index); // // originalEdgeName = edgeColName.Substring(index + 1, // // edgeColName.Length - "Reversed".Length - index - 1); // //} // //var decoderSchemaName = isRevEdge ? columnInfo.RefTableSchema : BindNodeTableObjName.SchemaIdentifier.Value; // //var decoderTableName = isRevEdge ? refTableName : BindNodeTableObjName.BaseIdentifier.Value; // //var decoderEdgeName = isRevEdge ? originalEdgeName : EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value; // //var decoderStr = decoderSchemaName + "_" + decoderTableName + "_" + decoderEdgeName + "_Decoder"; // var decoderFunction = new Identifier // { // Value = edgeInfo.EdgeUdfPrefix + "_Decoder", // }; // return new WSchemaObjectFunctionTableReference // { // SchemaObject = new WSchemaObjectName( // new Identifier { Value = "dbo" }, // decoderFunction), // Parameters = parameters, // Alias = new Identifier // { // Value = EdgeAlias, // } // }; //} /// <summary> /// Converts the edge to the table-valued function /// </summary> /// <param name="nodeAlias">Source node alias</param> /// <param name="dumbNode">Dumb node parameter alias</param> /// <param name="metaData">Meta data</param> /// <returns>A syntax tree node representing the table-valued function</returns> public virtual WSchemaObjectFunctionTableReference ToSchemaObjectFunction(string nodeAlias, string dumbNode, GraphMetaData metaData) { var edgeIdentifiers = EdgeColumn.MultiPartIdentifier.Identifiers; var edgeColIdentifier = edgeIdentifiers.Last(); var edgeColName = edgeColIdentifier.Value; var bindNodeTableTuple = WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName); HashSet <string> nodeSet; if (!metaData.NodeViewMapping.TryGetValue( WNamedTableReference.SchemaNameToTuple(SourceNode.NodeTableObjectName), out nodeSet)) { nodeSet = null; } if (IsEdgeView && IsReversedEdge) { var sinkNodeTableTuple = WNamedTableReference.SchemaNameToTuple(SinkNode.NodeTableObjectName); var index = edgeColName.IndexOf(sinkNodeTableTuple.Item2, StringComparison.OrdinalIgnoreCase); index += sinkNodeTableTuple.Item2.Length; var originalEdgeViewName = edgeColName.Substring(index + 1, edgeColName.Length - "Reversed".Length - index - 1); edgeColName = bindNodeTableTuple.Item2 + "_" + originalEdgeViewName + "Reversed"; } NodeColumns columnInfo = metaData.ColumnsOfNodeTables[WNamedTableReference.SchemaNameToTuple(BindNodeTableObjName)][ edgeColName]; EdgeInfo edgeInfo = columnInfo.EdgeInfo; List <Tuple <string, string> > edgeTuples = edgeInfo.EdgeColumns; var parameters = ConstructEdgeTvfParameters(nodeAlias, dumbNode, nodeSet, edgeTuples); var decoderFunction = new Identifier { Value = edgeInfo.EdgeUdfPrefix + "_Decoder", }; return(new WSchemaObjectFunctionTableReference { SchemaObject = new WSchemaObjectName( new Identifier { Value = "dbo" }, decoderFunction), Parameters = parameters, Alias = new Identifier { Value = EdgeAlias, } }); }
/// <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> /// Bind edge/edge view to node/node view /// </summary> /// <param name="schema"></param> /// <param name="edgeColumn"></param> /// <param name="nodeTable"></param> /// <param name="graphMetaData"></param> /// <returns>Return null when there not exists the binding node/node view, /// otherwise return name of the node/node view bound to the edge/edge view</returns> public string BindEdgeToNode(string schema, string edgeColumn, string nodeTable, GraphMetaData graphMetaData) { var edgeNodeTuple = new Tuple<string, string>(nodeTable, edgeColumn); string resNode; if (_edgeNodeBinding.TryGetValue(edgeNodeTuple, out resNode)) return resNode; var nodeTuple = new Tuple<string, string>(schema, nodeTable); if (graphMetaData.ColumnsOfNodeTables[nodeTuple].ContainsKey(edgeColumn)) { _edgeNodeBinding[edgeNodeTuple] = nodeTable.ToLower(); return nodeTable; } else if (graphMetaData.NodeViewMapping.ContainsKey(nodeTuple)) { foreach (var node in graphMetaData.NodeViewMapping[nodeTuple]) { if (graphMetaData.ColumnsOfNodeTables[new Tuple<string, string>(schema, node)].ContainsKey(edgeColumn)) { if (string.IsNullOrEmpty(resNode)) resNode = node; else return null; } } _edgeNodeBinding[edgeNodeTuple] = resNode; return resNode; } else { return null; } }
/// <summary> /// Retrieve the metadata /// </summary> /// <param name="conn"></param> private void Init() { _graphMetaData = new GraphMetaData(); var columnsOfNodeTables = _graphMetaData.ColumnsOfNodeTables; var nodeViewMapping = _graphMetaData.NodeViewMapping; using (var command = Tx.Connection.CreateCommand()) { command.Transaction = Tx; command.CommandText = string.Format( @" SELECT [TableSchema] as [Schema], [TableName] as [Name1], [ColumnName] as [Name2], [ColumnRole] as [Role], [Reference] as [Name3], null as [EdgeViewTable], null as [ColumnId] FROM [{0}] UNION ALL SELECT [TableSchema] as [Schema], [TableName] as [Name1], [ColumnName] as [Name2], -1 as [Role], [AttributeName] as [Name3], null, [AttributeId] FROM [{1}] UNION ALL SELECT [NV].[TableSchema] as [Schema], [NV].[TableName] as [Name1], [NT].[TableName] as [Name2], -2 as [Role], null as [Name3], null, null FROM [{2}] as [NV_NT_Mapping] JOIN [{3}] as [NV] ON NV_NT_Mapping.NodeViewTableId = NV.TableId JOIN [{3}] as [NT] ON NV_NT_Mapping.TableId = NT.TableId UNION ALL SELECT [EV].[TableSchema] as [Schema], [EV].[ColumnName] as [Name1], [ED].[ColumnName]as [Name2], -3 as [Role], [ED].[TableName] as [Name3], [EV].[TableName] as [EdgeViewTable], [ED].[ColumnId] as [ColumnId] FROM [{4}] as [EV_ED_Mapping] JOIN [{0}] as [EV] ON [EV_ED_Mapping].[NodeViewColumnId] = [EV].[ColumnId] and [EV].[ColumnRole] = 3 JOIN [{0}] as [ED] ON [EV_ED_Mapping].[ColumnId] = [ED].[ColumnId] ORDER BY [ColumnId]", GraphViewConnection.MetadataTables[1], GraphViewConnection.MetadataTables[2], GraphViewConnection.MetadataTables[7], GraphViewConnection.MetadataTables[0], GraphViewConnection.MetadataTables[5]); using (var reader = command.ExecuteReader()) { while (reader.Read()) { int tag = (int) reader["Role"]; string schema = reader["Schema"].ToString().ToLower(CultureInfo.CurrentCulture); string name1 = reader["Name1"].ToString().ToLower(CultureInfo.CurrentCulture); string name2 = reader["Name2"].ToString().ToLower(CultureInfo.CurrentCulture); // Retrieve columns of node tables var tableTuple = new Tuple<string, string>(schema, name1); if (tag >= 0) { Dictionary<string, NodeColumns> columnDict; if (!columnsOfNodeTables.TryGetValue(tableTuple, out columnDict)) { columnDict = new Dictionary<string, NodeColumns>(StringComparer.OrdinalIgnoreCase); columnsOfNodeTables.Add(tableTuple, columnDict); } var role = (WNodeTableColumnRole) tag; EdgeInfo edgeInfo = null; // Edge column if (role == WNodeTableColumnRole.Edge || role == WNodeTableColumnRole.EdgeView) { edgeInfo = new EdgeInfo { ColumnAttributes = new List<string>(), SinkNodes = role == WNodeTableColumnRole.Edge ? new HashSet<string>(StringComparer.OrdinalIgnoreCase) { reader["Name3"].ToString().ToLower(CultureInfo.CurrentCulture) } : new HashSet<string>(StringComparer.OrdinalIgnoreCase), }; } columnDict.Add(name2, new NodeColumns { EdgeInfo = edgeInfo, Role = role, }); } // Retrieve edge attributes else if (tag == -1) { var columnDict = columnsOfNodeTables[tableTuple]; columnDict[name2].EdgeInfo.ColumnAttributes.Add(reader["Name3"].ToString() .ToLower(CultureInfo.CurrentCulture)); } // Retrieve node view mapping else if (tag == -2) { HashSet<string> nodeTableSet; if (!nodeViewMapping.TryGetValue(tableTuple, out nodeTableSet)) { nodeTableSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase); nodeViewMapping.Add(tableTuple, nodeTableSet); } nodeTableSet.Add(name2); } // Retrieve edge view mapping else if (tag == -3) { string edgeViewSourceTableName = reader["EdgeViewTable"].ToString().ToLower(CultureInfo.CurrentCulture); string sourceTableName = reader["Name3"].ToString().ToLower(CultureInfo.CurrentCulture); string sinkTableName = columnsOfNodeTables[new Tuple<string, string>(schema, sourceTableName)][name2] .EdgeInfo.SinkNodes.First(); var edgeViewInfo = columnsOfNodeTables[new Tuple<string, string>(schema, edgeViewSourceTableName)][ name1].EdgeInfo; if (!edgeViewInfo.SinkNodes.Contains(sourceTableName)) edgeViewInfo.SinkNodes.Add(sinkTableName); if (edgeViewInfo.EdgeColumns == null) edgeViewInfo.EdgeColumns = new List<Tuple<string, string>>(); edgeViewInfo.EdgeColumns.Add(new Tuple<string, string>(sourceTableName, name2)); } } } } }