I created the below stored procedure to pull data from a source table and insert only "new records" into a destination table. I need to run this every minute. The challenge is that the table stores "images" with over 2 million rows.

When I run the stored procedure it is still running over 1 hour and 22 minutes. I've already moved all images over to this table so there are no new images to pull over so I'm assuming it's still reading the table for new records. The job is not being blocked and is still runnable.

Is there any way to optimize this stored procedure so that it pulls only new records. My goal would be to optimize so that we can run this on a re-occuring schedule. The business requirements are to run this every minute, but based on the current outcome that is not possible.

I've included the source and target table schema so you can see all columns that I have to work with. I am not sure if I can use the created_date in the source table to filter the data so it doesn't look thru all the data. There is also an identity column ( acc_image_id ) in the source table. I'm not sure how I should modify the stored procedure to pull data more quickly based on the max(identity) .

FYI, I can add columns to the target table if it makes sense to add a column to help filter this data further so that it only looks for recent images.

Source table:

CREATE TABLE [dbo].[acc_image]
(
    [acc_image_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [acc_id] [int] NULL,
    [image_type_id] [int] NOT NULL,
    [data_format] [varchar](10) NOT NULL,
    [label] [varchar](50) NOT NULL,
    [description] [varchar](255) NULL,
    [image_width] [smallint] NULL,
    [image_height] [smallint] NULL,
    [image_color_depth] [tinyint] NULL,
    [image_thumbnail] [image] NOT NULL,
    [data] [image] NOT NULL,
    [notes] [text] NULL,
    [acc_specimen_id] [int] NULL,
    [acc_slide_id] [int] NULL,
    [include_in_report] [char](1) NOT NULL,
    [include_in_internet] [char](1) NOT NULL,
    [created_date] [datetime] NOT NULL,
    [row_version] [timestamp] NOT NULL,
    [report_section_number] [smallint] NULL,
    [external_notes] [text] NULL,
    [source_filename] [varchar](255) NULL,
    [page_count] [int] NOT NULL,
    [include_as_attachment] [char](1) NOT NULL,
    [sort_order] [smallint] NULL,
    [acc_parent_image_id] [int] NULL,
    [created_by_id] [int] NULL,
    [annotation] [text] NULL,
    [specimen_results_enabled] [char](1) NOT NULL,
    [dis_imageserver_id] [int] NULL,
    [external_slide_image_id] [varchar](80) NULL,
    [external_report_image_id] [varchar](80) NULL,
)

Here is the target table schema. I have an inserted_date that I don't know if I can use to optimize:

CREATE TABLE [dbo].[acc_image]
(
    [acc_image_id] [int] NOT NULL,
    [acc_id] [int] NULL,
    [image_type_id] [int] NOT NULL,
    [data_format] [varchar](10) NOT NULL,
    [label] [varchar](50) NOT NULL,
    [description] [varchar](255) NULL,
    [image_width] [int] NULL,
    [image_height] [int] NULL,
    [image_color_depth] [tinyint] NULL,
    [image_thumbnail] [image] NOT NULL,
    [data] [image] NOT NULL,
    [image_guid] [uniqueidentifier] NULL,
    [created_date] [datetime] NOT NULL,
    [row_version] [varbinary](12) NOT NULL,
    [sort_order] [int] NULL,
    [insert_date] [datetime] NOT NULL,
    [updated_date] [datetime] NULL,
)

Here's the stored procedure code:

ALTER PROCEDURE [dbo].[get_image]
AS
BEGIN
    DECLARE @RecCt AS int = 0

    BEGIN TRY
        INSERT INTO connect_onprem.dbo.acc_image (acc_image_id, acc_id, image_type_id,    
                                                  data_format, label, description, 
                                                  image_width, image_height, 
                                                  image_color_depth, image_thumbnail, 
                                                  data, created_date, row_version, sort_order)
        SELECT 
            src.acc_image_id, a.id, src.image_type_id, 
            src.data_format, src.label, src.description, 
            src.image_width, src.image_height,
            src.image_color_depth, src.image_thumbnail,
            src.data, src.created_date, src.row_version, src.sort_order
        FROM 
            [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].accession_2 a 
        INNER JOIN 
            [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_specimen s ON a.primary_specimen_id = s.id
        INNER JOIN 
            [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_slide ass ON s.id = ass.acc_specimen_id
        --source table      
        INNER JOIN 
            [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_image src ON ass.id = src.acc_slide_id
    
        --target table
        LEFT JOIN 
            connect_onprem.dbo.acc_image tgt ON src.acc_image_id = tgt.acc_image_id
        WHERE 
            tgt.acc_image_id IS NULL
            AND a.acc_type_id <> 134
            AND a.status_final = 'Y'
        ORDER BY 
            acc_image_id
    
    SET @RecCt = @@ROWCOUNT

    IF @RecCt > 0
    BEGIN
        INSERT INTO connect_onprem.dbo.ErrorLog (UserName, ErrorNumber, ErrorState, 
                        ErrorSeverity, ErrorLine,  ErrorProcedure, ErrorMsg, ErrorDateTime)
        Values ('RecTrack', @RecCt, 0, 0, 0, 'get_image', 
                            'connect_onprem.dbo.acc_image Inserted Records', GETDATE());
    END

END TRY
BEGIN CATCH
INSERT INTO  connect_onprem.dbo.ErrorLog (UserName, ErrorNumber, ErrorState, ErrorSeverity, 
                  ErrorLine, ErrorProcedure,  ErrorMsg, ErrorDateTime)
VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), 
            ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE());
    END CATCH
END

Goal: optimize this to run as fast as possible; to read the source table and insert "new" records into the target table using a linked server running across the network.

Right now, the stored procedure is running over 1 hour and 22 minutes. I need to somehow change this to be able to run at the minimum every 1 minute (if possible).