Oracle & SQL datasource in single report

[Flopper]

Hi,

We are currently migrating data from SQL Server 2005 to Oracle 10g. I've been tasked with creating a simple data integrity report that e-mails users if a variance between an Oracle view and a SQL view exists. We are using BO Premium and Crystal XI.

The report links the Oracle view and SQL view by a MonthID field. Each view contains 6 similar fields and displays only 88 records (months), although they both have to query considerably large DBs.

The Oracle SQL executes in 00:06:45 minutes (TOAD) while the SQL executes in 00:02:36 minutes (Mngt Studio).

The problem i'm having is that within both Crystal, and during Scheduling in BO, the report takes 20 minutes. Each view executes in similar minutes in individual Crystal Reports.

I understand that each SQL statement will be executed sequentially, i.e. Oracle first, then SQL. But i don't understand why it takes a further 10 minutes to complete the linking and retrieve the data. I guess it's the way Crystal links the returned data sets...

Could someone please offer an alternative method or at least explain how Crystal joins data from different datasources locally.

Many thanks,

 

[Turkbear]

Hi,
If the views are linked Oracle -->SqlServer for instance,
for each record returned by the Oracle view a table scan of the SqlServer database is made, which could take that 2 minutes to return the row needed...Multiply that by the # of rows from Oracle and that may explain the time it takes..

Each run against its 'own' database can use all the indexing and other fast retreival methods of that database engine..when linked, it is possible that none of those advantages will apply.

It may be one time when using a subreport is better than a linked heterogenous structure.

Perhaps you can import ( not link) both tables into Access and report from there...









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Flopper]

Thanks Turkbear,

I'd actually tried the subreport approach. I created shared variables in order to pass the SQL data to the main 'Oracle' report. However... it was then that i realised that formulas with shared variables cannot be selected as a Condition within the Alerting functionality. i.e. If one value didn't match the corresponding value in the subreport.

I've already spoken to colleagues about sending one of the views data into the other DB and reporting from there. But then that includes more documentation etc... and we were after a quicker solution.

Thanks for your response all the same.