Use MERGE to Update 1 Million Rows

Posted May 22, 2017 by Soniya Shah, Information Developer

High angle view of Beijing Guomao.
This blog post was co-authored by Yassine Faihe, Michael Flower, and Moshe Goldberg.

Updating One Million Records in Two Seconds

To illustrate the true power of MERGE, this article describes how we used MERGE to demonstrate Vertica’s performance at scale.

SQL MERGE statements combine INSERT and UPDATE operations. They are a great way to update by inserting a small (<1000), or large (>1 million) number of records from a source table into a target table. The source table can include new and existing data. If the target table does not include any of the source table’s records (new or existing), MERGE inserts all source data into the target. The following is a MERGE example with two MERGE options that you update (WHEN MATCHED THEN UPDATE…) or insert data (WHEN NOT MATCHED THEN INSERT…). MERGE INTO target TGT USING source SRC ON SRC.A=TGT.A WHEN MATCHED THEN UPDATE SET A=SRC.A, B=SRC.B, C=SRC.C, D=SRC.D, E=SRC.E WHEN NOT MATCHED THEN INSERT VALUES (SRC.A,SRC.B, SRC.C, SRC.D, SRC.E); This diagram illustrates the results of merging data into an existing target table:



To realize the full potential of MERGE, both tables in the MERGE statement must meet a set of conditions. When tables meet such conditions, Vertica optimizes the statements to significantly improve performance.

This article describes using MERGE to address a set of customer requirements. By meeting all of the required MERGE conditions, we saw spectacular performance results: updating one million records in two seconds. We also illustrate how to convert a non-optimized merge into an optimized one, and explain, and include the script used to do so. For many more details about the MERGE statement, see Improving MERGE Performance in the Vertica documentation.

Putting MERGE to Work

Initially, our customer requested that we update one million records in a target fact table, pre-populated with dozens of columns and billions of rows. We ran some internal MERGE performance tests to benchmark results. These tests updated several columns and dozens of fields, with promising results: 30 seconds. By updating even fewer columns, initial results improved: decreasing from 30 seconds to 8 seconds. The 8-second result seemed to meet our customer’s request.

Still, we had many preconceptions about Vertica updates, especially our assumption that adding more fields would reduce performance. We did not consider the opposite possibility: that we could increase performance by adding more fields. We were about to uncover our faulty assumptions, totally changing our misconceptions.

After presenting our 8-second test results to the customer, their next request was to make similar updates, this time with a heavy query workload in process. Determined to address all customer requests, we continued.

Meeting Optimized Query Plan Conditions

Our first task was to change the MERGE statement to meet all of the required conditions for optimization:
  1. Target table’s join column has a unique or primary key constraint
  2. UPDATE and INSERT clauses include every column in the target table
  3. UPDATE and INSERT clause column attributes are identical
  4. Source table is smaller than the target table
After meeting these conditions, Vertica prepared an optimized query plan and ran much faster. When conditions were not met, Vertica prepared a non-optimized query plan, and performance decreased. The decreased result was similar to earlier Vertica releases before optimized MERGE was available.

Optimized Query Plan Benchmark Results

Here are the benchmark results when we met all the optimized query plan conditions. These tests all used the MERGE statement with the same fact table, which had 2.7B rows, and dozens of fields. Test iterations used different data:

Description Seconds
UPDATE 1 million records, dozens of fields, in a table with 2.7B rows, no background queries 2.2
UPDATE 1 million records, dozens of fields, in the same table, with background queries running 2.5
INSERT 1 million records into the same fact table, with background queries running 1.4
Use a MERGE statement with the same table to INSERT 500K records and UPDATE 500K records, with background queries running 13.0


Given these results, we wondered if the UPDATE performance behavior is linear? The answer is Yes, as shown by the following UPDATE benchmarks:

Description Seconds
Update 100 million records, dozens of fields, in the same target fact table, but with ~3B rows, without background queries running 220
Update 100 million records, dozens of fields, in the same target fact table, but with ~3B rows, with background queries running 250


