Forum Discussion

SenthilMadhan's avatar
SenthilMadhan
Copper Contributor
Oct 03, 2023

Sql Server 2019-handle huge records for Insert/update in SP

HI Team,

We are using SQL server 2019 and we have scenario to insert/update 1 million records in a stored proc.

 

We are now using a while loop for inserting/updating the 1 million records at a batch size of 10000 records at one time.  This is taking more than 8 mins .We have good amount of machine size.

 

1)What are all the options I have to tune it more?

2)Can I do the insert/update in one single shot instead of batch?

3)I need to perform this in stored proc.

 

Please advise

 

 

2 Replies

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Oct 04, 2023

    SenthilMadhan , a very hypothetical question with very less on informations, so what to answer? Test it.

     

    If there are indexes on the table, you could first disable them, load the data and then rebuild the indexes; that could speed it up.

     

    • SenthilMadhan's avatar
      SenthilMadhan
      Copper Contributor
      Oct 04, 2023
      HI Sir,
      I have a problem in that .My stored procedure will be executed in parallel from different nodes .

      So disabling and enabling from will create a block for the other transactions.

      Can I share my code here ?so you can have a better idea

Resources