HANA ODBC Writing Performance incredibly slow

Question

Hello guys,

we are facing some problems writing to our HANA databases with ODBC. Reading is fast and does not bring up any issues. Writing takes over an hour for 100k records. It seems to be like this because of the way Alteryx handles the inserts into the database row by row. Is anyone else facing the same issue or has some suggestions on how we could improve the writing speed?

Thank you!

Niklas

apathetichell · Answer

Are you using Hana or S4 Hana (Cloud or on prem)? Basically this sounds like you are making one api call per write action. This happens with some ODBCs (REDSHIFT - LOOKING AT YOU). It's usually based on the internal APIs and the implementation. The workaround is that there's usually a filestore/location you can bulk write to - > and then run an insert command from. Sometimes this is included in the ODBC -> sometimes it's not. For Redshift this runs via S3. These are usually (usually) not Alteryx specific issues but more related to the underlying internals of the ODBC - this is code for - google your specific issue with your specific driver and see what pops up.

CailinS · Answer

@niklas_greilinger I have two suggestions as you explore options here. First, I saw this thread on Community https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Write-into-DB-using-Bulk-performance-issue/m-p/1349533#M335319 where folks were discussing various approaches and then second, I searched the Knowledge Articles specifically (found in my.alteryx.com and through Community integrated search) and found this article: https://knowledge.alteryx.com/index/s/article/How-to-Write-to-Hive-Faster-1583460906020

If these resources don't provide the insights you need, you could open a Support case on my.alteryx.com through the portal and see if Customer Support has any further guidance.