While testing our optimized MERGE statement syntax (with UPDATE and INSERT clauses) we made these additional conclusions:
• Performance increased significantly when the source table data caused only UPDATES, or only INSERTS.
• Performance decreased when the source table data caused a mix of INSERTs and UPDATEs in the same statement.

Note: All results were generated with the maximum input/output operations per second (IOPS) possible on a DL380p lab system, with two CPUs, and only 12 internal commodity disk drives. Running the benchmarks on the same system with 22 disks would result in almost twice the number of IOPS, and even more impressive performance.

Converting to an Optimized MERGE

Nobody wants a MERGE statement that is not optimized, but sometimes it’s hard to avoid. Fortunately, you can convert a non-optimized MERGE into an optimized version. We’re including the full script we used OptimizeMERGE_Script. The script illustrates how we create an optimized MERGE version, by avoiding right outer joins.

In our test, we created a source table with three fields from the target table, using a non-optimized query.

Next, we updated 1 million records. Then, we inserted another million records, based on three fields in the SOURCE table. Separating the UPDATE and INSERT activities into the same TARGET table using an Optimized Merge took 7.6 seconds (including the convert time). So, the starting point is that we do not have all of the fields. Updating only two fields does not fulfill the optimized MERGE query plan conditions. Here are descriptions of the script steps and their completion times:

Test Phase Phases to Convert Non-Optimized MERGE to Optimized Time (ms)
Preparation Create a SOURCE table, containing three fields from TARGET. Use this table for a non-optimized test. N/A
Set rows for UPDATE Create TMPSOURCE table, containing all fields from TARGET. Use this table as the first phase of converting the query into an optimized UPDATE. 4.8
Set rows for UPDATE Insert 1M records from SOURCE into TMPSOURCE , with existing user_id in TARGET table. 4,179.774
Set rows for INSERT Create TMPISOURCE table, containing all fields from TARGET. Use this table during first phase of converting query to an optimized INSERT. 5.125
Set rows for INSERT Insert 1M records from SOURCE into TMPISOURCE, with existing user_id in TARGET table table. 977.238
UPDATE time MERGE INTO TARGET USING TMPSOURCE (Optimized 1M UPDATES) 1,262.036
INSERT time MERGE INTO TARGET USING TMPSOURCE (Optimized 1M UPDATES) 1,219.661


Let’s take a closer look at the breakdown of numbers:
Total time (Convert time + Optimized INSERT + Optimized UPDATE)= 7,648.634ms
Non-optimized time (MERGE into target using SOURCE table, 1M UPDATES + 1M INSERTS) = 57,134.958
Total savings (Non-optimized MERGE 57 seconds, Optimized MERGE 7.6 seconds)= 49,486.324

Proving MERGE Optimization

To confirm that your MERGE statement is optimized, search for a semi path in the EXPLAIN plan (search for semi). Similarly, you can confirm that a MERGE statement is not optimized by searching for RightOuter in the EXPLAIN plan.

Confirming MERGE Optimization

