Exemplo n.º 1
0
        /// <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));
        }
Exemplo n.º 3
0
 internal static WUnqualifiedJoin GetCrossApplyTableReference(WTableReference firstTableRef, WTableReference secondTableRef)
 {
     return(new WUnqualifiedJoin()
     {
         FirstTableRef = firstTableRef,
         SecondTableRef = secondTableRef,
         UnqualifiedJoinType = UnqualifiedJoinType.CrossApply
     });
 }
Exemplo n.º 4
0
        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));
        }
Exemplo n.º 5
0
 /// <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);
 }
Exemplo n.º 6
0
 /// <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);
 }
Exemplo n.º 7
0
 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
     };
 }
Exemplo n.º 8
0
        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));
        }
Exemplo n.º 9
0
 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;
     }
 }
Exemplo n.º 10
0
        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));
        }
Exemplo n.º 11
0
        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));
        }
Exemplo n.º 12
0
 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);
 }
Exemplo n.º 13
0
        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));
        }
Exemplo n.º 14
0
        /// <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);
        }
Exemplo n.º 15
0
 /// <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;
 }
Exemplo n.º 16
0
        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);
        }
Exemplo n.º 17
0
        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;

            }
        }
Exemplo n.º 18
0
        /// <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);
        }
Exemplo n.º 19
0
        /// <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;
            }
        }
Exemplo n.º 20
0
        /// <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
            });
        }
Exemplo n.º 21
0
 /// <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;
 }
Exemplo n.º 22
0
 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,
                        }
                    },
                }
            };
        }
Exemplo n.º 24
0
        /// <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;
        }
Exemplo n.º 25
0
 /// <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;
     }
 }
Exemplo n.º 26
0
        /// <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,
                }

            };
        }
Exemplo n.º 29
0
 /// <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;
 }
Exemplo n.º 30
0
        /// <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;
            

        }
Exemplo n.º 31
0
        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;
        }
Exemplo n.º 32
0
 public virtual void Visit(WTableReference node)
 {
     node.AcceptChildren(this);
 }
Exemplo n.º 33
0
        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;
 }
Exemplo n.º 35
0
 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
     };
 }
Exemplo n.º 36
0
 public virtual void Visit(WTableReference node)
 {
     node.AcceptChildren(this);
 }
Exemplo n.º 37
0
 /// <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;
 }