TM1 Forum

Running into an issue with awful performance pushing data from PA Cloud into HANA via Remote Gateway. Raised a ticket with IBM but reaching out to the hive mind also as suspect this is more to do with our HANA syntax knowledge.

Overview of Process:

Prolog:
ODBCOpen

Data:
ODBCOutput using SQL Insert statements

Epilog:
ODBCClose

Issue:
Using an Insert statement in conjunction with the TM1 ODBCOutput function takes 22 seconds to SQL Server but 11.5 mins to HANA.

When writing to a SQL server, we batch up the lines in the following manner:

Insert into TM1FCE ([Version], [Source], [Period], [Country], [ProfitCentre], [CostCentre], [Currency], [Account], [Value] )
Values('blah','blah','blah','blah','blah','blah','blah','blah','882.09'),
('blah','blah','blah','blah','blah','blah','blah','blah','77.22'),
(('blah','blah','blah','blah','blah','blah','blah','blah','99.77'),
Etc,
Etc

Batching in this manner results in a runtime of 22.22 Seconds to update the SQL Server. Non batched performance is similar to the poor Hana performance.

When trying to write the same data direct to a SAP Hana table, we can't use the same methodology as with a SQL server as similar batching syntax does not appear to be available in SAP Hana.

Anyone come across this and found a solution to improving performance or any Hana experts that can point me in the right direction for appropriate syntax? A combination of our Hana DBA's knowledge and the might of Google haven't turned up any suggestions yet.

Any suggestions welcome!

Long time Garry!

Have you tried batching multiple insert statements rather than just the values?
e.g. I bucket 100 to 200 of these when using ODBC but not sure if it will help on HANA.
Worth a try whilst waiting for someone else to add their 2 cents.

For reference for anyone who comes across this on a search in the future, the solution to this was Hana syntax for the batching as follows:

INSERT INTO "dummyname"."dummyname"
select NOW(),'blah','blah','blah','blah','blah','',-5.781 from dummy union
select NOW(),'blah','blah','blah','blah','blah','',-1.399 from dummy union
select NOW(),'blah','blah','blah','blah','blah','',-1.399 from dummy;

This allows the query to be batched in the same way that a normal SQL update would do but is syntax that actually exists in HANA.

The performance improvement was in line with normal SQL batching - 19k lines going from around 18 mins down to 15 seconds.

Thanks for sharing Garry.

Thank you!!! I just implemented this and it will definitely increase our efficiently of using file dumps

garry cook wrote: ↑Thu Jul 23, 2020 8:10 am For reference for anyone who comes across this on a search in the future, the solution to this was Hana syntax for the batching as follows:
...

Give this guy a medal! Thanks!