Here’s how we determined whether our tests were optimized, or not. To confirm optimization, we used the grep ‘Semi’ command to confirm that this Explain plan is optimized: \o | grep --color 'Semi' EXPLAIN MERGE INTO MYSCHEMA.TARGET TGT USING MYSCHEMA.TMPSOURCE SRC ON SRC.user_id=TGT.user_id WHEN MATCHED THEN UPDATE SET user_id=SRC.user_id, user_first_name=SRC.user_first_name, user_last_name=SRC.user_last_name,f04=SRC.f04, f05=SRC.f05, f06=SRC.f06, f07=SRC.f07, f08=SRC.f08, f09=SRC.f09, f10=SRC.f10, f11=SRC.f11, f12=SRC.f12, f13=SRC.f13, f14=SRC.f14, f15=SRC.f15, f16=SRC.f16, f17=SRC.f17, f18=SRC.f18, f19=SRC.f19, f20=SRC.f20, f21=SRC.f21, f22=SRC.f22, f23=SRC.f23, f24=SRC.f24, f25=SRC.f25, f26=SRC.f26, f27=SRC.f27 WHEN NOT MATCHED THEN INSERT VALUES(SRC.user_id, SRC.user_first_name, SRC.user_last_name, SRC.f04, SRC.f05, SRC.f06, SRC.f07, SRC.f08, SRC.f09, SRC.f10, SRC.f11, SRC.f12, SRC.f13, SRC.f14, SRC.f15,SRC.f16, SRC.f17, SRC.f18, SRC.f19, SRC.f20, SRC.f21, SRC.f22, SRC.f23, SRC.f24, SRC.f25, SRC.f26, SRC.f27); | +---> JOIN MERGEJOIN(inputs presorted) [Semi] [Cost: 2M, Rows: 1M] (PATH ID: 1) Inner (RESEGMENT) | +---> JOIN MERGEJOIN(inputs presorted) [Semi] [Cost: 2M, Rows: 1M] (PATH ID: 1) | +---> JOIN MERGEJOIN(inputs presorted) [Semi] [Cost: 2M, Rows: 1M] (PATH ID: 1) Inner (RESEGMENT) | +---> JOIN MERGEJOIN(inputs presorted) [Semi] [Cost: 2M, Rows: 1M] (PATH ID: 1) 4[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT1_DBD_1_seg_DestoUPD_b1 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 14[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT1_DBD_1_seg_DestoUPD_b0 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 22[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT0_PROJ_v1_b1 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 32[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT0_PROJ_v1_b0 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 50[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT1_DBD_1_seg_DestoUPD_b1 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 60[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT1_DBD_1_seg_DestoUPD_b0 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 68[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT0_PROJ_v1_b1 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 78[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT0_PROJ_v1_b0 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];

Search For Term RightOuter

To confirm that the MERGE statement is not optimized, we used the grep ‘RightOuter’ command to search for that term. This MERGE statement could never be optimized because it did not comply with the optimization requirements. How? By omitting the user_last_name=SRC.user_last_name clause from the UPDATE clause, the number of column attributes in the INSERT and UPDATE clauses were uneven. In addition, we did not specify all table columns in the statement. \o | grep 'RightOuter' EXPLAIN MERGE INTO MYSCHEMA.TARGET TGT USING MYSCHEMA.TMPSOURCE SRC ON SRC.user_id=TGT.user_id WHEN MATCHED THEN UPDATE SET user_id=SRC.user_id, user_first_name=SRC.user_first_name, f04=SRC.f04, f05=SRC.f05, f06=SRC.f06, f07=SRC.f07, f08=SRC.f08, f09=SRC.f09, f10=SRC.f10, f11=SRC.f11, f12=SRC.f12, f13=SRC.f13, f14=SRC.f14, f15=SRC.f15, f16=SRC.f16, f17=SRC.f17, f18=SRC.f18, f19=SRC.f19, f20=SRC.f20, f21=SRC.f21, f22=SRC.f22, f23=SRC.f23, f24=SRC.f24, f25=SRC.f25, f26=SRC.f26, f27=SRC.f27 WHEN NOT MATCHED THEN INSERT VALUES( SRC.user_id, SRC.user_first_name, SRC.user_last_name, SRC.f04, SRC.f05, SRC.f06, SRC.f07, SRC.f08, SRC.f09, SRC.f10, SRC.f11, SRC.f12, SRC.f13, SRC.f14, SRC.f15, SRC.f16, SRC.f17, SRC.f18, SRC.f19, SRC.f20, SRC.f21, SRC.f22, SRC.f23, SRC.f24, SRC.f25, SRC.f26, SRC.f27); | +---> JOIN MERGEJOIN(inputs presorted) [RightOuter] [Cost: 82M, Rows: 1M (NO STATISTICS)] (PATH ID: 1) 5[label = "Join: Merge-Join: \n(MYSCHEMA.TARGET x MYSCHEMA.TMPSOURCE) using MOGOT1_DBD_1_seg_DestoUPD_b0 and subquery (PATH ID: 1)\n[RightOuter]\n\nUnc: Integer(8)\nUnc: Varchar(64)\nUnc: Varchar(64)\nUnc: Integer(8)\nUnc […]