| Author |
Topic |
|
allenjones
Starting Member
3 Posts |
 Posted - 2014-11-25 : 10:02:55
|
| I have a master table with around 11 million records and 110 columns. Each week I get updates to add to or update this table. Depending on the process I also need to notify various people of changes to the data. The update records (around 86000 per week) are placed into a staging table. From here I need to check if any of the rows exist in the master table. If they do then I update the master table. If they do not then I insert them to the master table. I then need to send an email to some people in the office to notify them of what changes have been made.What is the easiest/fastest way of achieving this? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-25 : 10:18:06
|
| Use a MERGE command with the OUTPUT clause to write the inserts and updates to another table. After the MERGE, process the data written by the OUTPUT clause to build calls to DbMail. |
 |
|
|
allenjones
Starting Member
3 Posts |
Posted - 2014-11-25 : 10:50:16
|
| Thanks for the reply. I shall have a look at this and see how it goes. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-11-25 : 11:37:16
|
| MERGE has been known to cause performance issues and still seems to have other odd issues.For now, I'd stick with UPDATE and INSERT.1) UPDATE the main table using an INNER JOIN to the staging table. Use the OUTPUT clause to identify the rows that were processed for sending emails, etc.. When done with the UPDATE process, delete those rows from the staging table.2) INSERT remaining in the staging table to the main table. Use the staging table for sending emails, etc..For only 86K rows, hopefully you won't have to worry about batching the UPDATEs in smaller numbers of rows, but you can do that too if needed. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-25 : 11:51:46
|
quote:
Originally posted by ScottPletcher
MERGE has been known to cause performance issues and still seems to have other odd issues.
Hey Scott, do you have any references for that? I'd like to learn more. I'm a heavy user of and advocate for MERGE and need to stay on top of any issues.AFAYK, do these issues persist in SS 2012 and 2014? |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-11-25 : 12:12:32
|
quote:
Originally posted by gbritton
quote:
Originally posted by ScottPletcher
MERGE has been known to cause performance issues and still seems to have other odd issues.
Hey Scott, do you have any references for that? I'd like to learn more. I'm a heavy user of and advocate for MERGE and need to stay on top of any issues.AFAYK, do these issues persist in SS 2012 and 2014?
Here's a couple of the better/fuller links:[url]http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/[/url][url]http://www.sqlservercentral.com/Forums/Topic1465931-391-2.aspx[/url]I've seen odd things continue with MERGE thru 2012; I can't say for 2014, only used it so far in very limited initial testing. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-25 : 12:23:21
|
| Looks like most of the issues are old and (probably) fixed already. I'll keep an eye open for things with SS2012. Note that itzik recommends using SERIALIZABLE on the target table to avoid conflicts if two or more Merges are being run at the same time. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-11-25 : 12:57:48
|
quote:
Originally posted by gbritton
Looks like most of the issues are old and (probably) fixed already. I'll keep an eye open for things with SS2012. Note that itzik recommends using SERIALIZABLE on the target table to avoid conflicts if two or more Merges are being run at the same time.
But you'll never know if/when two merges are being run at the same time, so you'll always be forced to use SERIALIZABLE which will cause concurrency issues. Also given the havoc it creates with triggers firing, why bother? An UPDATE then an INSERT is far less painful than all that. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-26 : 09:42:53
|
| If you are using MERGE to update a SCD in an ETL to a DW, there should never be simultaneous merges (if there are, I question the ETL design). Triggers should be uncommon (preferably non-existent) on tables in a DW. Any other updates should be handled by the ETL process (e.g. with SSIS) instead. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-11-26 : 10:34:15
|
quote:
Originally posted by gbritton
If you are using MERGE to update a SCD in an ETL to a DW, there should never be simultaneous merges (if there are, I question the ETL design). Triggers should be uncommon (preferably non-existent) on tables in a DW. Any other updates should be handled by the ETL process (e.g. with SSIS) instead.
"Should not be" doesn't cut it here. Either there are never other such things, or MERGE isn't entirely safe. I have a DM that can have simultaneous loads into the same table ... if you want near-real-time data, at some point that can happen. The clustering keys are designed to isolate their activity from each other, and of course snapshot isolation is used as well. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-26 : 10:52:52
|
| Depends what you use it for. if you have two SSIS jobs running together updating the same slowly changing dimension at the same time, you have a problem with your design. To me, that is a prime example where MERGE shines. For fact tables, I agree with you. |
|
|
|
|
UPDATE or INSERT from a staging table