Optimizing individual "insert or update" 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
Answers
Tough luck. You need to use MERGE. SQL is a descriptive language, not a procedural one. And the SQL Server approach of using an "IF" is procedural, and is against the standard. Among other things, it slows down the process massively.
Vertica is a Big Data database, that is for masses of data.
Individual row updates is like trying to get off a train running at 320 km/h to pick up a few nice flowers and jump back in again. Either the train slows down and restarts (and loses massively time), or something breaks ...
I suppose it's not only one row per day that you need to treat this way.
I would collect them in a staging table, and then load them using an Optimized Merge (look that up in the manual)
Oh, you can also program a client with a MERGE statement with parameter markers, and actually trickle feed a merge. But that's programming in C, C++ or Java with embedded SQL.
Good luck
Marco