asked
Stream Aggregate Operator
Just Trying to understand about this operator and it is consuming 40 percent of the cost when looking at the excution plan. Here is the replica of the query that I am using: select
Final_res.id, (select id_desc from lookup_table where id =
Final_res.id ) as Desc ( select * from (select * from (select *, rownumber as rn from table1 ) where rn=1) R1 inner join (select * from (select *, rownumber as rn from table2 ) where rn=1) R2 on
R1.id=
R2.id) Final_res Actual execution plan says there is stream aggregate on top of lookup_table (table scan) What is this operator and is there a way we can get rid of it?
There's a useful article on the [Stream Aggregate showplan operator on Simple-Talk][1]. As for "how can I get rid of it"? It's difficult to say without seeing your full query and plan; but generally that would involve getting rid of any aggregate functions ('count', 'avg', 'sum' etc - anything that could involve a 'GROUP BY' clause - including some JOINs and DISTINCT) [1]:
https://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week---stream-aggregate/
Thanks for your response. What I understood from that article is :- 1. it is used to calculate the aggregate values 2. input must be ordered for this My stored procedure logic is to match 2 tables having same schema, number of columns and datatypes and then find out whether the values of certain columns have changed based on some primary columns Here is the my query and the screenshot from query plan, my question why we it is showing SA operator on top of EMPLOYEE_DIM_V table when I am not using any aggregate function? :- --Final Dataset to be returned to the report. SELECT DISTINCT Result_ids.Status, Result_ids.Old_Channel ,( SELECT CHNL.ORG_CHANNEL_DESC FROM BISTAGE.DBO.ORG_CHANNEL_DIM_V CHNL WHERE CHNL.ORG_CHANNEL_KEY = Result_ids.Old_Channel ) AS OrganisationChannelDesc ,Result_ids.New_Channel ,( SELECT CHNL.ORG_CHANNEL_DESC FROM BISTAGE.DBO.ORG_CHANNEL_DIM_V CHNL WHERE CHNL.ORG_CHANNEL_KEY = Result_ids.New_Channel ) AS OrganisationChannelDesc ,Result_ids.Old_Region ,( SELECT REG.ORG_REGION_DESC FROM BISTAGE.DBO.ORG_REGION_DIM_V REG WHERE REG.ORG_REGION_KEY = Result_ids.Old_Region ) AS OrganisationDivisionDesc ,Result_ids.Old_Region ,( SELECT REG.ORG_REGION_DESC FROM BISTAGE.DBO.ORG_REGION_DIM_V REG WHERE REG.ORG_REGION_KEY = Result_ids.Old_Region ) AS OrganisationDivisionDesc ,Result_ids.New_Region ,( SELECT REG.ORG_REGION_DESC FROM BISTAGE.DBO.ORG_REGION_DIM_V REG WHERE REG.ORG_REGION_KEY = Result_ids.New_Region ) AS OrganisationDivisionDesc ,Result_ids.Old_Area ,( SELECT AREA.ORG_AREA_DESC FROM BISTAGE.DBO.ORG_AREA_DIM_V AREA WHERE AREA.ORG_AREA_KEY = Result_ids.Old_Area ) AS OrganisationAreaDesc ,Result_ids.New_Area ,( SELECT AREA.ORG_AREA_DESC FROM BISTAGE.DBO.ORG_AREA_DIM_V AREA WHERE AREA.ORG_AREA_KEY = Result_ids.New_Area ) AS OrganisationAreaDesc ,Result_ids.Old_Team ,( SELECT TEAM.ORG_TEAM_DESC FROM BISTAGE.DBO.ORG_TEAM_DIM_V TEAM WHERE TEAM.ORG_TEAM_KEY = Result_ids.Old_Team ) AS OrganisationTeamDesc ,Result_ids.New_Team ,( SELECT TEAM.ORG_TEAM_DESC FROM BISTAGE.DBO.ORG_TEAM_DIM_V TEAM WHERE TEAM.ORG_TEAM_KEY = Result_ids.New_Team ) AS OrganisationTeamDesc ,Result_ids.Old_AM ,( SELECT EMPLOYEE_NAME FROM BISTAGE.DBO.EMPLOYEE_DIM_V EMP WHERE EMP.EMPLOYEE = Result_ids.Old_AM ) AS SalesConsultantDesc ,Result_ids.New_AM ,( SELECT EMPLOYEE_NAME FROM BISTAGE.DBO.EMPLOYEE_DIM_V EMP WHERE EMP.EMPLOYEE = Result_ids.New_AM ) AS SalesConsultantDesc ,Result_ids.sold_to_customer_key ,( SELECT AdvertiserDesc FROM CUSTOMERMEASUREMENTTOOL.dbo.advertiser A WHERE A.AdvertiserId = Result_ids.sold_to_customer_key ) AS AdvertiserDesc, Result_ids.line_item, Result_ids.sales_doc_no, Result_ids.received_date_key, Result_ids.seq_no FROM (select present.line_item, present.sales_doc_no, present.received_date_key, present.seq_no, case when ((history.tp_employee_sid = 0 or history.tp_employee_sid is null) AND (present.tp_employee_sid 0 or present.tp_employee_sid is not null) ) THEN ''ADDED'' when ((history.tp_employee_sid 0 or history.tp_employee_sid is NOT null) AND (present.tp_employee_sid = 0 or present.tp_employee_sid is null) ) THEN ''DELETED'' when (history.tp_employee_sid present.tp_employee_sid ) THEN ''AMENDED'' end as Status, present.sold_to_customer_key, case when (history.tp_employee_sid present.tp_employee_sid ) then history.tp_employee_sid else 0 end as Old_AM, case when (history.tp_employee_sid present.tp_employee_sid ) then present.tp_employee_sid else 0 end as New_AM, --TEAM-- case when (history.tp_employee_sid present.tp_employee_sid ) then history.tp_ORG_TEAM_KEY else 0 end as Old_Team, case when (history.tp_employee_sid present.tp_employee_sid ) then present.tp_ORG_TEAM_KEY else 0 end as New_Team, --AREA-- case when (history.tp_employee_sid present.tp_employee_sid ) then history.tp_ORG_AREA_KEY else 0 end as Old_Area, case when (history.tp_employee_sid present.tp_employee_sid ) then present.tp_ORG_AREA_KEY else 0 end as New_Area, --REGION-- case when (history.tp_employee_sid present.tp_employee_sid ) then history.tp_ORG_REGION_KEY else 0 end as Old_Region, case when (history.tp_employee_sid present.tp_employee_sid ) then present.tp_ORG_REGION_KEY else 0 end as New_Region, --CHANNEL-- case when (history.tp_employee_sid present.tp_employee_sid ) then history.tp_ORG_CHANNEL_KEY else 0 end as Old_Channel, case when (history.tp_employee_sid present.tp_employee_sid ) then present.tp_ORG_CHANNEL_KEY else 0 end as New_Channel from (select * from ( select line_item,sales_doc_no,received_date_key,seq_no, sold_to_customer_key,tp_employee_sid,TP_ORG_TEAM_KEY, tp_org_area_key,tp_org_region_key, tp_org_channel_key , row_number() over (partition by line_item,sales_doc_no,received_date_key,seq_no order by received_date_key desc) as rn from multi_market_fact where received_date_key > '+Cast(@StartDateKey as varchar(10))+' ) temp_result where rn=1) present inner join (select * from ( select line_item,sales_doc_no,received_date_key,seq_no, sold_to_customer_key,tp_employee_sid,TP_ORG_TEAM_KEY, tp_org_area_key,tp_org_region_key, tp_org_channel_key , row_number() over (partition by line_item,sales_doc_no,received_date_key,seq_no order by received_date_key desc) as rn from '+ @DbName1+' where received_date_key > '+ Cast(@StartDateKey as varchar(10)) +' ) temp_result where rn=1) history on present.line_item=history.line_item and history.sales_doc_no=present.sales_doc_no and present.received_date_key=history.received_date_key and present.seq_no=history.seq_no and present.sold_to_customer_key=history.sold_to_customer_key where history.tp_employee_sid != present.tp_employee_sid) Result_ids
Actually, the query is using DISTINCT. That's going to cause the aggregation operations. I agree, the subqueries need to be put into JOIN statements instead.
No, you're not explicitly doing anything that would warrant use of an aggregate function, but the various subqueries are probably being optimised into various JOIN statements which may well result in aggregates being used internally. Hence the request for the *actual* query plan - on the query plan window shown, right click and "save query plan". This will generate a file that you can upload.
screenshot from query plan isn't visible (to me), and isn't helpful, as the interesting data is usually hidden. Can you save the query plan and upload / attach it to your answer?
Hi @Grant and @ThomasRushton, I confirm that it was due to subqueries that stream aggregate was coming into picture. question -- going by the definition of SA operator what can the reason of aggregates being used internally?
17 People are following this question.
