Example #1
0
        private WTableReference ParseTableReference(TableReference tabRef)
        {
            if (tabRef == null)
            {
                return null;
            }
            var tabRefWithAlias = tabRef as TableReferenceWithAlias;
            if (tabRefWithAlias!=null && tabRefWithAlias.Alias!=null &&
                 GraphViewKeywords._keywords.Contains(tabRefWithAlias.Alias.Value))
            {
                var token = _tokens[tabRefWithAlias.Alias.FirstTokenIndex];
                throw new SyntaxErrorException(token.Line, tabRefWithAlias.Alias.Value,
                    "System restricted Name cannot be used");
            }
            switch (tabRef.GetType().Name)
            {
                case "NamedTableReference":
                    {
                        var oref = tabRef as NamedTableReference;
                        if (oref.SchemaObject.BaseIdentifier.QuoteType == QuoteType.NotQuoted &&
                            (oref.SchemaObject.BaseIdentifier.Value[0] == '@' ||
                             oref.SchemaObject.BaseIdentifier.Value[0] == '#'))
                        {
                            var pref = new WSpecialNamedTableReference
                            {
                                Alias = oref.Alias,
                                TableHints = new List<WTableHint>(),
                                FirstTokenIndex = oref.FirstTokenIndex,
                                LastTokenIndex = oref.LastTokenIndex,
                                TableObjectName = ParseSchemaObjectName(oref.SchemaObject),
                            };

                            if (oref.TableHints != null)
                            {
                                foreach (var hint in oref.TableHints)
                                    pref.TableHints.Add(ParseTableHint(hint));
                            }

                            return pref;
                        }
                        else
                        {
                            var pref = new WNamedTableReference
                            {
                                Alias = oref.Alias,
                                TableHints = new List<WTableHint>(),
                                FirstTokenIndex = oref.FirstTokenIndex,
                                LastTokenIndex = oref.LastTokenIndex,
                                TableObjectName = ParseSchemaObjectName(oref.SchemaObject),
                            };

                            if (oref.TableHints != null)
                            {
                                foreach (var hint in oref.TableHints)
                                    pref.TableHints.Add(ParseTableHint(hint));
                            }

                            return pref;
                        }
                    }
                case "QueryDerivedTable":
                    {
                        var oref = tabRef as QueryDerivedTable;
                        var pref = new WQueryDerivedTable
                        {
                            QueryExpr = ParseSelectQueryStatement(oref.QueryExpression),
                            Alias = oref.Alias,
                            Columns = oref.Columns,
                            FirstTokenIndex = oref.FirstTokenIndex,
                            LastTokenIndex = oref.LastTokenIndex,
                        };

                        return pref;
                    }
                case "SchemaObjectFunctionTableReference":
                    {
                        var oref = tabRef as SchemaObjectFunctionTableReference;
                        var pref = new WSchemaObjectFunctionTableReference
                        {
                            Alias = oref.Alias,
                            Columns = oref.Columns,
                            SchemaObject = ParseSchemaObjectName(oref.SchemaObject),
                            FirstTokenIndex = oref.FirstTokenIndex,
                            LastTokenIndex = oref.LastTokenIndex
                        };
                        if (oref.Parameters == null)
                            return pref;
                        pref.Parameters = new List<WScalarExpression>();
                        foreach (var param in oref.Parameters)
                            pref.Parameters.Add(ParseScalarExpression(param));
                        return pref;
                    }
                case "QualifiedJoin":
                    {
                        var oref = tabRef as QualifiedJoin;
                        var pref = new WQualifiedJoin
                        {
                            FirstTableRef = ParseTableReference(oref.FirstTableReference),
                            SecondTableRef = ParseTableReference(oref.SecondTableReference),
                            QualifiedJoinType = oref.QualifiedJoinType,
                            JoinHint = oref.JoinHint,
                            JoinCondition = ParseBooleanExpression(oref.SearchCondition),
                            FirstTokenIndex = oref.FirstTokenIndex,
                            LastTokenIndex = oref.LastTokenIndex,
                        };

                        return pref;
                    }
                case "UnqualifiedJoin":
                    {
                        var oref = tabRef as UnqualifiedJoin;
                        var pref = new WUnqualifiedJoin
                        {
                            FirstTableRef = ParseTableReference(oref.FirstTableReference),
                            SecondTableRef = ParseTableReference(oref.SecondTableReference),
                            UnqualifiedJoinType = oref.UnqualifiedJoinType,
                            FirstTokenIndex = oref.FirstTokenIndex,
                            LastTokenIndex = oref.LastTokenIndex,
                        };
                        return pref;
                    }
                case "JoinParenthesisTableReference":
                    {
                        var ptab = tabRef as JoinParenthesisTableReference;

                        var wptab = new WParenthesisTableReference
                        {
                            Table = ParseTableReference(ptab.Join),
                            FirstTokenIndex = ptab.FirstTokenIndex,
                            LastTokenIndex = ptab.LastTokenIndex,
                        };

                        return wptab;
                    }
                default:
                    return null;
            }
        }
 public virtual void Visit(WQualifiedJoin node)
 {
     node.AcceptChildren(this);
 }
 public virtual void Visit(WQualifiedJoin node)
 {
     node.AcceptChildren(this);
 }
