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));
}
}
}
}
}
