Skip to Content
0
Jun 30, 2010 at 03:22 PM

Need connectivity performance advice with MS SQL / Oracle Sourced Report

20 Views

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...