Post reply

MERGE Statement

  • June 21, 2012 at 2:26 pm

    #257847

    Hi,

    I am using merge statement for Insertion (just insert the new records)

    when conditions not matched then insert.

    IS merge is useful on Large dataset any performance issues using that.

    Please let me know and If yes please suggest the better solution.

    Thanks

  • June 21, 2012 at 2:52 pm

    #1504242

    If the only thing you're doing is inserts, I'd suggest Insert Select instead of Merge. That's usually faster.

    However, on very large datasets, inserts can end up slow too, depending on locking/blocking issues. Merge won't be faster, probably even slower, but you need to know what kind of environment you're working in to tell how fast either will be.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 21, 2012 at 6:09 pm

    #1504309

    The main benefit with Merge is the single transaction for both update and insert. Helps guarantee that no change can slip in between the insert and update statements.

  • June 21, 2012 at 8:07 pm

    #1504320

    I have one source table distributing data into two dest tables using incremental load concept but i need to insert new records if record already exists i wont insert that record.

    Please le me know with example how can i perform thus load using tsql if merge statement has performance issue

  • June 22, 2012 at 12:23 am

    #1504346

    mamatha17.r (6/21/2012)

    I have one source table distributing data into two dest tables using incremental load concept but i need to insert new records if record already exists i wont insert that record.

    Please le me know with example how can i perform thus load using tsql if merge statement has performance issue

    MERGE statement usually has great performance. However, on very large datasets, the performance of every statement (INSERT, MERGE) will degrade.

    Another option is thus to use partition switching. Insert the rows in an empty table, build the indexes and switch partitions. Very fast.

    In order to find the new rows, it would also be a good idea to set up change data capture (CDC) at the source.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 22, 2012 at 5:08 am

    #1504429

    I dont have idea on partioining plese let me know example how to write it

  • June 22, 2012 at 5:11 am

    #1504430

    mamatha17.r (6/22/2012)

    I dont have idea on partioining plese let me know example how to write it

    If you put some effort in it, you can find tons of information on the internet. Google is your friend.

    Even this site has some articles about it:

    http://qa.sqlservercentral.com/articles/partition/64740/

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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