Proc is running slow with NOT EXISTS

Question

I'm working on trying to create a stored procedure however I'm running into a issue where the stored procedure runs for over 5 minutes due to close to 50k records.

The process seems pretty straight forward, I'm just not sure why it is taking so long.

Essentially I have two tables:

Table_1

ApptDate      ApptName          ApptDoc          ApptReason   ApptType
-----------------------------------------------------------------------
03/15/2021    Physical          Dr Smith         Yearly       Day
03/15/2021    Check In          Dr Doe           Check In     Day
03/15/2021    Appt oth          Dr Dee           Check In     Monthly

Table_2 - this table has the same exact structure as Table_1, what I am trying to achieve is simply archive the the data from Table_1

DECLARE @Date_1 as DATETIME 
SET @Date_1 = GetDate() - 1

INSERT INTO Table_2 (ApptDate, ApptName, ApptDoc, ApptReason)
    SELECT ApptDate, ApptName, ApptDoc, ApptReason
    FROM Table_1
    WHERE ApptType = 'Day' AND ApptDate = @Date_1
      AND NOT EXISTS (SELECT 1 FROM Table_2 
                      WHERE AppType = 'Day' AND ApptDate = @Date_1)

So this stored procedure seems pretty straight forward, however the NOT EXIST is causing it to be really slow.

The reason for NOT EXIST, is that this stored procedure is part of a bigger process that runs multiple times a day (morning, afternoon, night). I'm trying to make sure that I only have 1 copy of the the '03/15/2021' data. I'm basically running an archive process on previous days data (@Date_1)

Any thoughts how this can be "sped up".

Answer 1

For this query:

INSERT INTO Table_2 (ApptDate, ApptName, ApptDoc, ApptReason)
    SELECT ApptDate, ApptName, ApptDoc, ApptReason
    from Table_1 t1
    Where ApptType = 'Day' and
          ApptDate = @Date_1 and
          NOT EXISTS (Select 1
                      from Table_2 t2
                      where t2.AppType = t1.AppType and
                            t2.ApptDate = t1.ApptDate
                     );

You want indexes on: table_1(ApptType) and more importantly, Table_2(AppType, ApptDate) or Table_2(ApptDate, AppType).

Note: I changed the correlation clause to just refer to the values in the outer query. This seems more general than your version, but should have the same performance (in this case).