private WTableReference ParseTableReference(TableReference tabRef)
{
if (tabRef == null)
{
return null;
}
var tabRefWithAlias = tabRef as TableReferenceWithAlias;
if (tabRefWithAlias!=null && tabRefWithAlias.Alias!=null &&
GraphViewKeywords._keywords.Contains(tabRefWithAlias.Alias.Value))
{
var token = _tokens[tabRefWithAlias.Alias.FirstTokenIndex];
throw new SyntaxErrorException(token.Line, tabRefWithAlias.Alias.Value,
"System restricted Name cannot be used");
}
switch (tabRef.GetType().Name)
{
case "NamedTableReference":
{
var oref = tabRef as NamedTableReference;
if (oref.SchemaObject.BaseIdentifier.QuoteType == QuoteType.NotQuoted &&
(oref.SchemaObject.BaseIdentifier.Value[0] == '@' ||
oref.SchemaObject.BaseIdentifier.Value[0] == '#'))
{
var pref = new WSpecialNamedTableReference
{
Alias = oref.Alias,
TableHints = new List<WTableHint>(),
FirstTokenIndex = oref.FirstTokenIndex,
LastTokenIndex = oref.LastTokenIndex,
TableObjectName = ParseSchemaObjectName(oref.SchemaObject),
};
if (oref.TableHints != null)
{
foreach (var hint in oref.TableHints)
pref.TableHints.Add(ParseTableHint(hint));
}
return pref;
}
else
{
var pref = new WNamedTableReference
{
Alias = oref.Alias,
TableHints = new List<WTableHint>(),
FirstTokenIndex = oref.FirstTokenIndex,
LastTokenIndex = oref.LastTokenIndex,
TableObjectName = ParseSchemaObjectName(oref.SchemaObject),
};
if (oref.TableHints != null)
{
foreach (var hint in oref.TableHints)
pref.TableHints.Add(ParseTableHint(hint));
}
return pref;
}
}
case "QueryDerivedTable":
{
var oref = tabRef as QueryDerivedTable;
var pref = new WQueryDerivedTable
{
QueryExpr = ParseSelectQueryStatement(oref.QueryExpression),
Alias = oref.Alias,
Columns = oref.Columns,
FirstTokenIndex = oref.FirstTokenIndex,
LastTokenIndex = oref.LastTokenIndex,
};
return pref;
}
case "SchemaObjectFunctionTableReference":
{
var oref = tabRef as SchemaObjectFunctionTableReference;
var pref = new WSchemaObjectFunctionTableReference
{
Alias = oref.Alias,
Columns = oref.Columns,
SchemaObject = ParseSchemaObjectName(oref.SchemaObject),
FirstTokenIndex = oref.FirstTokenIndex,
LastTokenIndex = oref.LastTokenIndex
};
if (oref.Parameters == null)
return pref;
pref.Parameters = new List<WScalarExpression>();
foreach (var param in oref.Parameters)
pref.Parameters.Add(ParseScalarExpression(param));
return pref;
}
case "QualifiedJoin":
{
var oref = tabRef as QualifiedJoin;
var pref = new WQualifiedJoin
{
FirstTableRef = ParseTableReference(oref.FirstTableReference),
SecondTableRef = ParseTableReference(oref.SecondTableReference),
QualifiedJoinType = oref.QualifiedJoinType,
JoinHint = oref.JoinHint,
JoinCondition = ParseBooleanExpression(oref.SearchCondition),
FirstTokenIndex = oref.FirstTokenIndex,
LastTokenIndex = oref.LastTokenIndex,
};
return pref;
}
case "UnqualifiedJoin":
{
var oref = tabRef as UnqualifiedJoin;
var pref = new WUnqualifiedJoin
{
FirstTableRef = ParseTableReference(oref.FirstTableReference),
SecondTableRef = ParseTableReference(oref.SecondTableReference),
UnqualifiedJoinType = oref.UnqualifiedJoinType,
FirstTokenIndex = oref.FirstTokenIndex,
LastTokenIndex = oref.LastTokenIndex,
};
return pref;
}
case "JoinParenthesisTableReference":
{
var ptab = tabRef as JoinParenthesisTableReference;
var wptab = new WParenthesisTableReference
{
Table = ParseTableReference(ptab.Join),
FirstTokenIndex = ptab.FirstTokenIndex,
LastTokenIndex = ptab.LastTokenIndex,
};
return wptab;
}
default:
return null;
}
}
public virtual void Visit(WParenthesisTableReference node)
{
node.AcceptChildren(this);
}
public virtual void Visit(WParenthesisTableReference node)
{
node.AcceptChildren(this);
}
/// <summary>
/// Calculate join costs and update components using optimal join method & order
/// </summary>
/// <param name="nodeUnitCandidate"></param>
/// <param name="joinCondition"></param>
/// <param name="preJoinSelectivity"></param>
/// <param name="postJoinSelectivity"></param>
/// <param name="estimatedSelectivity"></param>
/// <param name="metaData"></param>
/// <param name="isExecutable"></param>
private void ConstructPhysicalJoinAndUpdateCost(
CandidateJoinUnit nodeUnitCandidate,
WBooleanExpression joinCondition,
double preJoinSelectivity,
double postJoinSelectivity,
double estimatedSelectivity,
GraphMetaData metaData)
{
const double scaleFactor = 1.5;
const int sqlInPreMatEdgeSelectivityThreshold = 5;
var firstJoin = MaterializedNodeSplitCount.Count == 2;
var inPreMatEdges = nodeUnitCandidate.PreMatIncomingEdges;
var inPostMatEdges = nodeUnitCandidate.PostMatIncomingEdges;
var outPreMatEdges = nodeUnitCandidate.PreMatOutgoingEdges;
var outPostMatEdges = nodeUnitCandidate.PostMatOutgoingEdges;
var postMatEdges = inPostMatEdges.Select(e => new Tuple<MatchEdge, EdgeDir>(e, EdgeDir.In))
.Union(
outPostMatEdges.Select(e => new Tuple<MatchEdge, EdgeDir>(e, EdgeDir.Out)))
.OrderBy(t => t.Item1.AverageDegree)
.ToList();
var compDegrees = inPreMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur*next);
var nodeDegrees = outPreMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next);
var root = nodeUnitCandidate.TreeRoot;
var componentSize = Cardinality;
double sizeFactor = 5; // 1000;
var loopCost = nodeUnitCandidate.JoinHint == JoinHint.Loop
? componentSize * compDegrees * Math.Log(root.EstimatedRows, 512)
: double.MaxValue;
// only calc the size of table used to join
var matCompSizeWhenJoin = componentSize*compDegrees;
var matUnitSizeWhenJoin = root.EstimatedRows*nodeDegrees;
var hashCost = matCompSizeWhenJoin + matUnitSizeWhenJoin;
double loopJoinOuterThreshold = 1e4;//1e6;
double maxMemory = 1e8;
double cost;
// loop join
if (nodeUnitCandidate.JoinHint == JoinHint.Loop
//inPreMatEdges.Any() && !outPreMatEdges.Any() &&
//(
// //componentSize < loopJoinOuterThreshold || // the outer table is relatively small
// loopCost < hashCost ||
// (DeltaMemory + matCompSizeWhenJoin > maxMemory && DeltaMemory + matUnitSizeWhenJoin > maxMemory)
// // memory is in pressure
//)
)
{
if (firstJoin)
{
RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows;
RightestTableAlias = root.RefAlias;
LastTable = Nodes.First(n => n.NodeAlias != root.NodeAlias);
//LastTableAlias = LastTable.RefAlias;
LastJoinHint = JoinHint.Loop;
LastJoinSqlEstCardinality = LastTable.TableRowCount;
}
cost = loopCost;
var sqlInPreMatEdgesSelectivity = 1.0;
for (var i = 1; i < inPreMatEdges.Count && i <= sqlInPreMatEdgeSelectivityThreshold; ++i)
sqlInPreMatEdgesSelectivity = Math.Sqrt(sqlInPreMatEdgesSelectivity)/10;
var sqlEstPreJoinEdgeSize = Math.Pow(100, LastJoinPostMatEdgesCount)*Math.Pow(1000, inPreMatEdges.Count)*
sqlInPreMatEdgesSelectivity;
var sqlEstPreJoinInputSize = LastJoinSqlEstCardinality * (LastJoinHint == JoinHint.Loop
? LastTable.EstimatedRows / LastTable.TableRowCount
: 1.0) *
sqlEstPreJoinEdgeSize;
var estimateFactor = 0;
if (matCompSizeWhenJoin >= sqlEstPreJoinInputSize * scaleFactor)
estimateFactor = (int) Math.Ceiling(matCompSizeWhenJoin/sqlEstPreJoinInputSize);
else if (matCompSizeWhenJoin*scaleFactor < sqlEstPreJoinInputSize)
estimateFactor = -1;
var affectedUpsize = 1.0;
if (estimateFactor >= (int) Math.Ceiling(scaleFactor))
{
if (LastJoinHint == JoinHint.Loop)
TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, LastTableAlias, DumbType.Node, out affectedUpsize);
else if (LastJoinPostMatEdgesCount > 0)
TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, LastPostMatEdgeAlias, DumbType.Edge, out affectedUpsize);
else
TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, out affectedUpsize);
}
else if (estimateFactor == -1 && LastJoinHint == JoinHint.Loop)
{
sqlEstPreJoinInputSize = LastJoinSqlEstCardinality*
Math.Sqrt(LastTable.EstimatedRows/LastTable.TableRowCount)/
LastTable.TableRowCount * 1.5 * sqlEstPreJoinEdgeSize;
if (matCompSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor)
TableRef = ConstructUpSizeTableReference(TableRef, (int) Math.Ceiling(matCompSizeWhenJoin/sqlEstPreJoinInputSize),
LastTableAlias, DumbType.Node, out affectedUpsize);
}
foreach (var edge in inPreMatEdges)
TableRef = SpanTableRef(TableRef, edge, edge.SourceNode.RefAlias, LastTableAlias, metaData);
WTableReference table = new WQualifiedJoin
{
FirstTableRef = TableRef,
SecondTableRef =
nodeUnitCandidate.ToTableReference(root.RefAlias, root.RefAlias, metaData),
JoinCondition = estimateFactor == -1
? WBooleanBinaryExpression.Conjunction(joinCondition, ConstructDownSizeJoinCondition(LastTableAlias))
: joinCondition,
QualifiedJoinType = QualifiedJoinType.Inner,
JoinHint = JoinHint.Loop
};
table = postMatEdges.Aggregate(table, (current, next) => new WUnqualifiedJoin
{
FirstTableRef = current,
SecondTableRef =
next.Item1.ToSchemaObjectFunction(next.Item1.SourceNode.RefAlias,
next.Item2 == EdgeDir.In ? root.RefAlias : LastTableAlias,
metaData),
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply,
});
TableRef = new WParenthesisTableReference
{
Table = table,
};
LastTable = root;
LastTableAlias = root.RefAlias;
LastJoinHint = JoinHint.Loop;
LastJoinSqlEstCardinality = sqlEstPreJoinInputSize*affectedUpsize;
LastJoinPostMatEdgesCount = postMatEdges.Count;
LastPostMatEdgeAlias = LastJoinPostMatEdgesCount > 0 ? postMatEdges.Last().Item1.EdgeAlias : null;
SqlEstimatedSize = sqlEstPreJoinInputSize*root.EstimatedRows/root.TableRowCount*
Math.Pow(100, postMatEdges.Count);
SqlEstimatedSize = SqlEstimatedSize < 1.0 ? 1.0 : SqlEstimatedSize;
Cardinality = matCompSizeWhenJoin *
inPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) *
matUnitSizeWhenJoin *
outPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) *
preJoinSelectivity / root.TableRowCount * postJoinSelectivity;
}
// hash join
else
{
cost = hashCost;
WBooleanExpression adjustedJoincondition = null;
WTableReference buildTableReference = nodeUnitCandidate.ToTableReference(root.RefAlias, root.RefAlias, metaData);
double affectedUpsize = 1.0, sqlEstPreJoinEdgeSize;
int estimateFactor;
var sqlEstPreJoinInputSize = root.EstimatedRows;
if (firstJoin)
{
LastTable = Nodes.First(n => n.NodeAlias != root.NodeAlias);
//LastTableAlias = LastTable.RefAlias;
LastJoinHint = JoinHint.Loop;
LastJoinSqlEstCardinality = LastTable.TableRowCount;
}
// Build table adjustment
if (outPreMatEdges.Any())
{
var sqlOutPreMatEdgesSelectivity = 1.0;
foreach (var group in outPreMatEdges.GroupBy(e => e.SinkNode))
{
var selectivity = 1.0;
for (var i = 1; i < group.Count() && i <= sqlInPreMatEdgeSelectivityThreshold; ++i)
selectivity = Math.Sqrt(selectivity) / 10;
sqlOutPreMatEdgesSelectivity *= selectivity;
}
sqlEstPreJoinEdgeSize = Math.Pow(1000, outPreMatEdges.Count) * sqlOutPreMatEdgesSelectivity;
sqlEstPreJoinInputSize *= sqlEstPreJoinEdgeSize;
estimateFactor = 0;
if (matUnitSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor)
estimateFactor = (int) Math.Ceiling(matUnitSizeWhenJoin/sqlEstPreJoinInputSize);
else if (matUnitSizeWhenJoin*scaleFactor < sqlEstPreJoinInputSize)
{
estimateFactor = -1;
adjustedJoincondition = ConstructDownSizeJoinCondition(root.RefAlias);
}
if (estimateFactor >= (int) Math.Ceiling(scaleFactor))
buildTableReference = ConstructUpSizeTableReference(buildTableReference, estimateFactor,
root.RefAlias, DumbType.Node, out affectedUpsize);
else if (estimateFactor == -1)
{
sqlEstPreJoinInputSize = Math.Sqrt(root.EstimatedRows/root.TableRowCount)*1.5*sqlEstPreJoinEdgeSize;
if (matUnitSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor)
buildTableReference = ConstructUpSizeTableReference(buildTableReference,
(int)Math.Ceiling(matUnitSizeWhenJoin / sqlEstPreJoinInputSize),
root.RefAlias, DumbType.Node, out affectedUpsize);
}
}
sqlEstPreJoinInputSize *= affectedUpsize;
// Cardinality update
Cardinality = matCompSizeWhenJoin *
inPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) *
matUnitSizeWhenJoin *
outPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) *
preJoinSelectivity / root.TableRowCount * postJoinSelectivity;
// Output adjustment
var postJoinUpsizeFactor = -1;
var sqlInPreMatEdgesSelectivity = 1.0;
for (var i = 1; i < inPreMatEdges.Count; ++i)
sqlInPreMatEdgesSelectivity = Math.Sqrt(sqlInPreMatEdgesSelectivity) / 10;
sqlEstPreJoinEdgeSize = Math.Pow(100, LastJoinPostMatEdgesCount) * Math.Pow(1000, inPreMatEdges.Count) *
sqlInPreMatEdgesSelectivity;
var probeSqlEstCardinality = LastJoinSqlEstCardinality * (LastJoinHint == JoinHint.Loop
? LastTable.EstimatedRows / LastTable.TableRowCount
: 1.0)
* sqlEstPreJoinEdgeSize;
var hashJoinSqlSelectivity = outPreMatEdges.Any()
? Math.Pow(Math.Sqrt(0.001), outPreMatEdges.GroupBy(e => e.SinkNode).Count())
: (root.EstimatedRows/root.TableRowCount) / root.EstimatedRows;
var sqlEstHashCardinality = sqlEstPreJoinInputSize*probeSqlEstCardinality*hashJoinSqlSelectivity*
Math.Pow(100, postMatEdges.Count);
estimateFactor = 0;
if (Cardinality >= sqlEstHashCardinality*scaleFactor)
estimateFactor = (int) Math.Ceiling(Cardinality/sqlEstHashCardinality);
else if (Cardinality*scaleFactor < sqlEstHashCardinality)
{
estimateFactor = -1;
adjustedJoincondition = WBooleanBinaryExpression.Conjunction(adjustedJoincondition,
ConstructDownSizeJoinCondition(LastTableAlias));
}
if (estimateFactor >= (int) Math.Ceiling(scaleFactor))
postJoinUpsizeFactor = estimateFactor;
else if (estimateFactor == -1 && LastJoinHint == JoinHint.Loop)
{
probeSqlEstCardinality = LastJoinSqlEstCardinality*
Math.Sqrt(LastTable.EstimatedRows/LastTable.TableRowCount)/
LastTable.TableRowCount * 1.5 * sqlEstPreJoinEdgeSize;
sqlEstHashCardinality = sqlEstPreJoinInputSize*probeSqlEstCardinality*hashJoinSqlSelectivity*
Math.Pow(100, postMatEdges.Count);
if (Cardinality >= sqlEstHashCardinality*scaleFactor)
postJoinUpsizeFactor = (int) Math.Ceiling(Cardinality/sqlEstHashCardinality);
}
foreach (var edge in inPreMatEdges)
TableRef = SpanTableRef(TableRef, edge, edge.SourceNode.RefAlias, LastTableAlias, metaData);
WTableReference table = new WQualifiedJoin
{
FirstTableRef = buildTableReference,
SecondTableRef = TableRef,
JoinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, adjustedJoincondition),
QualifiedJoinType = QualifiedJoinType.Inner,
JoinHint = JoinHint.Hash
};
table = postMatEdges.Aggregate(table, (current, next) => new WUnqualifiedJoin
{
FirstTableRef = current,
SecondTableRef =
next.Item1.ToSchemaObjectFunction(next.Item1.SourceNode.RefAlias,
next.Item2 == EdgeDir.In ? root.RefAlias : LastTableAlias,
metaData),
UnqualifiedJoinType = UnqualifiedJoinType.CrossApply,
});
if (postJoinUpsizeFactor != -1)
{
if (postMatEdges.Any())
table = ConstructUpSizeTableReference(table, postJoinUpsizeFactor,
postMatEdges.Last().Item1.EdgeAlias, DumbType.Edge, out affectedUpsize);
else
table = ConstructUpSizeTableReference(table, postJoinUpsizeFactor, out affectedUpsize);
}
TableRef = new WParenthesisTableReference
{
Table = table,
};
LastJoinHint = JoinHint.Hash;
LastJoinSqlEstCardinality = sqlEstHashCardinality * affectedUpsize;
LastJoinPostMatEdgesCount = 0;
LastPostMatEdgeAlias = null;
SqlEstimatedSize = LastJoinSqlEstCardinality;
SqlEstimatedSize = SqlEstimatedSize < 1.0 ? 1.0 : SqlEstimatedSize;
}
Cost += cost;
// Debug
#if DEBUG
//foreach (var item in MaterializedNodeSplitCount.Where(e => e.Key != node))
//{
// Trace.Write(item.Key.RefAlias + ",");
//}
//Trace.Write(root.RefAlias);
//Trace.Write(" Size:" + Cardinality + " Cost:" + cost);
//Trace.Write(" Method:" + ((TableRef as WParenthesisTableReference).Table as WQualifiedJoin).JoinHint);
//Trace.WriteLine(" --> Total Cost:" + Cost);
#endif
}