/// <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 override WTableReference ToTableReference()
{
List <WScalarExpression> parameters = new List <WScalarExpression>();
if (SourceVariableProperty != null)
{
parameters.Add(SourceVariableProperty.ToScalarExpression());
}
if (SinkVariableProperty != null)
{
parameters.Add(SinkVariableProperty.ToScalarExpression());
}
foreach (var property in ProjectedProperties)
{
parameters.Add(SqlUtil.GetValueExpr(property));
}
WTableReference tableRef = null;
if (SourceVariableProperty != null && SinkVariableProperty != null)
{
//BothV
tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.BothV, parameters, GetVariableName());
}
else
{
tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.EtoV, parameters, GetVariableName());
}
return(SqlUtil.GetCrossApplyTableReference(tableRef));
}
internal static WUnqualifiedJoin GetCrossApplyTableReference(WTableReference firstTableRef, WTableReference secondTableRef)
{
return(new WUnqualifiedJoin()
{
FirstTableRef = firstTableRef,
SecondTableRef = secondTableRef,
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply
});
}
public override WTableReference ToTableReference()
{
List <WScalarExpression> parameters = new List <WScalarExpression>();
parameters.AddRange(this.ProjectedProperties.Select(SqlUtil.GetValueExpr));
WTableReference tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.V, parameters, this.GetVariableName());
return(SqlUtil.GetCrossApplyTableReference(tableRef));
}
/// <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 static WTableReference SpanTableRef(WTableReference tableRef, MatchEdge edge, string nodeAlias)
{
tableRef = new WUnqualifiedJoin
{
FirstTableRef = tableRef,
SecondTableRef = EdgeToTableReference(edge, nodeAlias),
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply,
};
return(tableRef);
}
/// <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);
}
public MatchComponent(MatchNode node) : this()
{
Nodes.Add(node);
MaterializedNodeSplitCount[node] = 0;
//SinkNodeStatisticsDict[node] = new Statistics ();
Cardinality *= node.EstimatedRows;
SqlEstimatedSize *= node.EstimatedRows;
TableRef = new WNamedTableReference
{
Alias = new Identifier {
Value = node.RefAlias
},
TableObjectName = node.NodeTableObjectName
};
}
public override WTableReference ToTableReference()
{
List <WScalarExpression> parameters = new List <WScalarExpression>();
WTableReference tableRef = null;
if (this.PropertyKeys.Count == 0)
{
parameters.Add(this.ProjectVariable.DefaultProjection().ToScalarExpression());
tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.AllValues, parameters, GetVariableName());
}
else
{
parameters.AddRange(this.PropertyKeys.Select(property => this.ProjectVariable.GetVariableProperty(property).ToScalarExpression()));
tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.Values, parameters, GetVariableName());
}
return(SqlUtil.GetCrossApplyTableReference(tableRef));
}
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 override WTableReference ToTableReference()
{
List <WScalarExpression> PropertyKeys = new List <WScalarExpression>();
if (SourceVariableProperty != null)
{
PropertyKeys.Add(SourceVariableProperty.ToScalarExpression());
}
if (SinkVariableProperty != null)
{
PropertyKeys.Add(SinkVariableProperty.ToScalarExpression());
}
foreach (var property in ProjectedProperties)
{
PropertyKeys.Add(SqlUtil.GetValueExpr(property));
}
WTableReference secondTableRef = null;
if (SourceVariableProperty != null && SinkVariableProperty != null)
{
//BothV
secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.BothV, PropertyKeys, this, GetVariableName());
}
switch (InputEdgeType)
{
case WEdgeType.BothEdge:
secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.OtherV, PropertyKeys, this, GetVariableName());
break;
case WEdgeType.InEdge:
secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.OutV, PropertyKeys, this, GetVariableName());
break;
case WEdgeType.OutEdge:
secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.InV, PropertyKeys, this, GetVariableName());
break;
case WEdgeType.Undefined:
secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.OutV, PropertyKeys, this, GetVariableName());
break;
}
return(SqlUtil.GetCrossApplyTableReference(null, secondTableRef));
}
public override WTableReference ToTableReference()
{
List <WScalarExpression> PropertyKeys = new List <WScalarExpression>();
if (SourceVertexVariableProperty != null)
{
PropertyKeys.Add(SourceVertexVariableProperty.ToScalarExpression());
}
if (AdjEdgeVariableProperty != null)
{
PropertyKeys.Add(AdjEdgeVariableProperty.ToScalarExpression());
}
if (RevAdjEdgeVariableProperty != null)
{
PropertyKeys.Add(RevAdjEdgeVariableProperty.ToScalarExpression());
}
if (LabelVariableProperty != null)
{
PropertyKeys.Add(LabelVariableProperty.ToScalarExpression());
}
foreach (var property in ProjectedProperties)
{
PropertyKeys.Add(SqlUtil.GetValueExpr(property));
}
WTableReference secondTableRef = null;
switch (EdgeType)
{
case WEdgeType.BothEdge:
secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.BothE, PropertyKeys, this, GetVariableName());
break;
case WEdgeType.InEdge:
secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.InE, PropertyKeys, this, GetVariableName());
break;
case WEdgeType.OutEdge:
secondTableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.OutE, PropertyKeys, this, GetVariableName());
break;
}
return(SqlUtil.GetCrossApplyTableReference(null, secondTableRef));
}
public WCommonTableExpression Invoke(WSqlFragment node, string tempTableName, string sourceTableName,
out WTableReference sinkTable, out List <WScalarExpression> edgeProperties)
{
_edgeProperties = new List <WScalarExpression>();
_tempTableName = tempTableName;
_sourceTableName = sourceTableName;
node.Accept(this);
sinkTable = _sinkTable;
_edgeProperties.Insert(0,
new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {
Value = "sink"
})
}
);
edgeProperties = _edgeProperties;
return(_result);
}
public override WTableReference ToTableReference()
{
List <WScalarExpression> parameters = new List <WScalarExpression>();
WTableReference tableRef = null;
if (PropertyKeys.Count == 0)
{
parameters.Add(ProjectVariable.GetVariableProperty(GremlinKeyword.Star).ToScalarExpression());
tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.AllValues, parameters, GetVariableName());
}
else
{
foreach (var property in PropertyKeys)
{
parameters.Add(ProjectVariable.GetVariableProperty(property).ToScalarExpression());
}
tableRef = SqlUtil.GetFunctionTableReference(GremlinKeyword.func.Values, parameters, GetVariableName());
}
return(SqlUtil.GetCrossApplyTableReference(tableRef));
}
/// <summary>
/// Calculate the number used for adjusting the SQL Server estimation in the downsize function.
/// </summary>
/// <param name="component"></param>
/// <param name="joinCondition"></param>
private static WTableReference AdjustEstimation(MatchComponent component, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize)
{
const int sizeFactor = 10;
int estimateFactor = 0;
double size = component.Cardinality;
double estimatedSize = component.SqlEstimatedSize;
double shrinkSize = component.RightestTableRefSize;
WTableReference tableReference = component.TableRef;
affectedSqlEstimatedSize = 1.0;
joinCondition = null;
if (size > sizeFactor * estimatedSize)
{
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
}
else if (sizeFactor * size < estimatedSize)
{
shrinkSize = 1.0 / (1 - Math.Pow((1 - 1.0 / shrinkSize), 1.5));
if (estimatedSize < shrinkSize)
{
affectedSqlEstimatedSize /= estimatedSize;
estimatedSize = 1;
}
else
{
affectedSqlEstimatedSize /= shrinkSize;
estimatedSize /= shrinkSize;
}
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
joinCondition = ConstructDownSizeJoinCondition(component.RightestTableAlias);
}
if (estimateFactor > 1)
{
double affectedUpSize;
tableReference = ConstructUpSizeTableReference(tableReference, estimateFactor,
out affectedUpSize);
affectedSqlEstimatedSize *= affectedUpSize;
}
return(tableReference);
}
/// <summary>
/// Deep Copy
/// </summary>
/// <param name="component"></param>
public MatchComponent(MatchComponent component)
{
Nodes = new HashSet <MatchNode>(component.Nodes);
EdgeMaterilizedDict = new Dictionary <MatchEdge, bool>(component.EdgeMaterilizedDict);
MaterializedNodeSplitCount = new Dictionary <MatchNode, int>(component.MaterializedNodeSplitCount);
UnmaterializedNodeMapping = new Dictionary <MatchNode, List <MatchEdge> >();
foreach (var nodeMapping in component.UnmaterializedNodeMapping)
{
UnmaterializedNodeMapping[nodeMapping.Key] = new List <MatchEdge>(nodeMapping.Value);
}
SinkNodeStatisticsDict = new Dictionary <MatchNode, Statistics>(component.SinkNodeStatisticsDict);
TableRef = component.TableRef;
Cardinality = component.Cardinality;
Cost = component.Cost;
DeltaMemory = component.DeltaMemory;
TotalMemory = component.TotalMemory;
SqlEstimatedDeltaMemory = component.SqlEstimatedDeltaMemory;
SqlEstimatedTotalMemory = component.SqlEstimatedTotalMemory;
SqlEstimatedSize = component.SqlEstimatedSize;
RightestTableAlias = component.RightestTableAlias;
RightestTableRefSize = component.RightestTableRefSize;
}
private static WTableReference ConstructUpSizeTableReference(WTableReference tableRef, double upSizeScalar, out double affectedEstimatedSize)
{
affectedEstimatedSize = 1.0;
int pow = (int)(Math.Floor(Math.Log(upSizeScalar, 1000)) + 1);
int adjustValue = (int)Math.Pow(upSizeScalar, 1.0 / pow);
while (pow > 0)
{
tableRef = new WUnqualifiedJoin
{
FirstTableRef = tableRef,
SecondTableRef = new WSchemaObjectFunctionTableReference
{
SchemaObject = new WSchemaObjectName(
new Identifier {
Value = "dbo"
},
new Identifier {
Value = "UpSizeFunction"
}),
Parameters = new List <WScalarExpression>
{
new WValueExpression {
Value = adjustValue.ToString()
}
},
Alias = new Identifier
{
Value = Path.GetRandomFileName().Replace(".", "").Substring(0, 8),
}
},
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply
};
pow--;
affectedEstimatedSize *= adjustValue;
}
return(tableRef);
}
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>
/// Transit from current component to the new component in the next state given the Node Unit
/// </summary>
/// <param name="candidateTree"></param>
/// <param name="densityDict"></param>
/// <param name="subGraph"></param>
/// <param name="statisticsCalculator"></param>
/// <returns></returns>
public MatchComponent GetNextState(
OneHeightTree candidateTree,
Dictionary <string, double> densityDict,
IMatchJoinStatisticsCalculator statisticsCalculator)
{
var newComponent = new MatchComponent(this);
var root = candidateTree.TreeRoot;
WBooleanExpression joinCondition = null;
string nodeName = "";
// Update Nodes
if (newComponent.MaterializedNodeSplitCount.ContainsKey(root))
{
newComponent.MaterializedNodeSplitCount[root]++;
nodeName = newComponent.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;
newComponent.Nodes.Add(root);
newComponent.MaterializedNodeSplitCount[root] = 0;
newComponent.StatisticsDict[root] = new ColumnStatistics {
Selectivity = 1.0 / root.TableRowCount
};
}
// Constructs table reference
WTableReference nodeTable = new WNamedTableReference
{
Alias = new Identifier {
Value = nodeName
},
TableObjectName = root.TableObjectName
};
WTableReference compTable = newComponent.TableRef;
// Updates join conditions
double selectivity = 1.0;
double degrees = 1.0;
var DensityCount = new Dictionary <string, int>(StringComparer.CurrentCultureIgnoreCase);
List <MatchEdge> inEdges;
if (newComponent.UnmaterializedNodeMapping.TryGetValue(root, out inEdges))
{
var firstEdge = inEdges.First();
bool materialized = newComponent.EdgeMaterilizedDict[firstEdge];
newComponent.UnmaterializedNodeMapping.Remove(root);
selectivity *= 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
});
//var statistics = ColumnStatistics.UpdateHistogram(newComponent.StatisticsDict[root],
// new ColumnStatistics {Selectivity = 1.0/root.TableRowCount});
//selectivity *= statistics.Selectivity;
//newComponent.StatisticsDict[root] = statistics;
if (DensityCount.ContainsKey(root.TableObjectName.ToString()))
{
DensityCount[root.TableObjectName.ToString()]++;
}
else
{
DensityCount[root.TableObjectName.ToString()] = 1;
}
}
// Component unmaterialized edge to root
else
{
ColumnStatistics statistics = null;
foreach (var edge in inEdges)
{
// Update component table
compTable = SpanTableRef(compTable, edge, newComponent.GetNodeRefName(edge.SourceNode));
newComponent.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
});
statistics = ColumnStatistics.UpdateHistogram(statistics,
newComponent.Context.GetEdgeStatistics(edge));
selectivity *= statistics.Selectivity;
}
newComponent.StatisticsDict[root] = statistics;
if (DensityCount.ContainsKey(root.TableObjectName.ToString()))
{
DensityCount[root.TableObjectName.ToString()] += inEdges.Count;
}
else
{
DensityCount[root.TableObjectName.ToString()] = inEdges.Count;
}
}
}
var jointEdges = candidateTree.MaterializedEdges;
int sinkToSinkCount = 0;
foreach (var jointEdge in jointEdges)
{
// Update node table
nodeTable = SpanTableRef(nodeTable, jointEdge, nodeName);
degrees *= jointEdge.AverageDegree;
newComponent.EdgeMaterilizedDict[jointEdge] = true;
var sinkNode = jointEdge.SinkNode;
// Leaf to component materialized node
if (newComponent.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
});
var statistics = ColumnStatistics.UpdateHistogram(newComponent.StatisticsDict[sinkNode],
newComponent.Context.GetEdgeStatistics(jointEdge));
selectivity *= statistics.Selectivity;
newComponent.StatisticsDict[sinkNode] = statistics;
if (DensityCount.ContainsKey(sinkNode.TableObjectName.ToString()))
{
DensityCount[sinkNode.TableObjectName.ToString()]++;
}
else
{
DensityCount[sinkNode.TableObjectName.ToString()] = 1;
}
}
// Leaf to component unmaterialized node
else
{
inEdges = newComponent.UnmaterializedNodeMapping[sinkNode];
var firstEdge = inEdges.First();
bool materlizedEdge = newComponent.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
});
sinkToSinkCount++;
var statistics = ColumnStatistics.UpdateHistogram(newComponent.StatisticsDict[sinkNode],
newComponent.Context.GetEdgeStatistics(jointEdge));
selectivity *= statistics.Selectivity;
newComponent.StatisticsDict[sinkNode] = statistics;
}
// Leaf to unmaterialized leaf
else
{
ColumnStatistics compSinkNodeStatistics = null;
foreach (var inEdge in inEdges)
{
compTable = SpanTableRef(compTable, inEdge, newComponent.GetNodeRefName(inEdge.SourceNode));
newComponent.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
});
sinkToSinkCount++;
var leafToLeafStatistics = statisticsCalculator.GetLeafToLeafStatistics(jointEdge, inEdge);
selectivity *= leafToLeafStatistics.Selectivity;
compSinkNodeStatistics =
ColumnStatistics.UpdateHistogram(compSinkNodeStatistics,
newComponent.Context.GetEdgeStatistics(inEdge));
}
newComponent.StatisticsDict[sinkNode] = compSinkNodeStatistics;
}
}
}
var unmatEdges = candidateTree.UnmaterializedEdges;
foreach (var unmatEdge in unmatEdges)
{
newComponent.EdgeMaterilizedDict[unmatEdge] = false;
newComponent.Nodes.Add(unmatEdge.SinkNode);
var sinkNodeInEdges = newComponent.UnmaterializedNodeMapping.GetOrCreate(unmatEdge.SinkNode);
sinkNodeInEdges.Add(unmatEdge);
degrees *= unmatEdge.AverageDegree;
}
// Calculate Estimated Join Selectivity & Estimated Node Size
double estimatedSelectity = 1.0;
int count = 0;
bool sinkJoin = false;
foreach (var item in densityDict.Where(e => DensityCount.ContainsKey(e.Key)))
{
var density = item.Value;
var curJoinCount = DensityCount[item.Key];
var curJoinSelectitivy = Math.Pow(density, 2 - Math.Pow(2, 1 - curJoinCount));
if (!sinkJoin && ColumnStatistics.DefaultDensity < density)
{
var curSinkJoinSelectivity = Math.Pow(ColumnStatistics.DefaultDensity,
2 - Math.Pow(2, 1 - sinkToSinkCount));
estimatedSelectity *= Math.Pow(curSinkJoinSelectivity, Math.Pow(2, -count));
count += sinkToSinkCount;
sinkJoin = true;
}
estimatedSelectity *= Math.Pow(curJoinSelectitivy, Math.Pow(2, -count));
count += curJoinCount;
}
var estimatedNodeUnitSize = root.EstimatedRows *
Math.Pow(1000, candidateTree.MaterializedEdges.Count + candidateTree.UnmaterializedEdges.Count);
// Update Table Reference
newComponent.TableRef = GetPlanAndUpdateCost(candidateTree, newComponent, nodeTable, compTable, joinCondition,
degrees, selectivity, estimatedNodeUnitSize, estimatedSelectity);
return(newComponent);
}
/// <summary>
/// Calculate the number used for adjusting the SQL Server estimation in the downsize function.
/// </summary>
/// <param name="component"></param>
/// <param name="tablfRef"></param>
/// <param name="joinTable"></param>
/// <param name="size"></param>
/// <param name="estimatedSize"></param>
/// <param name="shrinkSize"></param>
/// <param name="joinTableTuple"></param>
private static void AdjustEstimation(
MatchComponent component,
WTableReference tablfRef,
WQualifiedJoin joinTable,
double size,
double estimatedSize,
double shrinkSize,
Tuple <WQualifiedJoin, String> joinTableTuple)
{
const int sizeFactor = 10;
int estimateFactor = 0;
if (size > sizeFactor * estimatedSize)
{
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
}
else if (sizeFactor * size < estimatedSize)
{
estimatedSize /= shrinkSize;
component.EstimateSize /= shrinkSize;
component.FatherListofDownSizeTable.Add(joinTableTuple);
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
}
if (estimateFactor > 1)
{
WTableReference crossApplyTable = tablfRef;
int pow = (int)(Math.Floor(Math.Log(estimateFactor, 1000)) + 1);
int adjustValue = (int)Math.Pow(estimateFactor, 1.0 / pow);
while (pow > 0)
{
crossApplyTable = new WUnqualifiedJoin
{
FirstTableRef = crossApplyTable,
SecondTableRef = new WSchemaObjectFunctionTableReference
{
SchemaObject = new WSchemaObjectName(
new Identifier {
Value = "dbo"
},
new Identifier {
Value = "UpSizeFunction"
}),
Parameters = new List <WScalarExpression>
{
new WValueExpression {
Value = adjustValue.ToString()
}
},
Alias = new Identifier
{
Value = Path.GetRandomFileName().Replace(".", "").Substring(0, 8),
}
},
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply
};
pow--;
component.EstimateSize *= adjustValue;
}
joinTable.FirstTableRef = crossApplyTable;
}
}
/// <summary>
/// Calculate join costs and update components using optimal join method & order
/// </summary>
/// <param name="nodeUnitCandidate"></param>
/// <param name="component"></param>
/// <param name="nodeTable"></param>
/// <param name="componentTable"></param>
/// <param name="joinCondition"></param>
/// <param name="nodeDegrees"></param>
/// <param name="estimatedNodeUnitSize"></param>
/// <param name="estimatedSelectivity"></param>
/// <returns></returns>
private static WTableReference GetPlanAndUpdateCost(
OneHeightTree nodeUnitCandidate,
MatchComponent component,
WTableReference nodeTable,
WTableReference componentTable,
WBooleanExpression joinCondition,
double nodeDegrees,
double joinSelectivity,
double estimatedNodeUnitSize,
double estimatedSelectivity)
{
var nodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeDegrees;
var componentSize = component.Size;
var estimatedCompSize = component.EstimateSize;
var cost = nodeUnitSize + componentSize;
//var joinSelectivity =
// nodeWithJoinMapping.SelectivityProduct;
//nodeWithJoinMapping.ExponentialSelevtivityProduct;
WQualifiedJoin joinTable = new WQualifiedJoin
{
FirstTableRef = componentTable,
SecondTableRef = nodeTable,
JoinCondition = joinCondition,
QualifiedJoinType = QualifiedJoinType.Inner,
JoinHint = JoinHint.Hash
};
var node = nodeUnitCandidate.TreeRoot;
// If the node is already in the component, then only multiply the degree to get the size
double nodeUnitActualSize;
if (component.MaterializedNodeSplitCount[node] > 0)
{
nodeUnitActualSize = nodeDegrees;
var cEstEdge = Math.Pow(1000, component.EdgeMaterilizedDict.Count(e => !e.Value));
var cSize = component.EstimateSize / cEstEdge;
var nSize = node.EstimatedRows;
if (nSize > cSize)
{
component.EstimateSize = estimatedNodeUnitSize * cEstEdge * estimatedSelectivity;
}
else
{
component.EstimateSize = component.EstimateSize * Math.Pow(1000, nodeUnitCandidate.UnmaterializedEdges.Count) *
estimatedSelectivity;
}
}
else
{
nodeUnitActualSize = nodeUnitSize;
component.EstimateSize *= estimatedNodeUnitSize * estimatedSelectivity;
}
//Update Size
component.Size *= nodeUnitActualSize * joinSelectivity;
// Update Cost
component.Cost += cost;
// Debug
#if DEBUG
//Trace.Write(component.NodeUnits.Count+" ");
//foreach (var n in component.NodeUnits.Where(e => e.Key != node.Node.ExposedName))
//{
// Trace.Write(n.Value.NodeRefName);
//}
//Trace.Write(component.NodeUnits[node.Node.ExposedName].NodeRefName+" ");
//Trace.Write(" "+(long)component.Cost+" "+(long)component.Size);
//Trace.Write(" ");
//foreach (var item in component.PopulatedEdgesName)
//{
// Trace.Write(item + " ");
//}
//Trace.Write("; ");
//foreach (var unpopulatedEdge in component.UnpopulatedEdges)
//{
// Trace.Write(unpopulatedEdge.Alias + " ");
//}
//Trace.WriteLine("");
#endif
// Update TableRef
// Only consider the size in the first join
if (component.MaterializedNodeSplitCount.Count == 2 && component.MaterializedNodeSplitCount.All(e => e.Value == 0))
{
var nodeInComp = component.MaterializedNodeSplitCount.Keys.First(e => e != node);
if (nodeUnitSize < componentSize)
{
joinTable.FirstTableRef = nodeTable;
joinTable.SecondTableRef = componentTable;
component.TotalMemory = component.DeltaMemory = nodeUnitSize;
component.EstimateTotalMemory = component.EstimateDeltaMemory = estimatedNodeUnitSize;
component.RightestTableRefSize = nodeInComp.EstimatedRows;
component.FatherOfRightestTableRef = new Tuple <WQualifiedJoin, String>(joinTable,
component.GetNodeRefName(nodeInComp));
AdjustEstimation(component, nodeTable, joinTable, nodeUnitSize, estimatedNodeUnitSize,
nodeUnitCandidate.TreeRoot.EstimatedRows, new Tuple <WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node)));
}
else
{
component.TotalMemory = component.DeltaMemory = componentSize;
component.EstimateTotalMemory = component.EstimateDeltaMemory = component.EstimateSize;
component.RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows;
component.FatherOfRightestTableRef = new Tuple <WQualifiedJoin, String>(joinTable, component.GetNodeRefName(node));
AdjustEstimation(component, componentTable, joinTable, componentSize, estimatedCompSize,
nodeInComp.EstimatedRows, new Tuple <WQualifiedJoin, string>(joinTable, component.GetNodeRefName(nodeInComp)));
}
}
else
{
double sizeFactor = 5;//1000;
double maxMemory = 1e8;
double loopJoinInnerThreshold = 10000;
double loopJoinOuterThreshold = 1000000;
// Left Deep
if (componentSize * sizeFactor < nodeUnitSize)
{
var curDeltaMemory = componentSize;
component.TotalMemory = component.DeltaMemory + curDeltaMemory;
component.DeltaMemory = curDeltaMemory;
var curDeltaEstimateMemory = component.EstimateSize;
component.EstimateTotalMemory = component.EstimateDeltaMemory + curDeltaEstimateMemory;
component.EstimateDeltaMemory = curDeltaEstimateMemory;
// Adjust estimation in sql server
AdjustEstimation(component, componentTable, joinTable, componentSize, estimatedCompSize,
component.RightestTableRefSize, component.FatherOfRightestTableRef);
component.FatherOfRightestTableRef = new Tuple <WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node));
component.RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows;
}
else
{
// Loop Join
if (
//((nodeUnitSize < loopJoinInnerThreshold /*&& componentSize < loopJoinOuterThreshold*/) || component.DeltaMemory + componentSize > maxMemory / 100) &&
((nodeUnitSize < loopJoinInnerThreshold && componentSize < loopJoinOuterThreshold) ||
component.DeltaMemory + componentSize > maxMemory) &&
nodeUnitCandidate.MaterializedEdges.Count == 0)
{
component.TotalMemory = component.DeltaMemory;
component.EstimateTotalMemory = component.EstimateDeltaMemory;
joinTable.JoinHint = JoinHint.Loop;
component.EstimateSize = estimatedCompSize * estimatedNodeUnitSize /
nodeUnitCandidate.TreeRoot.TableRowCount;
}
// Right Deep
else
{
joinTable.FirstTableRef = nodeTable;
joinTable.SecondTableRef = componentTable;
AdjustEstimation(component, nodeTable, joinTable, nodeUnitSize, estimatedNodeUnitSize,
node.EstimatedRows, new Tuple <WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node)));
component.TotalMemory += nodeUnitSize;
component.DeltaMemory = component.TotalMemory;
component.EstimateTotalMemory += estimatedNodeUnitSize;
component.EstimateDeltaMemory = component.EstimateTotalMemory;
}
}
}
return(new WParenthesisTableReference
{
Table = joinTable
});
}
/// <summary>
/// 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 static WTableReference ConstructUpSizeTableReference(WTableReference tableRef, double upSizeScalar, string dumb, DumbType dumbType, out double affectedEstimatedSize)
{
affectedEstimatedSize = 1.0;
int pow = (int)(Math.Floor(Math.Log(upSizeScalar, 1000)) + 1);
int adjustValue = (int)Math.Pow(upSizeScalar, 1.0 / pow);
while (pow > 0)
{
tableRef = new WUnqualifiedJoin
{
FirstTableRef = tableRef,
SecondTableRef = new WSchemaObjectFunctionTableReference
{
SchemaObject = new WSchemaObjectName(
new Identifier { Value = "dbo" },
new Identifier { Value = "UpSizeFunction2" }),
Parameters = new List<WScalarExpression>
{
new WValueExpression {Value = adjustValue.ToString()},
new WColumnReferenceExpression
{
MultiPartIdentifier =
new WMultiPartIdentifier(
new Identifier {Value = dumb},
new Identifier {Value = dumbType == DumbType.Node ? "GlobalNodeId" : "Sink"})
}
},
Alias = new Identifier
{
Value = Path.GetRandomFileName().Replace(".", "").Substring(0, 8),
}
},
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply
};
pow--;
affectedEstimatedSize *= adjustValue;
}
return tableRef;
}
private WMultiCommonTableExpression ConstructDeleteEdgeSelect(WSelectQueryBlock node, WEdgeColumnReferenceExpression edgeCol, string tempTableName
,out WTableReference sinkTable, out WTableReference sourceTable, ref bool hasReversedEdge)
{
sourceTable = null;
sinkTable = null;
string sourceTableName = null;
string sinkTableName = null;
var sourceTableScalar = node.SelectElements[0] as WSelectScalarExpression;
if (sourceTableScalar == null)
return null;
var sourceTableColumn = sourceTableScalar.SelectExpr as WColumnReferenceExpression;
if (sourceTableColumn == null)
return null;
sourceTableName = sourceTableColumn.MultiPartIdentifier.Identifiers.Last().Value;
var sourceNodeIdExpr = new WColumnReferenceExpression();
foreach (var identifier in sourceTableColumn.MultiPartIdentifier.Identifiers)
sourceNodeIdExpr.Add(identifier);
sourceNodeIdExpr.AddIdentifier("GlobalNodeId");
var collectVarVisitor = new CollectVariableVisitor();
var context = collectVarVisitor.Invoke(node);
if (!context.NodeTableDictionary.Any())
{
throw new GraphViewException("Missing From Clause in Delete Edge statement");
}
WColumnReferenceExpression sinkNodeIdExpr = null;
sourceTable = context[sourceTableName];
var groupByParams = new List<WScalarExpression>();
for (var index = 1; index < node.SelectElements.Count; ++index)
{
var element = node.SelectElements[index] as WSelectScalarExpression;
if (element == null)
return null;
//sink table
if (index == 1)
{
var sinkTableColumn = element.SelectExpr as WColumnReferenceExpression;
if (sinkTableColumn == null)
return null;
sinkTableName = sinkTableColumn.MultiPartIdentifier.Identifiers.Last().Value;
sinkTable = context[sinkTableName];
sinkNodeIdExpr = new WColumnReferenceExpression();
foreach (var identifier in sinkTableColumn.MultiPartIdentifier.Identifiers)
sinkNodeIdExpr.Add(identifier);
sinkNodeIdExpr.AddIdentifier("GlobalNodeId");
}
}
var sourceNodeId = new WSelectScalarExpression
{
SelectExpr = sourceNodeIdExpr,
ColumnName = "src",
};
var sinkNodeId = new WSelectScalarExpression
{
SelectExpr = sinkNodeIdExpr,
ColumnName = "sink"
};
var edgeId = new WSelectScalarExpression
{
SelectExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(
new Identifier[]
{
new Identifier {Value = edgeCol.Alias??string.Format("{0}_{1}_{2}",sourceTableName,edgeCol.MultiPartIdentifier.Identifiers.Last().Value,sinkTableName)},
new Identifier {Value = "Edgeid"}
})
},
ColumnName = "edgeid"
};
var sourceTableNameRef = sourceTable as WNamedTableReference;
if (sourceTableNameRef == null)
throw new GraphViewException("Source table of DELETE EDGE statement should be a named table reference.");
var sinkTableNameRef = sinkTable as WNamedTableReference;
if (sinkTableNameRef == null)
throw new GraphViewException("Sink table of DELETE EDGE statement should be a named table reference.");
int compare = string.CompareOrdinal(sourceTableNameRef.TableObjectName.ToString(), sinkTableNameRef.TableObjectName.ToString());
using (var command = Tx.Connection.CreateCommand())
{
command.Transaction = Tx;
command.Parameters.AddWithValue("@schema", WNamedTableReference.SchemaNameToTuple(sourceTableNameRef.TableObjectName).Item1);
command.Parameters.AddWithValue("@tableName", sourceTableNameRef.TableObjectName.Identifiers.Last().Value);
command.Parameters.AddWithValue("@colName", edgeCol.MultiPartIdentifier.Identifiers.Last().Value);
command.Parameters.AddWithValue("@role", WNodeTableColumnRole.Edge);
command.CommandText = string.Format(@"
SELECT NodeColumn.HasReversedEdge
FROM [{0}] AS NodeColumn
WHERE NodeColumn.TableSchema = @schema and
NodeColumn.TableName = @tableName and
NodeColumn.ColumnName = @colName and
NodeColumn.ColumnRole = @role",
GraphViewConnection.MetadataTables[1]);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
hasReversedEdge = bool.Parse(reader[0].ToString());
}
}
}
var reversedEdgeName = sourceTableNameRef.TableObjectName.Identifiers.Last().Value +
"_" + edgeCol.MultiPartIdentifier.Identifiers.Last().Value + "Reversed";
var reversedEdgeId = new WSelectScalarExpression
{
SelectExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(
new Identifier[]
{
new Identifier {Value = edgeCol.Alias??string.Format("{0}_{1}_{2}",sourceTableName,edgeCol.MultiPartIdentifier.Identifiers.Last().Value,sinkTableName)},
new Identifier {Value = "Edgeid"}
})
},
ColumnName = "edgeid"
};
var elements = new List<WSelectElement>
{
sourceNodeId,
sinkNodeId,
edgeId
};
var reversedElements = new List<WSelectElement>
{
sourceNodeId,
sinkNodeId,
reversedEdgeId,
};
return new WMultiCommonTableExpression
{
WCommonTableExpressions = new List<WCommonTableExpression>()
{
new WCommonTableExpression
{
ExpressionName = new Identifier
{
Value = hasReversedEdge && compare == 0 ? tempTableName + "_1" : tempTableName
},
QueryExpression = new WSelectQueryBlock
{
FromClause = node.FromClause,
WhereClause = new WWhereClause
{
SearchCondition = node.WhereClause.SearchCondition
},
HavingClause = node.HavingClause,
OrderByClause = node.OrderByClause,
TopRowFilter = node.TopRowFilter,
UniqueRowFilter = node.UniqueRowFilter,
SelectElements = elements,
MatchClause = node.MatchClause,
}
},
new WCommonTableExpression
{
ExpressionName = new Identifier
{
Value = hasReversedEdge && compare == 0 ? tempTableName + "_2" : tempTableName
},
QueryExpression = new WSelectQueryBlock
{
FromClause = node.FromClause,
WhereClause = hasReversedEdge ?
new WWhereClause { SearchCondition = ObjectExtensions.Copy(node.WhereClause.SearchCondition), }
: node.WhereClause,
HavingClause = node.HavingClause,
OrderByClause = node.OrderByClause,
TopRowFilter = node.TopRowFilter,
UniqueRowFilter = node.UniqueRowFilter,
SelectElements = hasReversedEdge ? reversedElements : elements,
MatchClause = hasReversedEdge? ConstructReversedMatchClause(node, context) : node.MatchClause,
}
},
}
};
}
/// <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="component"></param>
/// <param name="tablfRef"></param>
/// <param name="joinTable"></param>
/// <param name="size"></param>
/// <param name="estimatedSize"></param>
/// <param name="shrinkSize"></param>
/// <param name="joinTableTuple"></param>
private static void AdjustEstimation(
MatchComponent component,
WTableReference tablfRef,
WQualifiedJoin joinTable,
double size,
double estimatedSize,
double shrinkSize,
Tuple<WQualifiedJoin,String> joinTableTuple)
{
const int sizeFactor = 10;
int estimateFactor = 0;
if (size > sizeFactor*estimatedSize)
{
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
}
else if (sizeFactor*size < estimatedSize)
{
estimatedSize /= shrinkSize;
component.EstimateSize /= shrinkSize;
component.FatherListofDownSizeTable.Add(joinTableTuple);
estimateFactor = (int) Math.Ceiling(size/estimatedSize);
}
if (estimateFactor > 1)
{
WTableReference crossApplyTable = tablfRef;
int pow = (int) (Math.Floor(Math.Log(estimateFactor, 1000)) + 1);
int adjustValue = (int) Math.Pow(estimateFactor, 1.0/pow);
while (pow > 0)
{
crossApplyTable = new WUnqualifiedJoin
{
FirstTableRef = crossApplyTable,
SecondTableRef = new WSchemaObjectFunctionTableReference
{
SchemaObject = new WSchemaObjectName(
new Identifier {Value = "dbo"},
new Identifier {Value = "UpSizeFunction"}),
Parameters = new List<WScalarExpression>
{
new WValueExpression {Value = adjustValue.ToString()}
},
Alias = new Identifier
{
Value = Path.GetRandomFileName().Replace(".", "").Substring(0, 8),
}
},
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply
};
pow--;
component.EstimateSize *= adjustValue;
}
joinTable.FirstTableRef = crossApplyTable;
}
}
/// <summary>
/// Calculate join costs and update components using optimal join method & order
/// </summary>
/// <param name="nodeUnitCandidate"></param>
/// <param name="component"></param>
/// <param name="nodeTable"></param>
/// <param name="componentTable"></param>
/// <param name="joinCondition"></param>
/// <param name="nodeDegrees"></param>
/// <param name="estimatedNodeUnitSize"></param>
/// <param name="estimatedSelectivity"></param>
/// <returns></returns>
private static WTableReference GetPlanAndUpdateCost(
OneHeightTree nodeUnitCandidate,
MatchComponent component,
WTableReference nodeTable,
WTableReference componentTable,
WBooleanExpression joinCondition,
double nodeDegrees,
double joinSelectivity,
double estimatedNodeUnitSize,
double estimatedSelectivity)
{
var nodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeDegrees;
var componentSize = component.Size;
var estimatedCompSize = component.EstimateSize;
var cost = nodeUnitSize + componentSize;
//var joinSelectivity =
// nodeWithJoinMapping.SelectivityProduct;
//nodeWithJoinMapping.ExponentialSelevtivityProduct;
WQualifiedJoin joinTable = new WQualifiedJoin
{
FirstTableRef = componentTable,
SecondTableRef = nodeTable,
JoinCondition = joinCondition,
QualifiedJoinType = QualifiedJoinType.Inner,
JoinHint = JoinHint.Hash
};
var node = nodeUnitCandidate.TreeRoot;
// If the node is already in the component, then only multiply the degree to get the size
double nodeUnitActualSize;
if (component.MaterializedNodeSplitCount[node] > 0)
{
nodeUnitActualSize = nodeDegrees;
var cEstEdge = Math.Pow(1000, component.EdgeMaterilizedDict.Count(e=>!e.Value));
var cSize = component.EstimateSize/cEstEdge;
var nSize = node.EstimatedRows;
if (nSize > cSize)
{
component.EstimateSize = estimatedNodeUnitSize*cEstEdge*estimatedSelectivity;
}
else
{
component.EstimateSize = component.EstimateSize*Math.Pow(1000, nodeUnitCandidate.UnmaterializedEdges.Count)*
estimatedSelectivity;
}
}
else
{
nodeUnitActualSize = nodeUnitSize;
component.EstimateSize *= estimatedNodeUnitSize * estimatedSelectivity;
}
//Update Size
component.Size *= nodeUnitActualSize * joinSelectivity;
// Update Cost
component.Cost += cost;
// Debug
#if DEBUG
//Trace.Write(component.NodeUnits.Count+" ");
//foreach (var n in component.NodeUnits.Where(e => e.Key != node.Node.ExposedName))
//{
// Trace.Write(n.Value.NodeRefName);
//}
//Trace.Write(component.NodeUnits[node.Node.ExposedName].NodeRefName+" ");
//Trace.Write(" "+(long)component.Cost+" "+(long)component.Size);
//Trace.Write(" ");
//foreach (var item in component.PopulatedEdgesName)
//{
// Trace.Write(item + " ");
//}
//Trace.Write("; ");
//foreach (var unpopulatedEdge in component.UnpopulatedEdges)
//{
// Trace.Write(unpopulatedEdge.Alias + " ");
//}
//Trace.WriteLine("");
#endif
// Update TableRef
// Only consider the size in the first join
if (component.MaterializedNodeSplitCount.Count == 2 && component.MaterializedNodeSplitCount.All(e => e.Value == 0))
{
var nodeInComp = component.MaterializedNodeSplitCount.Keys.First(e => e != node);
if (nodeUnitSize < componentSize)
{
joinTable.FirstTableRef = nodeTable;
joinTable.SecondTableRef = componentTable;
component.TotalMemory = component.DeltaMemory = nodeUnitSize;
component.EstimateTotalMemory = component.EstimateDeltaMemory = estimatedNodeUnitSize;
component.RightestTableRefSize = nodeInComp.EstimatedRows;
component.FatherOfRightestTableRef = new Tuple<WQualifiedJoin, String>(joinTable,
component.GetNodeRefName(nodeInComp));
AdjustEstimation(component, nodeTable, joinTable, nodeUnitSize, estimatedNodeUnitSize,
nodeUnitCandidate.TreeRoot.EstimatedRows, new Tuple<WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node)));
}
else
{
component.TotalMemory = component.DeltaMemory = componentSize;
component.EstimateTotalMemory = component.EstimateDeltaMemory = component.EstimateSize;
component.RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows;
component.FatherOfRightestTableRef = new Tuple<WQualifiedJoin, String>(joinTable, component.GetNodeRefName(node));
AdjustEstimation(component, componentTable, joinTable, componentSize, estimatedCompSize,
nodeInComp.EstimatedRows, new Tuple<WQualifiedJoin, string>(joinTable, component.GetNodeRefName(nodeInComp)));
}
}
else
{
double sizeFactor = 5;//1000;
double maxMemory = 1e8;
double loopJoinInnerThreshold = 10000;
double loopJoinOuterThreshold = 1000000;
// Left Deep
if (componentSize*sizeFactor < nodeUnitSize)
{
var curDeltaMemory = componentSize;
component.TotalMemory = component.DeltaMemory + curDeltaMemory;
component.DeltaMemory = curDeltaMemory;
var curDeltaEstimateMemory = component.EstimateSize;
component.EstimateTotalMemory = component.EstimateDeltaMemory + curDeltaEstimateMemory;
component.EstimateDeltaMemory = curDeltaEstimateMemory;
// Adjust estimation in sql server
AdjustEstimation(component, componentTable, joinTable, componentSize, estimatedCompSize,
component.RightestTableRefSize, component.FatherOfRightestTableRef);
component.FatherOfRightestTableRef = new Tuple<WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node));
component.RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows;
}
else
{
// Loop Join
if (
//((nodeUnitSize < loopJoinInnerThreshold /*&& componentSize < loopJoinOuterThreshold*/) || component.DeltaMemory + componentSize > maxMemory / 100) &&
((nodeUnitSize < loopJoinInnerThreshold && componentSize < loopJoinOuterThreshold) ||
component.DeltaMemory + componentSize > maxMemory) &&
nodeUnitCandidate.MaterializedEdges.Count==0)
{
component.TotalMemory = component.DeltaMemory;
component.EstimateTotalMemory = component.EstimateDeltaMemory;
joinTable.JoinHint = JoinHint.Loop;
component.EstimateSize = estimatedCompSize*estimatedNodeUnitSize/
nodeUnitCandidate.TreeRoot.TableRowCount;
}
// Right Deep
else
{
joinTable.FirstTableRef = nodeTable;
joinTable.SecondTableRef = componentTable;
AdjustEstimation(component, nodeTable, joinTable, nodeUnitSize, estimatedNodeUnitSize,
node.EstimatedRows, new Tuple<WQualifiedJoin, string>(joinTable, component.GetNodeRefName(node)));
component.TotalMemory += nodeUnitSize;
component.DeltaMemory = component.TotalMemory;
component.EstimateTotalMemory += estimatedNodeUnitSize;
component.EstimateDeltaMemory = component.EstimateTotalMemory;
}
}
}
return new WParenthesisTableReference
{
Table = joinTable
};
}
public override void Visit(WSelectQueryBlock node)
{
if (node.SelectElements == null || node.SelectElements.Count < 2)
{
throw new GraphViewException("Numbers of select elements mismatch.");
}
var sourceTableScalar = node.SelectElements[0] as WSelectScalarExpression;
if (sourceTableScalar == null)
throw new GraphViewException("Source node id should be a scalar expression.");
var sourceTableColumn = sourceTableScalar.SelectExpr as WColumnReferenceExpression;
if (sourceTableColumn == null)
throw new GraphViewException("Source node id column should be a column reference expression.");
var sourceTableName = sourceTableColumn.MultiPartIdentifier.Identifiers.Last().Value;
var sourceNodeIdExpr = new WColumnReferenceExpression();
foreach (var identifier in sourceTableColumn.MultiPartIdentifier.Identifiers)
sourceNodeIdExpr.Add(identifier);
sourceNodeIdExpr.AddIdentifier("GlobalNodeId");
if (node.SelectElements.Count < 2)
throw new GraphViewException("Source and Sink tables should be specified in select elements.");
if (node.GroupByClause != null)
{
throw new GraphViewException("GROUP BY clause is not allowed in INSERT EDGE statement.");
}
var collectVarVisitor = new CollectVariableVisitor();
var context = collectVarVisitor.Invoke(node);
WColumnReferenceExpression sinkNodeIdExpr = null;
for (var index = 1; index < node.SelectElements.Count; ++index)
{
var element = node.SelectElements[index] as WSelectScalarExpression;
if (element == null)
throw new GraphViewException("Edge property should be a scalar expression.");
//sink table
if (index == 1)
{
var sinkTableColumn = element.SelectExpr as WColumnReferenceExpression;
if (sinkTableColumn == null)
throw new GraphViewException("Sink node id column should be a column reference expression.");
var sinkTableName = sinkTableColumn.MultiPartIdentifier.Identifiers.Last().Value;
_sinkTable = context[sinkTableName];
sinkNodeIdExpr = new WColumnReferenceExpression();
foreach (var identifier in sinkTableColumn.MultiPartIdentifier.Identifiers)
sinkNodeIdExpr.Add(identifier);
sinkNodeIdExpr.AddIdentifier("GlobalNodeId");
}
else
{
_edgeProperties.Add(element.SelectExpr);
}
}
var sourceTable = context[sourceTableName] as WNamedTableReference;
if (sourceTable == null)
{
throw new GraphViewException("Source table of INSERT EDGE statement should be a named table reference.");
}
var sourceNodeId = new WSelectScalarExpression
{
SelectExpr = sourceNodeIdExpr,
ColumnName = "src",
};
var sinkNodeId = new WSelectScalarExpression
{
SelectExpr = sinkNodeIdExpr,
ColumnName = "sink"
};
var elements = new List<WSelectElement>
{
sourceNodeId,
sinkNodeId
};
var result = new WCommonTableExpression
{
ExpressionName = new Identifier {Value = _tempTableName},
QueryExpression = new WSelectQueryBlock
{
FromClause = node.FromClause,
WhereClause = new WWhereClause
{
SearchCondition = node.WhereClause.SearchCondition
},
HavingClause = node.HavingClause,
OrderByClause = node.OrderByClause,
TopRowFilter = node.TopRowFilter,
UniqueRowFilter = node.UniqueRowFilter,
SelectElements = elements,
MatchClause = node.MatchClause
}
};
_result = result;
}
private WCommonTableExpression ConstructDeleteEdgeSelect(WSelectQueryBlock node, string edgeAlias, string tempTableName
,out WTableReference sinkTable, out WTableReference sourceTable)
{
sourceTable = null;
sinkTable = null;
var sourceTableScalar = node.SelectElements[0] as WSelectScalarExpression;
if (sourceTableScalar == null)
return null;
var sourceTableColumn = sourceTableScalar.SelectExpr as WColumnReferenceExpression;
if (sourceTableColumn == null)
return null;
var sourceTableName = sourceTableColumn.MultiPartIdentifier.Identifiers.Last().Value;
var sourceNodeIdExpr = new WColumnReferenceExpression();
foreach (var identifier in sourceTableColumn.MultiPartIdentifier.Identifiers)
sourceNodeIdExpr.Add(identifier);
sourceNodeIdExpr.AddIdentifier("GlobalNodeId");
var collectVarVisitor = new CollectVariableVisitor();
var context = collectVarVisitor.Invoke(node);
if (!context.NodeTableDictionary.Any())
{
throw new GraphViewException("Missing From Clause in Delete Edge statement");
}
WColumnReferenceExpression sinkNodeIdExpr = null;
sourceTable = context[sourceTableName];
var groupByParams = new List<WScalarExpression>();
for (var index = 1; index < node.SelectElements.Count; ++index)
{
var element = node.SelectElements[index] as WSelectScalarExpression;
if (element == null)
return null;
//sink table
if (index == 1)
{
var sinkTableColumn = element.SelectExpr as WColumnReferenceExpression;
if (sinkTableColumn == null)
return null;
var sinkTableName = sinkTableColumn.MultiPartIdentifier.Identifiers.Last().Value;
sinkTable = context[sinkTableName];
sinkNodeIdExpr = new WColumnReferenceExpression();
foreach (var identifier in sinkTableColumn.MultiPartIdentifier.Identifiers)
sinkNodeIdExpr.Add(identifier);
sinkNodeIdExpr.AddIdentifier("GlobalNodeId");
}
}
var sourceNodeId = new WSelectScalarExpression
{
SelectExpr = sourceNodeIdExpr,
ColumnName = "src",
};
var sinkNodeId = new WSelectScalarExpression
{
SelectExpr = sinkNodeIdExpr,
ColumnName = "sink"
};
var edgeId = new WSelectScalarExpression
{
SelectExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(
new Identifier[]
{
new Identifier {Value = edgeAlias},
new Identifier {Value = "Edgeid"}
}
)
},
ColumnName = "edgeid"
};
var elements = new List<WSelectElement>
{
sourceNodeId,
sinkNodeId,
edgeId
};
return new WCommonTableExpression
{
ExpressionName = new Identifier { Value = tempTableName },
QueryExpression = new WSelectQueryBlock
{
FromClause = node.FromClause,
WhereClause = new WWhereClause
{
SearchCondition = node.WhereClause.SearchCondition
},
HavingClause = node.HavingClause,
OrderByClause = node.OrderByClause,
TopRowFilter = node.TopRowFilter,
UniqueRowFilter = node.UniqueRowFilter,
SelectElements = elements,
MatchClause = node.MatchClause,
}
};
}
/// <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 static WTableReference SpanTableRef(WTableReference tableRef, MatchEdge edge, string nodeAlias)
{
tableRef = new WUnqualifiedJoin
{
FirstTableRef = tableRef,
SecondTableRef = EdgeToTableReference(edge, nodeAlias),
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply,
};
return tableRef;
}
/// <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;
}
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;
}
public virtual void Visit(WTableReference node)
{
node.AcceptChildren(this);
}
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);
}
public WCommonTableExpression Invoke(WSqlFragment node, string tempTableName, out WTableReference sinkTable, out List<WScalarExpression> edgeProperties)
{
_edgeProperties = new List<WScalarExpression>
{
new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "sink"})
}
};
_tempTableName = tempTableName;
node.Accept(this);
sinkTable = _sinkTable;
edgeProperties = _edgeProperties;
return _result;
}
public MatchComponent(MatchNode node):this()
{
Nodes.Add(node);
MaterializedNodeSplitCount[node] = 0;
//SinkNodeStatisticsDict[node] = new Statistics ();
Cardinality *= node.EstimatedRows;
SqlEstimatedSize *= node.EstimatedRows;
TableRef = new WNamedTableReference
{
Alias = new Identifier { Value = node.RefAlias},
TableObjectName = node.NodeTableObjectName
};
}
public virtual void Visit(WTableReference node)
{
node.AcceptChildren(this);
}
/// <summary>
/// Deep Copy
/// </summary>
/// <param name="component"></param>
public MatchComponent(MatchComponent component)
{
Nodes = new HashSet<MatchNode>(component.Nodes);
EdgeMaterilizedDict = new Dictionary<MatchEdge, bool>(component.EdgeMaterilizedDict);
MaterializedNodeSplitCount = new Dictionary<MatchNode, int>(component.MaterializedNodeSplitCount);
UnmaterializedNodeMapping = new Dictionary<MatchNode, List<MatchEdge>>();
foreach (var nodeMapping in component.UnmaterializedNodeMapping)
{
UnmaterializedNodeMapping[nodeMapping.Key] = new List<MatchEdge>(nodeMapping.Value);
}
SinkNodeStatisticsDict = new Dictionary<MatchNode, Statistics>(component.SinkNodeStatisticsDict);
TableRef = component.TableRef;
Cardinality = component.Cardinality;
Cost = component.Cost;
DeltaMemory = component.DeltaMemory;
TotalMemory = component.TotalMemory;
SqlEstimatedDeltaMemory = component.SqlEstimatedDeltaMemory;
SqlEstimatedTotalMemory = component.SqlEstimatedTotalMemory;
SqlEstimatedSize = component.SqlEstimatedSize;
RightestTableAlias = component.RightestTableAlias;
RightestTableRefSize = component.RightestTableRefSize;
}