Auto commit for inserts

[patrickbachmann]

Hi experts,

I have a procedure that is writing a lot of INSERTS into a table one at a time and I noticed the inserts are not truly committed until after the procedure has completed successfully.  This is ok when running for a small amount of data but I'm concerned if I start running larger volumes of data that this will become a memory issue.  Is there a way to force the inserts to commit after each insert?

Thanks,

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

You can use the following commands to do it.

alter table <Table NAME> disable automerge before the start of the procedure.

Procedure ...

Exec ('COMMIT');

merge delta of <TABLE NAME> in the procedure

After Job is completed

alter table <Table NAME> enable automerge

merge delta of <TABLE NAME>

This process helped us in not increasing the Delta memory as well committed the data to the table.

Note :

LARS do not bash me for this recommendation. HANA is great for reads but when it comes to writing using procedures is pretty bad as we need to deal with single threaded process and high memory consumption before the writes are completed. I hope you can come with a better white paper about it.

Regards

PK

[patrickbachmann · Accepted solution]

lol sorry I have to laugh at your Lars comment because I too fear of being judged by Lars and perhaps one other person whom I won't name.    Thank you for making me smile today.  Ok I'm going to look into each of your suggestions now. 

-Patrick

[patrickbachmann · Accepted solution]

Hey Purnaram,

Is the reason for disabling the delta merge because HANA will automatically attempt a merge after each and every commit if I did a commit after each single insert?  I will read the link from Krishna too, maybe it will explain this more....

Thanks,

-Patrick

[Former Member · Accepted solution]

Patrick,

The scenario I gave was for High volume data and not running this merge command after every update resulted memory growing exponentially. To solve it, we used chunks logic and after every chuck of inserts we ran the Delta merge so that Delta merge memory don't grow exponentially.

We needed to load 6 year of data.

In our proc we had start time and end time as in put parameters and with in store proc called the exec('commit') option and followed by merge option.

This way we made sure that delta merge was never growing exponentially but growing at limited rate.

Hope this explains.

Regards

PK

[lbreddemann · Accepted solution]

Former Member and Former Member:

Easy guys!

No need to fear anything. I'm not going to bash anybody

I just try to stick to a no-BS-attitude and - agreed on that - I typically do have an opinion about what to do and how to do it.

But that does not mean I am right with my opinion hehehe (just saw this yesterday )

A short comment on the original question:

Manually triggering a hard merge is typically not the greatest option.

The merge will be executed synchronously (your session will wait) and no compression optimization takes place.

Committing after each row would be the performance killer per se, so don't go for that either.

At the time being,  a reasonable middle way can be what SAP BW does:

Bulk loading in batches and the use of smart merges.

The downside here is that you have additional development effort for that if you are not using tools like DataServices (you need to have logic that can deal with failed batches, repeat them etc).

For future versions of SAP HANA the L2 delta will provide much better data change characteristics, so that this kind of difficulty should become easy to cope with.

cheers,

Lars

[justin_molenaur2]

Couple things

- the syntax exec('commit') may not be supported in future revisions per a response I got from development in a previous OSS message, so I would be careful how much you are using this.

- I would differentiate the discussion between commit (for which all the work in a SP is done at the procedure completion point) and the merge process, these are two separate topics. If you are concerned with the commit happening on too much data, consider splitting it up logically into separate runs. If you find you have issues with the merge process, first ensure your table is partitioned appropriately, then look into the suggestions given by PK. I'm with Lars here, my findings tell me that if the tables are partitioned appropriately, auto merge works fine.

Happy HANA,

Justin

[former_member182302]

Hi Justin,

Can you share the details of the OSS message? And the reasons mentioned by SAP to stop supporting that syntax? It would be helpful

Regards,

Krishna Tangudu

[lbreddemann]

Hi there

the point is that allowing transaction control commands in the EXEC/EXEC IMMEDIATE syntax is not proper language support for these commands and renders the overall transaction handling inconsistent.

