Crytal Reports & Progress Database

[levogiro]

hi,

i have a lot of reports developed with Crystal Reports and SQL Server via ODBC.

now, i have to migrate their data from SQL Server to Progress. i've migrated some of my reports but the performance is awful, really slow and i don't know what to do.

i'm using Crystal Reports 10 with Service Pack 3 and a Merant ODBC driver (9.60) to connect to Progress.

does anyone know what i could do to improve performance ?

thanks.

 

[regulatre]

No doubt these reports were optimized to the original SQL DB and not the Progress SQL... For starters I would crack open the OpenEdge database optimization guide. It's in the documentation section on the Progress web site. The file name is something like tuning_oe_rdbms_ex_06_final.

Some companies have also worked around this type of problem by writing a CSV export in Progress (easy) that extracts and filters the data from the Progress DB using native 4gl code. The data in CSV format is then read in by the report and displayed to the user. The Crystal report would use a text ODBC drive that you'd have to create where the report will be viewed.

hi,

i have a lot of reports developed with Crystal Reports and SQL Server via ODBC.

now, i have to migrate their data from SQL Server to Progress. i've migrated some of my reports but the performance is awful, really slow and i don't know what to do.

i'm using Crystal Reports 10 with Service Pack 3 and a Merant ODBC driver (9.60) to connect to Progress.

does anyone know what i could do to improve performance ?

thanks.

 

[levogiro]

regulatre,

i don't understand. sure, originally the reports were developed in an ODBC driver for SQL Server but i'm now using an ODBC driver specific for Progress and i think it should be working the same way it was doing for the other database.

are you telling me that there is no ODBC driver good enough to extract data from Progress ?

i'm sorry if i sound a little upset, but i've been working with Crystal Reports for almost 7 year and this is the first time i see such poor performance in one of my reports.

thanks for you reply.

 

[regulatre]

The information I gave you is the most you're going to get from anybody on this forum. It will be a waste of your time to take this any further.
This will be my last post to this thread.
HTH

regulatre,

i don't understand. sure, originally the reports were developed in an ODBC driver for SQL Server but i'm now using an ODBC driver specific for Progress and i think it should be working the same way it was doing for the other database.

are you telling me that there is no ODBC driver good enough to extract data from Progress ?

i'm sorry if i sound a little upset, but i've been working with Crystal Reports for almost 7 year and this is the first time i see such poor performance in one of my reports.

thanks for you reply.

 

[kunalpathak]

Hi

Levogiro,

regulatre is right in his answer i was also searching around for a strong and convincing solution but finally we had to compromise by selecting either performance by going with CSV or using direct cr rprts with little low performance anyways if you come accross any solution over this pls. share here i would be thankful to you

thanks

Kunal

 

[joey.jeremiah]

guys, what version ?

 

[levogiro]

joey.jeremiah,

we're using Crystal Reports 10 (i also trying Crystal Reports 11 without any success.)

Progress is 9.1D, with a MERANT 9.60 ODBC driver.

 

[joey.jeremiah]

supposedly, there were teething problems with sql in v9.


could you download 10.1a test drive @psdn.com and give it a try.

i think alot of people would be interested to hear about it

 

[levogiro]

joey,

i'll give it a try. thanks...

 

[levogiro]

hi guys,

i'm back...i talked to my boss and it turns out that right now we can afford to migrate do Progress 10...i don't know all the details but with the hollidays coming it would be risk doing this at this moment.

i looking for another options, but until now i got nothing.

any help will be appreciated.

thanks,

levogiro.

 

[joey.jeremiah]

hi guys,

i'm back...i talked to my boss and it turns out that right now we can afford to migrate do Progress 10...i don't know all the details but with the hollidays coming it would be risk doing this at this moment.

i looking for another options, but until now i got nothing.

any help will be appreciated.

thanks,

levogiro.

hi levogiro,

did you try crystal with 10.1a test drive ?

i don't think that it would be too hard finding progress customers with crystal.

i'd want to speak with someone who's done that first, before commiting to any costs, upgrades etc. like i said i don't think it would be a problem, contact your local vendor or progress.

good luck !

 

[vdennis]

Sorry for jumping in here late, but here is some info that might be useful.

1. Be sure to check out the info on left-outer joins. This is a known problem but in BOXI and OE10 it was improved.
2. All fields that are ''linked" should have some index with the field at the top of the list.
3. In using sub-reports, performance will always slow down as the sub-report has to be looked at for each pass.
4. There is a REAL PROBLEM that can cause your progress session to crash. You would need to be running Provision on the PC and BOXI SP2. The bug has been verified by Progress and duplicated in VB. It has to do with the OCX viewer. A sample code written by Youssif is available if anyone wants to have a look.
5. Check the settings on the ODBC driver, to be sure that the driver is not locking records, or that a locked record could hold up the process. You also have some fine tuning you can try for perfomance issues.
6. If you can , try re-creating a test report based on the same query right from CR and see what happens. If the report runs fine and fast, the problem is not in the ODBC.
-Dennis-
Member VAPUG

 

[levogiro]

dennis,

thanks for your reply...i'm going to check everything you said. we're also going to make a test with OE10 to see if there's an improvement.

levogiro.

 

[vdennis]

Two other things I thought of:
1. Record selection can be a bit of a strain, CR handles this more in the native SQL format than Progress does, so that could be another place to look at. Record selection based on a formula can also be a problem if many records are needed to do the 'math'.

2. This has nothing to do with speed, but it can cause the report to crash so you might want to be aware of this. When using a format, i.e. FORMAT "X(30)", or a number format, you must keep in mind that in Progress this is only for display purposes. If you have a field defined as X(30) you can in fact put as much data in there as the system will allow. It will only 'display' the first 30 charactures. In the transfer over to SQL, progress 'informs' CR that the format is 60 characters long. Guess they built this in as a safty. If CR tries to read a field that is longer it will crash, give you an error message, but I can't remember if it will tell you which field is the problem. If you go into Progress and change the field size, you have to remember just changing the X(30) to X(60) will not help; you will need to go to the field, then click on options, then adjust field width.
-Dennis-

 

[briandrab]

hi,

i have a lot of reports developed with Crystal Reports and SQL Server via ODBC.

now, i have to migrate their data from SQL Server to Progress. i've migrated some of my reports but the performance is awful, really slow and i don't know what to do.

i'm using Crystal Reports 10 with Service Pack 3 and a Merant ODBC driver (9.60) to connect to Progress.

does anyone know what i could do to improve performance ?

thanks.

We have the "exact" same setup and the exact same disappointment and the exact same answers that the others gave. After months of trying different things we were able to get our reports running at an "acceptable" speed by tweaking, playing, etc. Definately something to get used to.

One thing I didn't notice anyone suggest (maybe because it is so obvious...but wasn't for me) is to use a secondary broker for SQL queries. Are you doing this?

 

[vdennis]

Boy, that one really blew past me. Yep, you need to set up two brokers, one for progress, and one for SQL. By changing thoses setting you can improve performance as well. Some other things.

The ODBC, unless you need it otherwise should be set to READ UNCOMMITTED. I have the FETCH set to 300.

Left Outer-joins are a know problem with Progess 9.x and CR. BOXI changed the rules on left outer-joins, so it might be worth while to download their FREE eval of BOXI Rel2 and see if there is improved performace. Be warned there is a know bug with BOXI release 1 and SP2 when using the OCX viewer on a computer with full progress, it may cause Progess to close. I am running OE 10.o b with BOXI SP2 (Which has it's own set of problems).

My ODBC is DataDirect 4.20 that came with OE10. You could try DataDirect and see if they have an updated driver for you.

-Dennis-