Improving Performance of a multidatabase report

[Former Member]

Hi All,

This is regarding multiple database reprot.

I am getting a query from mysql like this .

SELECT *  FROM OBJSETTING_DATA

and other query from oracle like this.

select country,empno from HO_USERS

and other also from oracle like this

select linemanager,empno from hr_apps

And the parameters are year,division,Status.And I am linking empno using cr links tab

May be I will have some hundreds of records only.

In my report I need show country,empno,name,linemanager,grade,status.

So here country,linemanager coming from oracle and rest of all coming from mysql.

Please suggest what are the steps to follow to improve performance.

[abhilash_kumar · Accepted solution]

Hi Divya,

If you join these Oracle Queries to the MySQL database, you're ought to experience performance issues as the joins are not processed on the database server.

The first step, before you even try anything else, would be to somehow get all these tables in a single database.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash and Sastry,

I did like this instead of linking tables in links tab and somehow I am able to improve performance

Created main report using mysql query..
And created 2 sub reports using oracle db with parameter empno and linking empno field with empno parameter using sub reports links tab and placed the sub reports in details section of main report as per my requirement

I am getting somewhat better performance compared to earlier.

Please suggest

[abhilash_kumar · Accepted solution]

If you see an improvement in performance that's great!

You could probably squeeze out more performance by getting rid of fields like Page N of M (if you've used it) or by making sure whatever record selection criteria you've defined is defined in the where clause of the queries instead.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Please clarify me on this.

Now I have the report with coming from 2 different databases(oracle and MYSQL)

My client will integrate this report with one java application.

So how they will connect to 2 different databases from application to get data into report.

What they will do .

Please suggest

[Former Member · Accepted solution]

Hi All,

Can somebody please suggest me on this ?

[abhilash_kumar · Accepted solution]

This is something that you'll need to ask in the Java Space, Divya.

-Abhilash

[former_member205840]

Hi

You can make one query from Oracle to reduce performance hit like :

Select country,empno, linemanager from Hr_apps h, Ho_users u where h.empno=u.empno

and use other query to join with this and try to run the report.

-Sastry

[former_member205840]

Hi

Use below sql.. and see..

Select u.country,u.empno, h.linemanager from Hr_apps h, Ho_users u where h.empno=u.empno

-sastry