Example #4
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
            });
        }
Example #5
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;
            }
        }
Example #6
0
        /// <summary>
        /// Calculate join costs and update components using optimal join method & order
        /// </summary>
        /// <param name="nodeUnitCandidate"></param>
        /// <param name="joinCondition"></param>
        /// <param name="preJoinSelectivity"></param>
        /// <param name="postJoinSelectivity"></param>
        /// <param name="estimatedSelectivity"></param>
        /// <param name="metaData"></param>
        /// <param name="isExecutable"></param>
        private void ConstructPhysicalJoinAndUpdateCost(
            CandidateJoinUnit nodeUnitCandidate,
            WBooleanExpression joinCondition,
            double preJoinSelectivity,
            double postJoinSelectivity,
            double estimatedSelectivity,
            GraphMetaData metaData)
        {
            const double scaleFactor = 1.5;
            const int sqlInPreMatEdgeSelectivityThreshold = 5;
            var firstJoin = MaterializedNodeSplitCount.Count == 2;

            var inPreMatEdges = nodeUnitCandidate.PreMatIncomingEdges;
            var inPostMatEdges = nodeUnitCandidate.PostMatIncomingEdges;
            var outPreMatEdges = nodeUnitCandidate.PreMatOutgoingEdges;
            var outPostMatEdges = nodeUnitCandidate.PostMatOutgoingEdges;
            var postMatEdges = inPostMatEdges.Select(e => new Tuple<MatchEdge, EdgeDir>(e, EdgeDir.In))
                               .Union(
                                   outPostMatEdges.Select(e => new Tuple<MatchEdge, EdgeDir>(e, EdgeDir.Out)))
                               .OrderBy(t => t.Item1.AverageDegree)
                               .ToList();

            var compDegrees = inPreMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur*next);
            var nodeDegrees = outPreMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next);

            var root = nodeUnitCandidate.TreeRoot;
            var componentSize = Cardinality;

            double sizeFactor = 5; // 1000;
            var loopCost = nodeUnitCandidate.JoinHint == JoinHint.Loop
                ? componentSize * compDegrees * Math.Log(root.EstimatedRows, 512)
                : double.MaxValue;
            // only calc the size of table used to join
            var matCompSizeWhenJoin = componentSize*compDegrees;
            var matUnitSizeWhenJoin = root.EstimatedRows*nodeDegrees;
            var hashCost = matCompSizeWhenJoin + matUnitSizeWhenJoin;

            double loopJoinOuterThreshold = 1e4;//1e6;
            double maxMemory = 1e8;
            double cost;

            // loop join
            if (nodeUnitCandidate.JoinHint == JoinHint.Loop
                    //inPreMatEdges.Any() && !outPreMatEdges.Any() &&
                    //(
                    //    //componentSize < loopJoinOuterThreshold ||     // the outer table is relatively small
                    //    loopCost < hashCost ||
                    //    (DeltaMemory + matCompSizeWhenJoin > maxMemory && DeltaMemory + matUnitSizeWhenJoin > maxMemory)
                    //    // memory is in pressure
                    //)
                )
            {
                if (firstJoin)
                {
                    RightestTableRefSize = nodeUnitCandidate.TreeRoot.EstimatedRows;
                    RightestTableAlias = root.RefAlias;
                    LastTable = Nodes.First(n => n.NodeAlias != root.NodeAlias);
                    //LastTableAlias = LastTable.RefAlias;
                    LastJoinHint = JoinHint.Loop;
                    LastJoinSqlEstCardinality = LastTable.TableRowCount;
                }

                cost = loopCost;

                var sqlInPreMatEdgesSelectivity = 1.0;
                for (var i = 1; i < inPreMatEdges.Count && i <= sqlInPreMatEdgeSelectivityThreshold; ++i)
                    sqlInPreMatEdgesSelectivity = Math.Sqrt(sqlInPreMatEdgesSelectivity)/10;

                var sqlEstPreJoinEdgeSize = Math.Pow(100, LastJoinPostMatEdgesCount)*Math.Pow(1000, inPreMatEdges.Count)*
                                            sqlInPreMatEdgesSelectivity;
                var sqlEstPreJoinInputSize = LastJoinSqlEstCardinality * (LastJoinHint == JoinHint.Loop
                                             ? LastTable.EstimatedRows / LastTable.TableRowCount
                                             : 1.0) *
                                             sqlEstPreJoinEdgeSize;

                var estimateFactor = 0;
                if (matCompSizeWhenJoin >= sqlEstPreJoinInputSize * scaleFactor)
                    estimateFactor = (int) Math.Ceiling(matCompSizeWhenJoin/sqlEstPreJoinInputSize);
                else if (matCompSizeWhenJoin*scaleFactor < sqlEstPreJoinInputSize)
                    estimateFactor = -1;

                var affectedUpsize = 1.0;
                if (estimateFactor >= (int) Math.Ceiling(scaleFactor))
                {
                    if (LastJoinHint == JoinHint.Loop)
                        TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, LastTableAlias, DumbType.Node, out affectedUpsize);
                    else if (LastJoinPostMatEdgesCount > 0)
                        TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, LastPostMatEdgeAlias, DumbType.Edge, out affectedUpsize);
                    else
                        TableRef = ConstructUpSizeTableReference(TableRef, estimateFactor, out affectedUpsize);
                }
                else if (estimateFactor == -1 && LastJoinHint == JoinHint.Loop)
                {
                    sqlEstPreJoinInputSize = LastJoinSqlEstCardinality*
                                             Math.Sqrt(LastTable.EstimatedRows/LastTable.TableRowCount)/
                                             LastTable.TableRowCount * 1.5 * sqlEstPreJoinEdgeSize;

                    if (matCompSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor)
                        TableRef = ConstructUpSizeTableReference(TableRef, (int) Math.Ceiling(matCompSizeWhenJoin/sqlEstPreJoinInputSize),
                            LastTableAlias, DumbType.Node, out affectedUpsize);
                }

                foreach (var edge in inPreMatEdges)
                    TableRef = SpanTableRef(TableRef, edge, edge.SourceNode.RefAlias, LastTableAlias, metaData);

                WTableReference table = new WQualifiedJoin
                {
                    FirstTableRef = TableRef,
                    SecondTableRef =
                        nodeUnitCandidate.ToTableReference(root.RefAlias, root.RefAlias, metaData),
                    JoinCondition = estimateFactor == -1
                                    ? WBooleanBinaryExpression.Conjunction(joinCondition, ConstructDownSizeJoinCondition(LastTableAlias))
                                    : joinCondition,
                    QualifiedJoinType = QualifiedJoinType.Inner,
                    JoinHint = JoinHint.Loop
                };

                table = postMatEdges.Aggregate(table, (current, next) => new WUnqualifiedJoin
                {
                    FirstTableRef = current,
                    SecondTableRef =
                        next.Item1.ToSchemaObjectFunction(next.Item1.SourceNode.RefAlias,
                                                          next.Item2 == EdgeDir.In ? root.RefAlias : LastTableAlias,
                                                          metaData),
                    UnqualifiedJoinType = UnqualifiedJoinType.CrossApply,
                });

                TableRef = new WParenthesisTableReference
                {
                    Table = table,
                };

                LastTable = root;
                LastTableAlias = root.RefAlias;
                LastJoinHint = JoinHint.Loop;
                LastJoinSqlEstCardinality = sqlEstPreJoinInputSize*affectedUpsize;
                LastJoinPostMatEdgesCount = postMatEdges.Count;
                LastPostMatEdgeAlias = LastJoinPostMatEdgesCount > 0 ? postMatEdges.Last().Item1.EdgeAlias : null;
                SqlEstimatedSize = sqlEstPreJoinInputSize*root.EstimatedRows/root.TableRowCount*
                                   Math.Pow(100, postMatEdges.Count);
                SqlEstimatedSize = SqlEstimatedSize < 1.0 ? 1.0 : SqlEstimatedSize;

                Cardinality = matCompSizeWhenJoin *
                                inPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) *
                              matUnitSizeWhenJoin *
                                outPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) *
                              preJoinSelectivity / root.TableRowCount * postJoinSelectivity;
            }
            // hash join
            else
            {
                cost = hashCost;
                WBooleanExpression adjustedJoincondition = null;
                WTableReference buildTableReference = nodeUnitCandidate.ToTableReference(root.RefAlias, root.RefAlias, metaData);
                double affectedUpsize = 1.0, sqlEstPreJoinEdgeSize;
                int estimateFactor;
                var sqlEstPreJoinInputSize = root.EstimatedRows;

                if (firstJoin)
                {
                    LastTable = Nodes.First(n => n.NodeAlias != root.NodeAlias);
                    //LastTableAlias = LastTable.RefAlias;
                    LastJoinHint = JoinHint.Loop;
                    LastJoinSqlEstCardinality = LastTable.TableRowCount;
                }

                // Build table adjustment
                if (outPreMatEdges.Any())
                {
                    var sqlOutPreMatEdgesSelectivity = 1.0;
                    foreach (var group in outPreMatEdges.GroupBy(e => e.SinkNode))
                    {
                        var selectivity = 1.0;
                        for (var i = 1; i < group.Count() && i <= sqlInPreMatEdgeSelectivityThreshold; ++i)
                            selectivity = Math.Sqrt(selectivity) / 10;
                        sqlOutPreMatEdgesSelectivity *= selectivity;
                    }

                    sqlEstPreJoinEdgeSize = Math.Pow(1000, outPreMatEdges.Count) * sqlOutPreMatEdgesSelectivity;
                    sqlEstPreJoinInputSize *= sqlEstPreJoinEdgeSize;

                    estimateFactor = 0;
                    if (matUnitSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor)
                        estimateFactor = (int) Math.Ceiling(matUnitSizeWhenJoin/sqlEstPreJoinInputSize);
                    else if (matUnitSizeWhenJoin*scaleFactor < sqlEstPreJoinInputSize)
                    {
                        estimateFactor = -1;
                        adjustedJoincondition = ConstructDownSizeJoinCondition(root.RefAlias);
                    }

                    if (estimateFactor >= (int) Math.Ceiling(scaleFactor))
                        buildTableReference = ConstructUpSizeTableReference(buildTableReference, estimateFactor,
                            root.RefAlias, DumbType.Node, out affectedUpsize);
                    else if (estimateFactor == -1)
                    {
                        sqlEstPreJoinInputSize = Math.Sqrt(root.EstimatedRows/root.TableRowCount)*1.5*sqlEstPreJoinEdgeSize;

                        if (matUnitSizeWhenJoin >= sqlEstPreJoinInputSize*scaleFactor)
                            buildTableReference = ConstructUpSizeTableReference(buildTableReference,
                                (int)Math.Ceiling(matUnitSizeWhenJoin / sqlEstPreJoinInputSize),
                                root.RefAlias, DumbType.Node, out affectedUpsize);
                    }
                }
                sqlEstPreJoinInputSize *= affectedUpsize;

                // Cardinality update
                Cardinality = matCompSizeWhenJoin *
                                inPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) *
                              matUnitSizeWhenJoin *
                                outPostMatEdges.Select(e => e.AverageDegree).Aggregate(1.0, (cur, next) => cur * next) *
                              preJoinSelectivity / root.TableRowCount * postJoinSelectivity;

                // Output adjustment
                var postJoinUpsizeFactor = -1;

                var sqlInPreMatEdgesSelectivity = 1.0;
                for (var i = 1; i < inPreMatEdges.Count; ++i)
                    sqlInPreMatEdgesSelectivity = Math.Sqrt(sqlInPreMatEdgesSelectivity) / 10;

                sqlEstPreJoinEdgeSize = Math.Pow(100, LastJoinPostMatEdgesCount) * Math.Pow(1000, inPreMatEdges.Count) *
                                            sqlInPreMatEdgesSelectivity;
                var probeSqlEstCardinality = LastJoinSqlEstCardinality * (LastJoinHint == JoinHint.Loop
                                             ? LastTable.EstimatedRows / LastTable.TableRowCount
                                             : 1.0)
                                             * sqlEstPreJoinEdgeSize;

                var hashJoinSqlSelectivity = outPreMatEdges.Any()
                    ? Math.Pow(Math.Sqrt(0.001), outPreMatEdges.GroupBy(e => e.SinkNode).Count())
                    : (root.EstimatedRows/root.TableRowCount) / root.EstimatedRows;

                var sqlEstHashCardinality = sqlEstPreJoinInputSize*probeSqlEstCardinality*hashJoinSqlSelectivity*
                                            Math.Pow(100, postMatEdges.Count);

                estimateFactor = 0;
                if (Cardinality >= sqlEstHashCardinality*scaleFactor)
                    estimateFactor = (int) Math.Ceiling(Cardinality/sqlEstHashCardinality);
                else if (Cardinality*scaleFactor < sqlEstHashCardinality)
                {
                    estimateFactor = -1;
                    adjustedJoincondition = WBooleanBinaryExpression.Conjunction(adjustedJoincondition,
                        ConstructDownSizeJoinCondition(LastTableAlias));
                }

                if (estimateFactor >= (int) Math.Ceiling(scaleFactor))
                    postJoinUpsizeFactor = estimateFactor;
                else if (estimateFactor == -1 && LastJoinHint == JoinHint.Loop)
                {
                    probeSqlEstCardinality = LastJoinSqlEstCardinality*
                                             Math.Sqrt(LastTable.EstimatedRows/LastTable.TableRowCount)/
                                             LastTable.TableRowCount * 1.5 * sqlEstPreJoinEdgeSize;
                    sqlEstHashCardinality = sqlEstPreJoinInputSize*probeSqlEstCardinality*hashJoinSqlSelectivity*
                                            Math.Pow(100, postMatEdges.Count);

                    if (Cardinality >= sqlEstHashCardinality*scaleFactor)
                        postJoinUpsizeFactor = (int) Math.Ceiling(Cardinality/sqlEstHashCardinality);
                }

                foreach (var edge in inPreMatEdges)
                    TableRef = SpanTableRef(TableRef, edge, edge.SourceNode.RefAlias, LastTableAlias, metaData);

                WTableReference table = new WQualifiedJoin
                {
                    FirstTableRef = buildTableReference,
                    SecondTableRef = TableRef,
                    JoinCondition = WBooleanBinaryExpression.Conjunction(joinCondition, adjustedJoincondition),
                    QualifiedJoinType = QualifiedJoinType.Inner,
                    JoinHint = JoinHint.Hash
                };

                table = postMatEdges.Aggregate(table, (current, next) => new WUnqualifiedJoin
                {
                    FirstTableRef = current,
                    SecondTableRef =
                        next.Item1.ToSchemaObjectFunction(next.Item1.SourceNode.RefAlias,
                                                          next.Item2 == EdgeDir.In ? root.RefAlias : LastTableAlias,
                                                          metaData),
                    UnqualifiedJoinType = UnqualifiedJoinType.CrossApply,
                });

                if (postJoinUpsizeFactor != -1)
                {
                    if (postMatEdges.Any())
                        table = ConstructUpSizeTableReference(table, postJoinUpsizeFactor,
                            postMatEdges.Last().Item1.EdgeAlias, DumbType.Edge, out affectedUpsize);
                    else
                        table = ConstructUpSizeTableReference(table, postJoinUpsizeFactor, out affectedUpsize);
                }

                TableRef = new WParenthesisTableReference
                {
                    Table = table,
                };

                LastJoinHint = JoinHint.Hash;
                LastJoinSqlEstCardinality = sqlEstHashCardinality * affectedUpsize;
                LastJoinPostMatEdgesCount = 0;
                LastPostMatEdgeAlias = null;
                SqlEstimatedSize = LastJoinSqlEstCardinality;
                SqlEstimatedSize = SqlEstimatedSize < 1.0 ? 1.0 : SqlEstimatedSize;
            }

            Cost += cost;

            // Debug
            #if DEBUG
            //foreach (var item in MaterializedNodeSplitCount.Where(e => e.Key != node))
            //{
            //    Trace.Write(item.Key.RefAlias + ",");
            //}
            //Trace.Write(root.RefAlias);
            //Trace.Write(" Size:" + Cardinality + " Cost:" + cost);
            //Trace.Write(" Method:" + ((TableRef as WParenthesisTableReference).Table as WQualifiedJoin).JoinHint);
            //Trace.WriteLine(" --> Total Cost:" + Cost);
            #endif
        }
Example #7
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
            };
        }
Example #8
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;
     }
 }