Latset ODBC driver

[BCM]

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.

 

[Tarby777]

Hi,

Does your ORDER BY clause reflect the indexes on the tables? It's one thing to have indexes on all the columns in your search criteria, but if the sort order is something totally different to the order in which the data is initially returned from the database then you could see a big slowdown...

 

[BCM]

Do not have the ORDER BY performed by the Progress database. Have it performed by the database into which the records are being retreived.

 

[ZombieBritts]

I agree with the above posts , just move the raw data to your SQL Server and build your queries in Crystal Reports. If that moves way too much data , then I would write a program in Progress to dump out the required data, that runs locally to the database , and have the ODBC just grab all of that.

Have you tried your queries on the Progress database directly to rule out your ODBC connection ?

Hope this inspires a working solution.

 

[JJames100]

I have found from using ODBC to make queries against Progress from Access, that I have to :
1. make sure I include all the fields that are part of the primary key - even if I don't need them in the query.
2. know how the Progress fields have been defined in terms of length - I am working against a Progress database where most of the text fields were defined as length 2000, which of course, Access will interpret as a memo field. Besides the obvious issues that arise, many of the queries would end with an error indicating problematic size issues. Also, we have software written in Progress where frequently, the size constraints for the interface are not enforced, therefore allowing data of greater length to be entered & thereby causing some failures there. There is a parameter that can be set so ODBC will define the field as a shorter length - that helped with a lot of queries...
hope this helps

 

[aviovda]

hello
(i only try).

for the performance i would check first if the joined fields are indexed
properly , than the question is who the sql drivers "sees" this indexes.

thanks.

 

[blackds]

You said you were using SQL Server?...

Use DTS packages to synch strategic tables over to SQL Server on a schedule. Then dump Crystal and use SQL Server Reporting Services.

 

[ericdzhang]

Thanks for getting back to me Casper.

The performance issues Im getting is that when I run a certain report or any kind of complex report it is taking hours to run. The report runs fine for our smaller departments but when I run it for our large main department it takes hours.

Im not saying for sure that it’s the ODBC driver it was just a thought!

Here is my query

SELECT Table_A.Field_A, Table_A.Field_B,
Table_A.Field_C,
Table_A.Field_D, Table_A.Field_E ,Table_A.Field_F,
Table_A.Field_G, Table_A.Field_H, Table_B.Field_I, Table_C.Field_J
FROM {oj (PUB.Table_A Table_A
LEFT OUTER JOIN PUB.Table_C Table_C
ON (Table_A.Field_B=Table_C.Field_B) AND (Table_A.Field_G=Table_C.Field_G))
LEFT OUTER JOIN PUB.Table_B Table_B
ON ((Table_A.Field_G=Table_B.Field_G)
AND (Table_C.Field_K=Table_B.Field_K))
AND (Table_C.Field_L=Table_B.Field_L)}
WHERE Table_A.Field_F='R' AND Table_A.Field_G=39
ORDER BY .... etc

I was hoping it would be a simple matter of updating the ODBC driver. But that theory goes as it runs fine for the smaller departments, however as stated the main departments it takes hours and when I say hours Im talking about 5-6 hours!!!!. Can anyone see anything wrong with my query? Please any suggestions welcome.

Will my report work more efficiently if I run it off SQL server? Whats the best way to integrate my Progress DB with SQL server?

Cheers and thanks to all!!!

Al

please check your database index that you uses is correct ,if you use DB index correctly. it takes you few minutes,otherwise it takes you few hours.

 

[levogiro]

itsAK,

i'm having de same problems. my reports was originally developed using a SQL Server 2000 database, but my customer wanted them migrated to Progress and it's been almost 2 months. my reports used to be very fast to be generated, but now it takes hours to get the work done.

i'm using de same version of crystal reports and the ODBC driver as you are.

i'm not saying progress is not a good database, i don't have enough experience to do this kind of judgement, but i miss the days when my reports took 10 to 20 seconds to be done.

have you found any solutions to this problems ?

any help would do, please.

thanks,

levogiro.

 

[itsAK]

Hello all thanks for all your replies and views. It has been some time since I posted.

For the record the first things I checked where the indexes etc on the joins.....

The problem was not the ODBC connection but that the query and the sheer size of the bulk of data within the tables were causing PROGRESS problems in executing the query. As BCM stated PROGRESS is not as efficient as SQL / ORACLE in processing certain complex queries including LEFT OUTER JOINS / UNION ALL etc....

I have now finished with this project as we now have an extremely functional and efficient reporting process. We use a combination of reporting off of PROGRESS and SQL. I have multiple stored procedures running on the hour importing PROGRESS tables and there selected fields into SQL Server using the standard SELECT statement and then from within SQL Server I report via crystal. This solved all our reporting problems for the more complex queries and I would very much recommend this approach. We have managed to get the best of both worlds using PROGRESS and SQL. After some early teething problems and some minor adjustments we now have an extremely efficient and pro-active reporting process. It will take some time setting up your linked server and depending on the amount of tables and data you wish to import and also how often you wish to do so but if it is done right it can be very effective indeed.

I have now developed our asp.net reporting intranet that updates the tables with “live” data and runs the reports from a click of a button from a user.

Levogiro – You say you originally developed the crystal report pulling of SQL 2000, I presume since transferring this report to pull off of PROGRESS you have checked the JOINS are indexed and all the correct indexes are set up?.... If you had a report that worked efficiently off of SQL and you know want to use the basis of that same report to report of off PROGRESS tables then may I suggest my approach above as that report query is proven against SQL.

A simple stored procedure is all that’s required to import the selected PRGRESS columns into a SQL table.

From reading a lot of the posts on this site on related topics of dumping/importing/copying data out of PROGRESS into SQL/ORACLE (I get a lot of PMs regarding this) I sense a lot of reluctance from new PROGRESS users to actually understand PROGRESS and they just want a quick fix. When I first started at this company I was a complete novice and had never come across PROGRESS before but have taken this opportunity to try and learn the DB and Im working my way through 4GL etc….

That’s all from me at this stage again thanks to all the posts….

BCM – thanks for your advice!

 

[customprogress]

i have found that for complex queries a good way to speed up is
first narrow down the data you want to see and store in a temp / work table. then execute your query off this temp table.
i have done this on many occasions and got drastic improvments.