I have a report requirement to pull data from both Oracle and Microsoft SQL databases. Both are using ODBC data connections. The report is complicated by the fact that the SQL table "tblmasterboth" is the primary table in the report but both are used in record selection. The Oracle data source is actually a custom SQL command.
SELECT date2, SiteAN, Description, CheckNumber, Date, Amount, BankorGL, Text1, fldStatus, Text3 FROM tblmasterboth WHERE BankorGL = 'G' AND (Date2 >= {ts '2010-01-01 00:00:00'} AND Date2 < {ts '2010-03-31 00:00:00'}) select DEPOSIT_COMPANY_GROUP, DEPOSIT_REPORTING_PAYROLL, LIABILITY_PAYROLL_CODE from MV_LIBH
My record selection is as follows:
{tblmasterboth.BankorGL} = "G" and {tblmasterboth.Date2} in {@Quarter Start} to {@Quarter End} and {tblmasterboth.SiteAN} = {sql_MTAX.LIABILITY_PAYROLL_CODE} and {sql_MTAX.DEPOSIT_COMPANY_GROUP} = {?Deposit Company Group}
The report runs EXTREMELY slow, with accessing the database freezing up the Crystal Reports Designer to the point where it is not responding for whole minutes. The hangup appears to be accessing the database. I know accessing multiple databases in a single report is not ideal, but I am surprised by these performance results. Is there something else I can do?
Fuskie
Who notes the tables are not linked in the Database Expert since the SQL Query produced cannot link them...