Stream Aggregate in an update statement | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Stream Aggregate in an update statement

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. W update tblCategory set Vendor_ID =543634 where CAT_ID =428980 OR CAT_ID
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)
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.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |