insert and update

  • girl_bj

    SSCommitted

    Points: 1719

    Hi, need some advice. I have more than 20 columns and more than 50k rows. I would like to do insert and update.

    I did the merge in SQL from this example: http://www.sqlservertutorial.net/sql-server-basics/sql-server-merge/

    I am having issue where it takes too long. More than 20 minutes. Is it because of the total rows? Any other option that I can use instead of merge?

  • Phil Parkin

    SSC Guru

    Points: 243596

    Sure, you can break down the merge into its constituent INSERTs, UPDATEs and DELETEs.

    Once you have done that, you will be in a better position to work out which statement(s) are the more time-consuming.

    You will also be able to get execution plans which should help with your troubleshooting. I am assuming that you have taken the time to ensure that the columns you are matching on are appropriately indexed.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • SGT_squeequal

    SSCertifiable

    Points: 7063

    I find Merge statements are very efficient for upserts (insert,update,delete) operations. could you share with us your execution plan? actual not estimated.

     

     

    ***The first step is always the hardest *******

  • Phil Parkin

    SSC Guru

    Points: 243596

    SGT_squeequal wrote:

    I find Merge statements are very efficient for upserts (insert,update,delete) operations. could you share with us your execution plan? actual not estimated.    

    You may be interested to read this. Depends, of course, on your definition of efficiency too.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • girl_bj

    SSCommitted

    Points: 1719

    Hi, i am using this method. but it takes longer time. i believe the columns are not index.

    More than 20 columns and more than 50k rows. It takes me more than half an hour to complete.

  • girl_bj

    SSCommitted

    Points: 1719

    After applying indexes, it seems faster now.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply