cancel
Showing results for 
Search instead for 
Did you mean: 

turn delta merge off

rajarshi_muhuri
Active Participant
0 Kudos

I have a stored procedure doing joins and transformation in a loop and writing to a column table in HANA.

The stored Procedure is taking 65 minutes to run in Prod for a 50 million with 40 million left join .

Seems that during the entire run , not a single record is written . but at the end of 65 min all the records are written all at once . Seems that "MERGE delta " is running all during this time .

How do I disable DELTA MERGE , so that the stored procedure bypasses it and writes to the physical table directly .

Would table lock be an option ?

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Rajarshi Muhuri wrote:

I have a stored procedure doing joins and transformation in a loop and writing to a column table in HANA.

Hi Rajarshi,

ok, you do joins and transform your data - but why do  you do this in a loop?

Why do you need to persist this?

Experience so far is that persisting such things is in many cases not required. Instead proper query model design can help a great deal here.

Anyhow, there are two comment I'd like to make to the rest of your description of the situation:

Seems that during the entire run , not a single record is written . but at the end of 65 min all the records are written all at once . Seems that "MERGE delta " is running all during this time .

Nope, the effect of "seeing" the records in the table only after the commit is done.

Remember, by default HANA studio uses autocommit which means, that after every SQL command a COMMIT is send to the database.

This also applies to procedure calls...

So, when you check for the data in the target table from another session (each SQL command window in HANA studio has it's own session!), you won't ever see uncommitted data.

Only after COMMIT, which means after the end of the procedure call in your case, the data will be visible to other sessions.

This has nothing to do with the DELTA MERGE process.

The DELTA MERGE does not change the visibility of data and does not put locks on your data.

Even when the DELTA MERGE is running, you can SELECT, UPDATE, INSERT or DELETE your data.

If you want to check, whether a MERGE operation is currently active, you can just check the THREADS or JOBS display in HANA Studio Administration perspective.

Would table lock be an option ?

As the other commentators already answered your questions on disabling DELTA MERGE, I'll just focus on the last question.

What would a table lock do here?

It would prevent any other process to acquire locks on data in this table.

It would also hang and wait if, at the moment you ask for the table lock, another session has a lock on a row in this table.

It basically would kill concurrent and parallel table access.

It would be useful however, if and only if, waiting for locks is a major contributor to the runtime of your procedure (which I somewhat doubt)...

- Lars

Answers (3)

Answers (3)

Former Member
0 Kudos

ex.

alter table tablename disable automerge

former_member184768
Active Contributor
0 Kudos

Hi Rajarshi,

I don't think there can be the issue with the delta merge. Basically delta merge is an operation to move data from one type of memory (write optimized) to other type.

I did not quite understand what do you mean by "write directly to physical table". If you mean the columnar table memory (Main memory) then you may not be allowed to write directly to it. The process of writing to the main memory itself is quite expensive. Hence delta merge is performed when the system utilization is low.

Also how did you identify that no records are written. There could be some lag in reflecting the table stats after the commit is performed.

Can you please identify the bottlenecks in the procedure. You can also try to break it down into multiple inserts and use explicit commit transactions to check if the records are periodically written and committed to the database. You can perform the commits in the loop you mentioned.

Please check how much data is being processed. Check if you can somehow parameterize the joins with some where conditions and put some filters and break down the high volume join on the tables.

Regards,

Ravi

former_member182114
Active Contributor
0 Kudos

Hi Rajarshi,

You can disable delta merge during your operation but you can't bypass DELTA.

Delta is the "inbox" for column tables. The only exception I know is when you import from binary which have all the indexes already prepared from the other system (it's not an option for you).

The command to disable auto merge is alter table:

http://help.sap.com/hana/html/sql_alter_table.html

This can speed up your loading process, but at end you should trigger the merge manually or turn on auto merge.

Regards, Fernando Da Rós