I have deployed CDATA Sage Intacct ODBC Driver on an Azure Virtual Machine that also hosts a SQL Server for which we want to copy data from Sage Intacct to table(s) on SQL Server. I have set up my environment 2 ways described in the bulleted list below and BOTH options experience very high latency and slowness when trying to query tables and views created within the Sage Intacct data model; I experience especially very high latency and slowness when querying view, GLDetail, from the CDATA Sage Intacct ODBC Driver:
- CDATA SQL Gateway/Linked Server: I set up the ODBC Sage Intacct Driver as a service in SQL Gateway and set up a Linked Service on the SQL Server instance that lives on the same machine the SQL Gateway sits on. I followed the steps in the below articles to set it up and also applied caching which did not seem to aid in the increase in throughput to get the data from Sage Intacct.
- Set up for SQL Gateway: CData SQL Gateway - Services
- Set up for Linked Server using SQL Gateway: https://www.cdata.com/kb/articles/sqlgateway-linked-server.rst
- CDATA ODBC Driver/Azure Data Factory/Self-Hosted Integration Runtime: I set up the ODBC Sage Intacct Driver, installed the Azure Self-Hosted Integration Runtime on the same Azure Virtual Machine, and connected the ODBC driver as a Linked Service within Azure Data Factory. Below contains a sample image showcasing the slowness of throughput when trying to use a copy activity for GLDetail. Example of latency are highlighted here:
-
Slowness in Data Reads: 2 million records take OVER 7 HOURS to successfully pull from the Sage Intacct endpoint.
-
Throughput is slow: The highest throughput we reached was only 60 KB/s which is very slow.
-
I have already attempted to optimize the copy activity using this article from Azure and STILL have no success increasing throughput: Copy activity performance and scalability guide - Azure Data Factory & Azure Synapse | Microsoft Learn
-
The CDATA Helpdesk did not know how to help in this case (Case # CS-SS00114441), so I’m hoping by reaching out to the community, I could get some assistance from others who may have also experienced this issue. Any suggestion to help improve performance of extracting data from the CDATA Sage Intacct ODBC Driver using above architecture patterns would be very helpful.
Thank you!
Lee