Optimizing individual "update or insert" statements
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.
0
Comments
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 msSorry, 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)@natalia_2017 - You asked the same question twice.
See also:
https://forum.vertica.com/discussion/239186/optimizing-individual-insert-or-update-statements#latest
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"
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.