|
public ToTableReference ( string nodeAlias, string dumbNode, |
||
| nodeAlias | string | |
| dumbNode | string | |
| metaData | ||
| return | GraphView.WTableReference | |
/// <summary>
/// Calculate the number used for adjusting the SQL Server estimation in the downsize function.
/// </summary>
/// <param name="metaData"></param>
/// <param name="joinCondition"></param>
/// <param name="candidateJoinUnit"></param>
/// <param name="nodeAlias"></param>
/// <param name="affectedSqlEstimatedSize"></param>
private static WTableReference AdjustEstimation(CandidateJoinUnit candidateJoinUnit, string nodeAlias, GraphMetaData metaData, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize)
{
const int sizeFactor = 10;
int estimateFactor = 0;
double size = candidateJoinUnit.EdgeDegrees;
double estimatedSize = candidateJoinUnit.SqlEstimatedEdgeDegrees;
double shrinkSize = candidateJoinUnit.TreeRoot.EstimatedRows;
WTableReference tableReference = candidateJoinUnit.ToTableReference(nodeAlias, metaData);
affectedSqlEstimatedSize = 1.0;
joinCondition = null;
if (size > sizeFactor * estimatedSize)
{
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
}
else if (sizeFactor * size < estimatedSize)
{
shrinkSize = 1.0 / (1 - Math.Pow((1 - 1.0 / shrinkSize), 1.5));
affectedSqlEstimatedSize /= shrinkSize;
estimatedSize /= shrinkSize;
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
joinCondition = ConstructDownSizeJoinCondition(nodeAlias);
}
if (estimateFactor > 1)
{
double affectedUpSize;
tableReference = ConstructUpSizeTableReference(tableReference, estimateFactor,
out affectedUpSize);
affectedSqlEstimatedSize *= affectedUpSize;
}
return(tableReference);
}
/// <summary>
/// Calculate join costs and update components using optimal join method & order
/// </summary>
/// <param name="nodeUnitCandidate"></param>
/// <param name="joinCondition"></param>
/// <param name="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;
//}
private static WTableReference AdjustEstimation(CandidateJoinUnit candidateJoinUnit, string nodeAlias, GraphMetaData metaData, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize)
{
const int sizeFactor = 10;
int estimateFactor = 0;
List<MatchEdge> matEdges = candidateJoinUnit.PreMatOutgoingEdges;
double size = matEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next);
double estimatedSize = Math.Pow(1000, matEdges.Count);
double shrinkSize = candidateJoinUnit.TreeRoot.EstimatedRows;
WTableReference tableReference = candidateJoinUnit.ToTableReference(nodeAlias, nodeAlias, metaData);
affectedSqlEstimatedSize = 1.0;
joinCondition = null;
if (size > sizeFactor * estimatedSize)
{
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
}
else if (sizeFactor * size < estimatedSize)
{
shrinkSize = 1.0 / (1 - Math.Pow((1 - 1.0 / shrinkSize), 1.5));
affectedSqlEstimatedSize /= shrinkSize;
estimatedSize /= shrinkSize;
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
joinCondition = ConstructDownSizeJoinCondition(nodeAlias);
}
if (estimateFactor > 1)
{
double affectedUpSize;
tableReference = ConstructUpSizeTableReference(tableReference, estimateFactor,
out affectedUpSize);
affectedSqlEstimatedSize *= affectedUpSize;
}
return tableReference;
}
/// <summary>
/// Calculate 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>
/// Calculate the number used for adjusting the SQL Server estimation in the downsize function.
/// </summary>
/// <param name="metaData"></param>
/// <param name="joinCondition"></param>
/// <param name="candidateJoinUnit"></param>
/// <param name="nodeAlias"></param>
/// <param name="affectedSqlEstimatedSize"></param>
private static WTableReference AdjustEstimation(CandidateJoinUnit candidateJoinUnit, string nodeAlias, GraphMetaData metaData, out WBooleanExpression joinCondition, out double affectedSqlEstimatedSize)
{
const int sizeFactor = 10;
int estimateFactor = 0;
double size = candidateJoinUnit.EdgeDegrees;
double estimatedSize = candidateJoinUnit.SqlEstimatedEdgeDegrees;
double shrinkSize = candidateJoinUnit.TreeRoot.EstimatedRows;
WTableReference tableReference = candidateJoinUnit.ToTableReference(nodeAlias, metaData);
affectedSqlEstimatedSize = 1.0;
joinCondition = null;
if (size > sizeFactor * estimatedSize)
{
estimateFactor = (int)Math.Ceiling(size / estimatedSize);
}
else if (sizeFactor*size < estimatedSize)
{
shrinkSize = 1.0/(1 - Math.Pow((1 - 1.0/shrinkSize), 1.5));
affectedSqlEstimatedSize /= shrinkSize;
estimatedSize /= shrinkSize;
estimateFactor = (int) Math.Ceiling(size/estimatedSize);
joinCondition = ConstructDownSizeJoinCondition(nodeAlias);
}
if (estimateFactor > 1)
{
double affectedUpSize;
tableReference = ConstructUpSizeTableReference(tableReference, estimateFactor,
out affectedUpSize);
affectedSqlEstimatedSize *= affectedUpSize;
}
return tableReference;
}
/// <summary>
/// Calculate join costs and update components using optimal join method & order
/// </summary>
/// <param name="nodeUnitCandidate"></param>
/// <param name="joinCondition"></param>
/// <param name="joinSelectivity"></param>
/// <param name="estimatedSelectivity"></param>
/// <returns></returns>
private void ConstructPhysicalJoinAndUpdateCost(
CandidateJoinUnit nodeUnitCandidate,
WBooleanExpression joinCondition,
double joinSelectivity,
double estimatedSelectivity,
GraphMetaData metaData)
{
var nodeDegrees = nodeUnitCandidate.EdgeDegrees;
var nodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows * nodeDegrees;
var estimatedNodeUnitSize = nodeUnitCandidate.TreeRoot.EstimatedRows *
nodeUnitCandidate.SqlEstimatedEdgeDegrees;
var componentSize = Cardinality;
var estimatedCompSize = SqlEstimatedSize;
var node = nodeUnitCandidate.TreeRoot;
// If the node is already in the component, then only multiply the degree to get the size
double nodeUnitActualSize;
double newCompEstSize;
if (MaterializedNodeSplitCount[node] > 0)
{
nodeUnitActualSize = nodeDegrees;
var cEstEdge = Math.Pow(1000, EdgeMaterilizedDict.Count(e => !e.Value));
var cSize = SqlEstimatedSize / cEstEdge;
var nSize = node.EstimatedRows;
if (nSize > cSize)
{
newCompEstSize = estimatedNodeUnitSize * cEstEdge * estimatedSelectivity;
}
else
{
newCompEstSize = SqlEstimatedSize * Math.Pow(1000, nodeUnitCandidate.UnmaterializedEdges.Count) * estimatedSelectivity;
}
}
else
{
nodeUnitActualSize = nodeUnitSize;
newCompEstSize = SqlEstimatedSize * estimatedNodeUnitSize * estimatedSelectivity;
}
newCompEstSize = newCompEstSize < 1.0 ? 1.0 : newCompEstSize;
bool firstJoin = MaterializedNodeSplitCount.Count == 2 &&
MaterializedNodeSplitCount.All(e => e.Value == 0);
// Update TableRef
double loopJoinOuterThreshold = 1e4; //1e6;
double sizeFactor = 5; //1000;
double maxMemory = 1e8;
double loopCost = componentSize * Math.Log(nodeUnitCandidate.TreeRoot.EstimatedRows, 512) * 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;
}
