Reporting on disparate DBs: FoxPro and Oracle

[todder]

I have a report that is pulling data from two different databases: FoxPro 3 and Oracle 8. The report runs extremely slow, if at all. CR seems to be pulling all the possible rows from Oracle and all possible rows from Foxpro and then selecting records from that pool. The "Show SQL Query" window is inactive so the SQL cannot be examined. The report utilizes usere specified parameters so using a view to limit the number of records on the Oracle side is precluded

Does anyone know of any method that may be used to make the report run faster?

We are currently using the ODBC provider to access the Foxpro tables. Would switching to the OLE provider for Foxpro expedite the query?

Thanks for the help.

 

[kenhamady]

Whenever you cross platforms in one report, Crystal has to do all of the work at the client, which is very inefficient. You might actually find that linking the tables via linked subreports is more efficient, depending on what you are pulling from each table. This is because there are separate queries for each database. Can you give a brief description of what you are pulling from each table? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[todder]

From Oracle: 8 fields from one table
From Foxpro: 4 fields from two tables

Nothing fancy, just alot of rows. 10000+ on the Foxpro side.

I tried putting the queries in subreports. Once with Oracle and once with Foxpro. Neither report ran much faster.

Thanks for your help.

 

[kenhamady]

Is there a one to many relationship between the tables?
Which table has the fields with the more specific WHERE clause criteria? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[chelseatech]

Where are the FoxPro tables?

If they are on a remote server, it can be faster to access the data via ODBC rather than Native foxpro drivers. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[todder]

There is no one to many relationship between the tables.

The Oracle side has the more specific where clause criteria. User-entered parameters are used to narrow the selected set to about 900 records.

On the Foxpro side the where clause criteria narrow the selected set to about 10,000 records.

We are delivering the reports via the web with ASP code. The error that occurs is Server.ScriptTimeOut. We are considering a change to the value of Server.ScriptTimeOut on the IIS server to allow the script more time to run.

Any advice?

 

[kenhamady]

So it is a one-to-one relationship? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[todder]

yes

 

[kenhamady]

The quickest would likely be to create the container report to pull the 900 records from Oracle, and then have a linked subreport to pull the FoxPro records that match each of the Oracle records. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[MrJC]

I'm in a similar situation (in my case it's MS-SQL and Oracle). I've found the best way (for me) is to generate stand alone SQL and then bind to Crystal.

Not the best solution - mainly cause it involves VB with Crystal, but I've found it fast (er

Jon

 

[todder]

How do you create stand alone SQL and bind it to crystal? Do you use the Crystal SQL Designer?

I tried to run my report with the Foxpro side in a subrport that is displayed on each Oracle detail line. I poweered down my pc and left for home after one and a quarter hours.

 

[kenhamady]

todder,

Well, that approach might not have helped. Are you sure you linked the subreport so that each detail only retrieves matching records?

If you create a view with user parameters in Oracle, are the parameters not exposed to the report? The reason I ask is that Stored Procedures in SQL Server can have parameters that are seen by a Crystal Report. If you were using SQL Server you could probably have the SP retrieve the records from FoxPro and then present the combined results to the report. I am not sure that Oracle has the same capabilities, but it seems like it would. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com