I'm trying to improve DB performance. I'v noticed a query that according to the plan it produces a lot of rows but i know for fact there are no more than 400k rows in the table after that insert. Why there is such a big difference between actuall plan and the query result? Query plan is here https://www.brentozar.com/pastetheplan/?id=rJWX327pB
I cannot understand why the query is so complex.
Query text taken from published plan:
with Object1 as ( select Object2.Column1 as Column2 from Schema1.Object3 as Object2 left outer join Schema1.Object4 as Object5 on Object2.Column1 = Object5.Column1 where Object5.Column1 is null ) insert into Schema1.Object4 select * from Schema1.Object3 where Column1 in ( select Column2 from Object1 )
What do you do in CTE? you select records from
Schema1.Object3 which have no according record in
What records are inserted in main query? those which were selected in CTE.
So CTE seems to be excess, and the query may be simplified to
insert into Schema1.Object4 select Schema1.Object3.* from Schema1.Object3 left outer join Schema1.Object4 on Schema1.Object3.Column1 = Schema1.Object4.Column1 where Schema1.Object4.Column1 is null
insert into Schema1.Object4 select Schema1.Object3.* from Schema1.Object3 where not exists ( select 1 from Schema1.Object4 where Schema1.Object3.Column1 = Schema1.Object4.Column1 )
Please check - does I'm right?