Skip to Content

INSERT Vs UPSERT in SAP HANA?

We have 100 millions to handle.

We have a final table . Let say the name of final table is TFinal.

We have different sub table (T1,T2,T3 , etc ) which insert the calculated data in TFinal.

The problem is we have duplicate records in T1, T2, T3.

For Example :- T1 data,

ID 1 2 3 4

T2 data ,

ID 5 6 3 7

First the data from T1 will be inserted into 'TFinal' table . Subsequently, we will be inserting T2 data in TFinal table. But, Insert statement for Table T2 will fail because ID (3) will be duplicate entry in table 'TFinal' as it was already inserted in TFinal from Table T1.

Solution for the above problem is, we can use UPSERT instead of INSERT.

But, I want to understand the time complexity of both the statement.
Any link where I can read about it or any quick explanation will be appreciated.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Posted on Jul 26, 2016 at 03:31 PM

    Suleman...

    you can attempt the UPSERT and also you could do a left join and only insert missing records.

    -- try

    select t2.id

    from t1 left join t2

    on t1.id = t2.id

    where t1.id is null; -- try this

    as far as the time it may take... it will depend on your HANA free memory, etc... what you can do to estimate the time.. you could do a select statement using the left join... I dont think you can get the actual insert time until you run it, again due to other processes running, etc.

    hope this helps...

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 26, 2016 at 08:33 PM

    Does that table have any "DEFAULT" columns ?

    Apart from that ,if you are OK to upsert, It means you are OK to replace the values against that particular Primary key combination and replace with the new record.

    While trying to get the statistics, you can also perform a delete first ( for the matching records ) and then use a insert as well.

    we have been using UPSERT and definately it would have some performance overhead as it would try to match against the primary key combination to determine if it has to be a INSERT or UPDATE.

    Based on the above suggestions and from others and looking into the stats, you could take a call

    Regards,

    Krishna Tangudu

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 26, 2016 at 03:27 PM

    Hello

    You could try each technique and capture runtime information yourself to determine the execution plan of each approach - see the documentation for explain plan and visualise plan.

    However, with HANA the theory would be not to move large amounts of data between tables, you would create a virtual data model (TFinal in your case) on top of the tables (t1, t2, t3 in your case) to provide the result you require. Your current theory is very much of the ETL mechanisms that preceded HANA (although they are still very useful with traditional RDBMSs).

    Michael

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 28, 2016 at 09:41 AM

    Hi,

    From T1, T2, T3 ... tables, Select the records which are not there in TFinal Table.

    Then Insert those records to TFinal table.

    In this approach, Select and Insert statements used.

    UPSERT - Overwrites existing data.

    INSERT - Deletes existing data and Insert again.

    P.S: As table have 100 millions of data, Select statement has to process the all the records to check any duplicacy.


    Insert into TFinal ("ID")

    Select "ID" from T1

    WHERE "ID" NOT IN (Select "ID" from TFinal)


    Insert into TFinal ("ID")

    Select "ID" from T2

    WHERE "ID" NOT IN (Select "ID" from TFinal)



    Regards,

    Muthuram

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.