Crystal XI and VFP OLEDB have killed my report speeds!

[chriscboy]

Hi All,

We have recently upgraded our crystal reports from using 8.5 and VFP ODBC connection to using XI and VFPOLEDB driver. We are using Visual Foxpro 7 SP1.

Since the upgrade some reports run incredibly slower. I did some further investigation and it appears that it only occurs on reports which have date fields as parameters to filter the data.

I have compared the sql queries that crystal generates between the 8.5 and XI reports and it appears that in XI reports crystal is removing the WHERE syntax for the date selection, so it then returns a massive data set before filtering out the data.

Is there anyway from prevent this from happening, as our users (rightfully) are not happy with this "upgrade" that I have carried out?

I was under the impression that OLEDB was superior to ODBC in terms of speed/functionality, but I have seem to have gone backwards!

Any help greatfully recieved

 

[MJRBIM]

I don't think it's a VFPOLEDB driver problem. I have had similar problems with Oracle-native reports converted from CR-8.5

The SQL-generator model changed in CRYSTAL REPORTS between 8.5 and 10, and some SQL seemed to get "broken". Mostly where we had made custom SQL changes to the query in CR-8.5.

There were two possible solutions that both seemed to work...

1.) Change the CR-8.5 SQL to a COMMAND in CR-XI.
2.) Rebuild the RPT DB/RECORD-SELECT using the CR-XI tools (usually can be done in the same layout).

 

[chriscboy]

Hi,

I will have a look at the COMMAND in CR-XI, whatever that is!!

Thanks

Chris