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!
ODBCOutput Insertion to HANA Performance
- garry cook
- Community Contributor
- Posts: 209
- Joined: Thu May 22, 2008 7:45 am
- OLAP Product: TM1
- Version: Various
- Excel Version: Various
- gtonkin
- MVP
- Posts: 1192
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: ODBCOutput Insertion to HANA Performance
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.
Have you tried batching multiple insert statements rather than just the values?
e.g.
Code: Select all
Insert into TM1FCE ([Version], [Source], [Period], [Country], [ProfitCentre], [CostCentre], [Currency], [Account], [Value] )
Values('blah','blah','blah','blah','blah','blah','blah','blah','882.09');
Insert into TM1FCE ([Version], [Source], [Period], [Country], [ProfitCentre], [CostCentre], [Currency], [Account], [Value] )
Values('blah','blah','blah','blah','blah','blah','blah','blah','77.22');
Insert into TM1FCE ([Version], [Source], [Period], [Country], [ProfitCentre], [CostCentre], [Currency], [Account], [Value] )
Values('blah','blah','blah','blah','blah','blah','blah','blah','99.77');
...
Worth a try whilst waiting for someone else to add their 2 cents.
- garry cook
- Community Contributor
- Posts: 209
- Joined: Thu May 22, 2008 7:45 am
- OLAP Product: TM1
- Version: Various
- Excel Version: Various
Re: ODBCOutput Insertion to HANA Performance
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.
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.
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: ODBCOutput Insertion to HANA Performance
Thanks for sharing Garry.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 13
- Joined: Mon Mar 09, 2015 11:54 pm
- OLAP Product: TM1
- Version: 9.1 to 10.2.2
- Excel Version: Office 2010
Re: ODBCOutput Insertion to HANA Performance
Thank you!!! I just implemented this and it will definitely increase our efficiently of using file dumps
-
- Posts: 12
- Joined: Thu Dec 07, 2017 11:17 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0
- Excel Version: 2016
Re: ODBCOutput Insertion to HANA Performance
Give this guy a medal! Thanks!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:
...