For example: usually (if not in auto commit mode) you can always run a rollback after calling a procedure and everything will be as it was before.

With using commit/rollback within a procedure that's not the case at all.

In fact you cannot know what will be committed or rolled back which makes life suddenly a lot more complicated.

Up to now the only use case I have seen requiring transaction control in SQLScript is managing data loads. And again often the scenario for that was to build a data warehouse-like data mart.

The problem with that is: currently SQLScript is language wise insufficient for this kind of application.

For the smallest things (think error logging and handling) huge workarounds need to be implemented.

There is no reasonable scheduling option available and no way to run multiple tasks/procedures in parallel and manage these tasks.

It boils down to a huge list of workarounds and the inevitable use of external tools (or the reduction of functions in the system).

Now, given this situation, as a vendor for data processing platforms as we are, doesn't it make sense to say: let's concentrate functionality in the tools that are made for this kind of requirement and keep the other tools simple and lean?

I think it does.

and again - that's what I think - not the official SAP line in any way.

- Lars

[justin_molenaur2]

Lars would have the more definitive explanation from a core DBMS perspective, which he has already given.

What I can say from my experience recently can be summarized like this. The was in an endeavor to use TRUNCATE within a stored procedure originally.

- The TRUNCATE statement is not allowed in a stored procedure, if you attempt to activate you will get an 'unsupported feature' error.

- The EXEC('TRUNCATE') or similar using a variable, is also not supported in a stored procedure. If you attempt to activate, you will get an 'unsupported feature' error.

- The COMMIT statement is not allowed in a stored procedure, it you attempt to activate you will get an 'unsupported feature' error.

The EXEC('COMMIT') or similar using a variable is ALLOWED in a stored procedure.

Since the TRUNCATE statement has no support either directly or through EXEC in a stored procedure, my guess is that it is a bug that a commit through the EXEC function can be 'worked around' and allowed. The OSS message processor indicated that should you build processes that leverage this workaround, be sure to check if they work after a rev upgrade as this bug may get fixed and your workaround may go away.

Regards,

Justin

[Former Member]

Hi Justin,

I totally agree with your view point about using Commit option. There is only one change in my approach. Instead of using it with in the Procedure we developed a work flow in Data services and after completing every chuck of the procedure let say upload data for only 2011 year, after the procedure is completed we called the EXEC('COMMIT') command from Data services on HANA followed by Merge option. Once this flow for year 2011 was completed the next work flow for 2012 started.

Hopefully this helps.

Regards

PK

[lbreddemann]

Hi PK

Why do you feel it's necessary to call a procedure from DS to perform a commit?

Simply send a SQL command after your initial procedure call.

- Lars

[patrickbachmann]

Thanks everyone.

[former_member182302]

Hi Patrick,

Workaround to use COMMIT is using it with EXEC as shown in this blog:

Also have a look on the discussions in this blog regarding merge:

Regards,

Krishna Tangudu

[patrickbachmann]

Ok finally read the links from Krishna and all the comments back and forth between Lars and Justin and it answered my questions.  Now I will tinker with this and let you know once I have it working.  Many thanks for pointing me to this.  Funny thing is I did indeed do a search for 'commit' before posting this but somehow missed these great discussions.

Thanks,

-Patrick

[former_member182302]

To update you on what we have done for a similar situation:

We have used a generic procedure to load the data into hana. Please find below the sample ( It doesnt have any commits or rollbacks in this )

And we are loading the data in multiple batches with each having a unique number. ( we already had that batch number generation in place, it happens while we generate the files from the source (Teradata) )

And to have a control, when any error occurs ( DELETION successfully completed and INSERT failed with errors resulting in emtpy table ) have triggered commit manually using the work around mentioned.

Again for that we need to maintain one error table to log errors and to send to support team.

Regards,

Krishna Tangudu

[rama_shankar3]

Patrick,

I am assuming that you are using a loop of some kind to write multiple records using a procedure. Please use commit ("COMMIT;") statement after each insert that you want to commit. Hope this helps.

Regards,

Rama