Need connectivity performance advice with MS SQL / Oracle Sourced Report

[Former Member]

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

[former_member11696]

Hi Fuskie,

A couple of things to note:

{ts '2010-01-01 00:00:00'} AND Date2 < {ts '2010-03-31 00:00:00'})

Should be: {ts '2010-01-01 00:00:00'} AND Date2 < {ts '2010-04-01 00:00:00'})

It's not checking the last day of the month of march

Second is you have the filter in the both SQL and Selection:

WHERE BankorGL = 'G' AND (Date2 >= {ts '2010-01-01 00:00:00'} AND Date2 < {ts '2010-03-31 00:00:00'})

{tblmasterboth.BankorGL} = "G"

and {tblmasterboth.Date2} in {@Quarter Start} to {@Quarter End}

You don't need both.

Likely what is happening is for ever record in the selection formula returned from MS SQL the query to Oracle runs scanning every record again, yes this is going to be very slow.

We always recommend using a subreport if another data source is required. You should be able to link to the sub report and filter both.

You may want to check MS SQL Server help file. I believe it has the ability to link to other databases within the Server itself. May not work for you in this case but something to look into.

Thank you

Don

[Former Member]

Sorry for the confusion. The above record selection produced the above SQL query. The filter is not duplicated.

Fuskie

Whose problem is that the filter is based on rules from both databases, which limits his ability to use a subreport...

[former_member11696]

Hi Fuskie,

All I can suggest is to either try linking the database within MS SQL Server, I believe it has the ability to do this, or use a subreport. It may be quicker than what you are doing.

There are other Products from SAP Crystal that may work but they are standalone or require BOE to get them. Universe of Data Integrator etc....

Thanks

Don