Will update have good performance over MERGE ?

suryatejasuryateja Vertica Customer
edited March 2023 in General Discussion

Hi Team,

I recently started using vertica and i see inserts and bulk updates are performing better but logical merges are lagging behind.

I have a tgt table with monthly data of around 30 million records and for merging one column of data into that is taking more than 60 mins and if i take the complete monthly data and put it in separate and run the same operation then its taking 3-5 mins and i have a partition based on month in the tgt table.

Merge into tgt_table -- 30 million monthly records
using src table -- this has around 40-50k records
on (conditions) -- these conditions has projections on them in both the tables
when matched then update
set tgt.column=src.column

Also, for some reason in some cases updates are performing better than merges and why it will usually happen ?

Anyway we can improve merge performance and are there any hints such as PARALLEL in vertica ?

Best Answer

  • VValdarVValdar Vertica Employee Employee
    Answer ✓

    There is hints in Vertica but parallelism is by design.
    Check the resources usage from both queries (merge and update), time is relative to the load of your cluster. CPU cycles, IO, memory usage are more stable.
    Check also the explain plans, and if you can profile both queries (to be sure partitions are correctly used in both case).
    They are other considerations but those are a good start.

Answers

  • VValdarVValdar Vertica Employee Employee

    And be sure to read this page about Merge optimization, there are several conditions.

  • suryatejasuryateja Vertica Customer

    Thanks for the details @VValdar and can you also help me in retrieving the DDL of stored procedure and edit on the fly as i am not able to do from dbeaver or razorsql. I can export object but this is tedious task and most DB's will have the option of editing the stored proc on the fly in the editor itself by clicking on function

  • SruthiASruthiA Administrator

    @suryateja I just tried to edit stored procedure via DBViz and it fails with below error. It requires a new feature request. Could you please open a support case, we can create a new feature request to support it via JDBC Driver.

    "There are no details for the 'Procedure' object type in database profile 'vertica'."

This discussion has been closed.