June 21, 2012 at 2:26 pm
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
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
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
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
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
I dont have idea on partioining plese let me know example how to write it
June 22, 2012 at 5:11 am
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://www.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