When retrieving data from a Progress 9.1d database via ODBC and SQL the performance will be negatively impacted by the complexity of the query. Progress isn't good at the same things that Oracle and SQL Server are good at. For example, SELECT COUNT(*) FROM PUB.TBLNAME will be very slow on a large Progress table, but the same query would be fairly quick on Oracle or SQL Server. Here's another silly example. SELECT * FROM PUB.TBLNAME WHERE 1<>1 executes very slow on a large Progress table because Progress will scan the entire table, blindly comparing 1 to 1 for each row. A true relational database will evaluate the condition prior to executing the query and deduce that no rows will pass the condition.
Furthermore, the ODBC driver is flawed. When selecting data from a Progress table containing a large number of columns of various datatypes, sometimes the driver will return an error indicating that there was a problem converting a Date value to a SQL Server datetime. However, the true problem will be that one of the character columns contained a value with a length greater than the column's SQL Width in Progress.
Just last week I encountered a problem where the table I was importing into SQL Server from Progress contained 16,400 rows. The SQL command was simple: SELECT * INTO TBLNAME FROM PUB.TBLNAME. The result, though, was that only 9,200 rows came across. We have multiple SQL ports setup to the Progress database, so I tried the same query through another port. This time only 8,300 rows were returned. The problem turned out to be a character column containing values longer than the SQL Width. However, no error condition was indicated.
My advice is: Keep the SQL commands sent to Progress as simple as possible. Check the record counts to ensure that the data copied to SQL Server is complete. Do the complex joining, conditional testing, and data massaging on SQL Server.
Furthermore, the ODBC driver is flawed. When selecting data from a Progress table containing a large number of columns of various datatypes, sometimes the driver will return an error indicating that there was a problem converting a Date value to a SQL Server datetime. However, the true problem will be that one of the character columns contained a value with a length greater than the column's SQL Width in Progress.
Just last week I encountered a problem where the table I was importing into SQL Server from Progress contained 16,400 rows. The SQL command was simple: SELECT * INTO TBLNAME FROM PUB.TBLNAME. The result, though, was that only 9,200 rows came across. We have multiple SQL ports setup to the Progress database, so I tried the same query through another port. This time only 8,300 rows were returned. The problem turned out to be a character column containing values longer than the SQL Width. However, no error condition was indicated.
My advice is: Keep the SQL commands sent to Progress as simple as possible. Check the record counts to ensure that the data copied to SQL Server is complete. Do the complex joining, conditional testing, and data massaging on SQL Server.