/// <summary>
/// Estimates the number of rows of each node table in the graph pattern.
/// </summary>
/// <param name="graph">The graph pattern specified by the MATCH clause</param>
private void EstimateRows(MatchGraph graph)
{
var declareParameter = "";
if (_variables != null)
{
declareParameter = _variables.Aggregate(declareParameter,
(current, parameter) =>
current +
("DECLARE " + parameter.VariableName.Value + " " +
TsqlFragmentToString.DataType(parameter.DataType) + "\r\n"));
}
var estimator = new TableSizeEstimator(Tx);
bool first = true;
// Constructs a union-all query in which each sub-query is a selection of
// a node table alias plus predicates that can be associated with it.
// This query is sent to the SQL DB to get the estimated cardinality of
// each node table alias.
var estimateQuerySb = new StringBuilder(1024);
foreach (var subGraph in graph.ConnectedSubGraphs)
{
foreach (var node in subGraph.Nodes)
{
if (first)
first = false;
else
estimateQuerySb.Append("\r\nUNION ALL\r\n");
var currentNode = node.Value;
estimateQuerySb.AppendFormat("SELECT GlobalNodeId FROM {0} AS [{1}]",
currentNode.NodeTableObjectName,
currentNode.NodeAlias);
if (currentNode.Predicates != null && currentNode.Predicates.Count > 0)
{
estimateQuerySb.AppendFormat("\r\nWHERE {0}", currentNode.Predicates[0]);
for (int i = 1; i < currentNode.Predicates.Count; i++)
{
var predicate = currentNode.Predicates[i];
estimateQuerySb.AppendFormat(" AND {0}", predicate);
}
}
}
}
string nodeEstimationQuery = string.Format("{0}\r\n {1}\r\n", declareParameter,
estimateQuerySb);
// A list of estimated cardinalities
var estimateRows = estimator.GetUnionQueryTableEstimatedRows(nodeEstimationQuery);
int j = 0;
foreach (var subGraph in graph.ConnectedSubGraphs)
{
// Update Row Estimation for nodes
foreach (var node in subGraph.Nodes)
{
var currentNode = node.Value;
var tableSchema = currentNode.NodeTableObjectName.SchemaIdentifier.Value;
var tableName = currentNode.NodeTableObjectName.BaseIdentifier.Value;
var tableTuple = WNamedTableReference.SchemaNameToTuple(currentNode.NodeTableObjectName);
if (_graphMetaData.NodeViewMapping.ContainsKey(tableTuple))
{
var nodeSet = _graphMetaData.NodeViewMapping[tableTuple];
int n = nodeSet.Count;
double nodeViewEstRows = 0.0;
currentNode.TableRowCount = nodeSet.Aggregate(0,
(cur, next) => cur + estimator.GetTableRowCount(tableSchema, next));
bool bug = false;
while (n > 0)
{
n--;
if (j > estimateRows.Count - 1)
{
bug = true;
break;
}
nodeViewEstRows += estimateRows[j];
j++;
}
currentNode.EstimatedRows = bug ? currentNode.TableRowCount : nodeViewEstRows;
}
else
{
currentNode.TableRowCount = estimator.GetTableRowCount(tableSchema, tableName);
if (j > estimateRows.Count - 1)
currentNode.EstimatedRows = currentNode.TableRowCount;
else
currentNode.EstimatedRows = estimateRows[j];
j++;
}
}
}
}
/// <summary>
/// Estimate number of rows of node table in graph.
/// </summary>
/// <param name="graph">Constructed node graph</param>
private void EstimateRows(WSelectQueryBlock query, MatchGraph graph)
{
var declareParameter = "";
if (_variables != null)
{
foreach (var parameter in _variables)
{
declareParameter += "DECLARE " + parameter.VariableName.Value + " " +
TsqlFragmentToString.DataType(parameter.DataType) + "\r\n";
}
}
var estimator = new TableSizeEstimator(Conn);
bool first = true;
var fromQuerySb = new StringBuilder(1024);
foreach (var subGraph in graph.ConnectedSubGraphs)
{
foreach (var node in subGraph.Nodes)
{
if (first)
first = false;
else
fromQuerySb.Append(", ");
var currentNode = node.Value;
fromQuerySb.AppendFormat("{0} AS [{1}] WITH (ForceScan)", currentNode.TableObjectName,
currentNode.NodeAlias);
}
}
// Attach proper parts of the where clause into the Estimiation Query
var columnTableMapping = _context.GetColumnTableMapping(_columnsOfNodeTables);
var attachWhereClauseVisiter = new AttachNodeEdgePredictesVisitor();
var nodeEstimationWhereClause = attachWhereClauseVisiter.Invoke(query.WhereClause, graph, columnTableMapping);
string nodeEstimationQuery = string.Format("{0}\r\n SELECT * FROM {1}\r\n", declareParameter,
fromQuerySb);
if (nodeEstimationWhereClause.SearchCondition != null)
nodeEstimationQuery += nodeEstimationWhereClause.ToString();
var estimateRows = estimator.GetQueryTableEstimatedRows(nodeEstimationQuery);
foreach (var subGraph in graph.ConnectedSubGraphs)
{
// Update Row Estimation for nodes
foreach (var node in subGraph.Nodes)
{
var currentNode = node.Value;
currentNode.EstimatedRows = estimateRows[node.Key];
var tableSchema = currentNode.TableObjectName.SchemaIdentifier.Value;
var tableName = currentNode.TableObjectName.BaseIdentifier.Value;
currentNode.TableRowCount = estimator.GetTableRowCount(tableSchema, tableName);
}
}
// Attach predicates to nodes and edges
var attachPredicateVisitor = new AttachWhereClauseVisitor();
attachPredicateVisitor.Invoke(query.WhereClause, graph, columnTableMapping);
}