How to improve the performance in BIP11.1.1.7.141014 with two the two Data Model sets with two diffe

Question

Summary

How to improve the performance in BIP11.1.1.7.141014 with two the two Data Model sets with two different databases  one AS400(IBM-DB2) and second one Oracle DB 12c.

Content

Hi All,

Thanks in Advance..

I am creating a RTF template In BIP 11.1.1.7.141014 by using the two Data Model sets with two different databases

one AS400(IBM-DB2) and second one Oracle DB 12c.

i am taking one sql from one database(AS400 sql like as a function

select * from table get_udf(pm_dept as decimal(4,0)),(pm_emp as varchar2(10) udf) and

second sql(oracle sql like as select * from emp) is taking from other database and joined together based on the key's in DataModel.

But the report is coming after 15 Mintutes(the agreed SLA for the report time is 1 mintutes).

How to improve the performance in BIP11.1.1.7.141014 with two the two Data Model sets with two different databases

one AS400(IBM-DB2) and second one Oracle DB 12c.

Enabled the options like cache Results also in List of Values

and in the parameters also selected as a refresh other parameters on change user request.

Regards,

Chandra

Thomas Dodds · Answer

oi! ... agreed on an SLA before actually knowing what the performance impact is of cross database joins in XML publisher?

Further than that:  SELECT * ... seriously?  In addition to that ... how is your EMP table configured for performance?  Partitioned?  Indexed?  Is this the live transaction table or a replicated table for data out queries?

Why not have a timed replication from AS400 to Oracle ... then you can tune that destination table as well as the EMP table AND will be stitching the data IN A database (which does it much much better).

I wouldn't be looking at BIP until I had solved/mitigated ALL the other performance bottlenecks.

Venkat Thota - BIP · Answer

When you are using multiple datasets ,there might be performance impact.

did you find any time difference between sql run at database and at data model?

try to tune your queries at database level.Thanks

how much volume of data does each data set has? Thanks