Insert Into Table performance questions



  • I am writing a stored procedure, main purpose of it will be the insertion into table
    Procedure will be signed by certificate, and sending email notifications

    Procedure will accept user-defined table-variable as a parameter, and insert it's contents into persistent table

    Below couple of questions:

    1. Can insertion into persistent table cause Table lock escalation on that table?
      Does it make any sense to the limit number of rows allowed to be inserted per 1 procedure execution to certain limit? say 100,200,500 rows etc. - to avoid lock_escalation event ?

    2. Temp tables have some benefit comparing to table variables (like statistics).
      Inside stored procedure, does it make any sense of first inserting contents of user-defined table-variable into #temp table, and then into target table ?
      Or we can skip #temp table and insert into persistent table, from table-variable directly and there won't be any performance drawbacks ?

    3. What locks are taken during insert ? And are there any other performance considerations for inserts ?

    update: added simplified procedure code:

    create procedure InsertIntoTable (@_TableData TableData readonly)
    as begin
    

    -- lets use intermediate temp table
    drop table if exists #InsertIntoTable

    create table #InsertIntoTable (
    column1 bigint,
    column2 varchar(50),
    column3 smallint,
    column4 datetime,
    column5 tinyint,
    column6 datetime,
    column7 varchar(500)
    )

    -- insert from table-variable into temp table
    insert into #InsertIntoTable (column1, column2, column3, column4, column5, column6, column7)
    select
    column1,
    column2,
    column3,
    column4,
    column5,
    column6,
    column7
    from @_TableData

    -- let's do the actual insert
    insert into Database..Table (column1, column2, column3, column4, column5, column6, column7)
    select
    column1,
    column2,
    column3,
    column4,
    column5,
    column6,
    column7
    from #InsertIntoTable

    end



  • Since something needs to first populate the table variable that is then passed to the stored procedure, unless the procedure is doing more than what you posted, why not populate the table directly? Skipping the use of the table variable and sproc?




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2