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

  • Could you please paste your plan here --> brentozar.com/pastetheplan Dec 3 '19 at 10:41
  • @Learning_DBAdmin updated my post with link to my plan
    – whd
    Dec 3 '19 at 10:43
  • Is it possible to include estimated as well as actual plan? Dec 3 '19 at 10:48
  • 2
    The query is selecting data from views and a lot of needed information is not in the anonimized estimated plan. Things like the view definitions, table DDL, the function definitions are also needed as well as the actual execution plan. As much information as possible would be best here. Dec 3 '19 at 11:32
  • @RandiVertongen currently i can't add all needed data as vpn went down.
    – whd
    Dec 3 '19 at 11:39

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 )

Let's think.

What do you do in CTE? you select records from Schema1.Object3 which have no according record in Schema1.Object4.

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?

  • As this is the only answer i will accept it. I think i have found the root cause of this problem and i will ask another question
    – whd
    Dec 3 '19 at 23:32
  • @whd Have you checked the queries listed in my answer? Does they are correct?
    – Akina
    Dec 4 '19 at 4:57
  • yes i have checked, and results are the same
    – whd
    Dec 4 '19 at 8:12
  • @whd results are the same Ok, thanks.
    – Akina
    Dec 4 '19 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.