Optimizing individual "update or insert" statements

natalia_2017

We are switching to vertica and one of our applications needs to periodically "update or insert" an individual record in a table, i.e if a record with the given keys exists in the table, then update the non-key fields. If the record doesn't exist, insert it.
On SQL Server, we used [update] if \@\@rowcount=0 [insert] construct, but vertica doesn't support it.
Can you please recommend the most optimal way to implement individual "update or insert" on Vertica table? I cannot use "merge", as it only works between two tables which doesn't make sense in case of an individual row update.

Jim_Knicely

Single updates/deletes are not a problem in Vertica. Just make sure you optimize your design for it!

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AnalyzingData/Optimizations/OptimizingDELETEsAndUPDATEsForPerformance.htm

Example of optimized design:

dbadmin=> select count(*) from test;
  count
----------
 5100000000
(1 row)

Time: First fetch (1 row): 24.595 ms. All rows formatted: 24.685 ms

dbadmin=> select * from test3 where c1 = 3030330;
   c1    | c2
---------+----
 3030330 |  1
(1 row)

Time: First fetch (1 row): 14.327 ms. All rows formatted: 14.385 ms

dbadmin=> update test3 set c2 = 100 where c1 = 3030330;
 OUTPUT
--------
      1
(1 row)

Time: First fetch (1 row): 25.638 ms. All rows formatted: 25.708 ms

dbadmin=> select * from test3 where c1 = 3030330;
   c1    | c2
---------+-----
 3030330 | 100
(1 row)

Time: First fetch (1 row): 19.235 ms. All rows formatted: 19.270 ms

Jim_Knicely

Sorry, I misunderstood the question. When I first read your post I thought you had a concern with single row updates

Anyway, you can use the MERGE command on a single table. Like this:

dbadmin=> select * from merge_test;
 c1 | c2
----+-----
  1 | JIM
(1 row)

dbadmin=> merge into merge_test a using (select 1 c1, 'JANE' c2) b on b.c1 = a.c1 when matched then update set c2 = b.c2 when not matched then insert values (b.c1, b.c2);
 OUTPUT
--------
      1
(1 row)

dbadmin=> merge into merge_test a using (select 2 c1, 'JOSH' c2) b on b.c1 = a.c1 when matched then update set c2 = b.c2 when not matched then insert values (b.c1, b.c2);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from merge_test;
 c1 |  c2
----+------
  1 | JANE
  2 | JOSH
(2 rows)

Jim_Knicely

@natalia_2017 - You asked the same question twice.

See also:
https://forum.vertica.com/discussion/239186/optimizing-individual-insert-or-update-statements#latest

natalia_2017

Thanks a lot for the answer. I was looking at the documentation for 8.0 version in this version merge doesn't support source subqueries. "Source data can come from a base or external table only. Subqueries and joins are not allowed.". However, I checked documentation on Vertica 8.1 and I see that it now supports merging from subquery, which is precisely what I need.

Apologies for the duplicate post - I resubmitted by mistake. How can I delete it? I don't see "delete" option, only "edit"

Jim_Knicely

Glad that worked for you!

There isn't a way to delete posts, but I can ask to have one removed if you want. But since there are responses to each post, it's probably best just to leave them both.