SQL Server Performance

Stream Aggregate in an update statement

Discussion in 'ALL SQL SERVER QUESTIONS' started by WingSzeto, Feb 21, 2012.

  1. WingSzeto Member

    I have an update statement which doesn't involve any aggregate function or group-by but the execution plan shows there is a stream aggregate. Somehow the optimizer use a 'group-by' internally. Can someone explain why? I list the update statement and its execution plan below.

    Also, when I looked at the graphical execution plan, the estimated number of rows coming from the aggregate steam is about 290 but when these rows passing through the TOP operator, it becomes 1.4 million estimated rows. Why is that? These million rows are also passing to the clustered index update process as well. Please help me understand this execution plan

    I have two indexes directly addressing the CAT_ID, and ResourceCat_ID and Operator_ID respectively. The total number of records are being updated is 172, one from the first part of the 'OR' and 171 coming from the second part of the 'OR'. If the 'OR' in the query is a problem, please advice if there is a better way to write this query.

    Thanks in advance.




    set Vendor_ID =543634

    where CAT_ID =428980


    in (

    select c.CAT_ID from tblCategory as c with (NOLOCK)
    where c.ResourceCat_ID =428980 and c.Operator_ID >0)
    |--Clustered Index Update(OBJECT:([dbo].[tblCategory].[PK_tblCategory]), SET:([dbo].[tblCategory].[Vendor_ID] = [Expr1005]))
    |--Compute Scalar(DEFINE:([Expr1005]=(543634)))
    |--Top(ROWCOUNT est 0)
    |--Stream Aggregate(GROUP BY:([dbo].[tblCategory].[CAT_ID]))
    |--Merge Join(Concatenation)
    |--Top(TOP EXPRESSION:((1)))
    | |--Clustered Index Seek(OBJECT:([dbo].[tblCategory].[PK_tblCategory]), SEEK:([dbo].[tblCategory].[CAT_ID]=(428980)) ORDERED FORWARD)
    |--Sort(ORDER BY:([dbo].[tblCategory].[CAT_ID] ASC))
    |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[CAT_ID], [Expr1012]) OPTIMIZED WITH UNORDERED PREFETCH)
    |--Index Seek(OBJECT:([dbo].[tblCategory].[IX_tblCategory_ReSourceCat_ID] AS [c]), SEEK:([c].[ReSourceCat_ID]=(428980) AND [c].[Operator_ID] > (0)) ORDERED FORWARD)
    |--Clustered Index Seek(OBJECT:([dbo].[tblCategory].[PK_tblCategory]), SEEK:([dbo].[tblCategory].[CAT_ID]=[dbo].[tblCategory].[CAT_ID] as [c].[CAT_ID]) ORDERED FORWARD)
  2. FrankKalis Moderator

    The Stream Aggregate is a consequence of the IN clause and is used to filter out possible duplicates of tblCategory.CAT_ID. Since you have an index of that column it is a cheap way of achieving this.

Share This Page