I have a report server that is MSSQL 2008. I get data from an ERP system that is Progress 9.1x. Currently, I connect through an ODBC and pull data over with OpenQuery. This works fine with one exception.
Under load when users are banging away at the ERP, the import slows to a crawl and slow Progress down on some queries. The table I'm hitting has close to a million records. We have put indexes on the table and select only specific records, passing the conditions through OpenQuery.
I'm thinking there has to be a faster way to connect to Progress. The same query using the Progress SQL tool are super fast. That leads me to think the bottleneck is ODBC. On off-peak hours, my queries run within a minute or two. During the day, they take 20-30 minutes.
The question is: Are there more modern methods to connect to Progress than ODBC?