title: I have a Point-in-Time SQL insert prodecure that requires some speed up
body: <p>I have two SQL insert procedures in Azure Data Factory. First one is to insert changes and the second is for inserting new rows in case they are found to be not existing in the new CSV files. Especially the second insert procedure is too slow of a process.</p>
  
  <p>As background, there are around 50-100 thousand daily rows in the dbo.reservations2 table. And the tables that are inserted into vary between 200 thousand to 2 million rows. Currently, the first procedure takes around 2,5 minutes which is most certainly ok. But the second one seems to take multiple hours. The required speed for the second one would be under one hour. </p>
  
  <p>To further specify there are 3 types of time horizons coming in, one with 7 days, second with 14 days and last with 30 days to the future. The 30 days update only comes ones a day, 14 days ones an hour and the 7 days every 15 minutes. For the 30 days update, there is a timeslot where nothing else happens for around 1,5 hours each morning</p>
  
  <p>I have tried the following indexes, not sure though if they help. At least the second one actually slowed down the insertion of first procedure when testing (even though Explain capability of Azure data studio did suggest it).</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX reservations_date_custom on dbo.reservations2 (datetime) include (dv_id, dv_datahash, id, unit, room, duration, specialist, specialisation1, specialisation2, specialisation3, timetype)
  
  CREATE NONCLUSTERED INDEX reservations_date_custom2 on dbo.reservations2 (datetime) include (dv_datahash, dv_load_time, id)
  
  CREATE NONCLUSTERED INDEX staging_id on staging.reservations (id)
  </code></pre>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[dv_insert]
  AS
  INSERT INTO 
  dbo.reservations2 (
      dv_id, 
      dv_datahash, 
      dv_load_time, 
      dv_expir_time,
      id, 
      unit,
      room, 
      datetime, 
      duration, 
      specialist, 
      specialisation1, 
      specialisation2, 
      specialisation3,
      timetype, 
      reserved
  ) 
  SELECT 
      dv_id, 
      dv_datahash, 
      dv_load_time, 
      dv_expir_time,
      id, 
      unit,
      room, 
      datetime, 
      duration, 
      specialist, 
      specialisation1, 
      specialisation2, 
      specialisation3,
      timetype, 
      reserved
  FROM (
      SELECT 
      CONVERT(VARCHAR(32), HashBytes('MD5', UPPER(ISNULL(id, '-1'))), 2) AS dv_id,
      CONVERT(VARCHAR(32), HashBytes('MD5', UPPER(ISNULL(CAST(id AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(unit AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(room AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(datetime AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(duration AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(specialist AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(specialisation1 AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(specialisation2 AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(specialisation3 AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(timetype AS nvarchar(max)), '-1') + '~' + ISNULL(CAST(reserved AS nvarchar(max)), '-1'))), 2)  AS dv_datahash, 
      GETDATE() AS dv_load_time, 
      null as dv_expir_time,
      id AS id, 
      unit AS unit, 
      room AS room, 
      datetime AS datetime, 
      duration AS duration, 
      specialist AS specialist, 
      specialisation1, 
      specialisation2, 
      specialisation3,
      timetype AS timetype, 
      reserved AS reserved  
      FROM staging.reservations2) t1 
  WHERE t1.dv_datahash NOT IN (
      Select dv_datahash
      From (Select dv_datahash, row_number() over(partition by id order by dv_load_time desc) as ranking
          from dbo.reservations2
          Where datetime &gt;= (Select min([datetime]) from staging.reservations2)) as t2
      Where t2.ranking = '1'
  )
  </code></pre>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[dv_insert_deleted]
  AS
  INSERT INTO 
  dbo.reservations2 (
      dv_id, 
      dv_datahash, 
      dv_load_time, 
      dv_expir_time,
      id, 
      unit,
      room, 
      datetime, 
      duration, 
      specialist, 
      specialisation1, 
      specialisation2, 
      specialisation3,
      timetype, 
      reserved
  ) 
  SELECT 
      dv_id, 
      dv_datahash, 
      dv_load_time, 
      dv_expir_time,
      id, 
      unit,
      room, 
      datetime, 
      duration, 
      specialist, 
      specialisation1, 
      specialisation2, 
      specialisation3,
      timetype, 
      reserved
  FROM (
      SELECT 
      dv_id,
      dv_datahash, 
      Getdate() AS dv_load_time, 
      null as dv_expir_time,
      id, 
      unit, 
      room, 
      datetime, 
      duration, 
      specialist, 
      specialisation1, 
      specialisation2, 
      specialisation3,
      timetype, 
      2 AS reserved  
      FROM dbo.reservations2) t3
      WHERE t3.[datetime] &gt;= (Select min([datetime]) from staging.reservations2)
      AND t3.[datetime] &lt;= (Select max([datetime]) from staging.reservations2)
      AND t3.id NOT in(
          SELECT distinct id
          FROM staging.reservations2)
      AND t3.id Not in(
          Select id
          From (Select reserved, id, row_number() over(partition by id order by dv_load_time desc) as ranking
              from dbo.reservations2
              Where datetime &gt;= (Select min([datetime]) from staging.reservations2)
              AND datetime &lt;= (Select max([datetime]) from staging.reservations2)) as t2
          Where t2.ranking = '1'
          AND reserved = '2')
  </code></pre>