Array Fetch Size limitations

[Former Member]

Hi All,

My environment:

BO 3.1 SP 6

Database Oracle 11g

my issue is, i am working on transactional database, there are billions of rows. I need to show the counts of those in my reports, its taking forever to returning the results(almost 3+ hours)

I am thinking to increase the array fetch size but not sure what the ideal limit is.  Can some one help in this.

Current Array Fetch Size is 250 and Array bind size is 32767  can i increase the fetch size to 500 or 1000?

Thanks,

Praveen.

[former_member4998]

Hi

As above Ajay said, If you increase array fetch size, BO server will consume more memory,

if your BO server not have any issue with consume memory you can increase array fetch size.

And also check other alternative options. Please find the below link for more info.

http://scn.sap.com/community/semantic-layer/blog/2014/05/14/universe-optimization-techniques

[CdnConnection]

Praveen,

     I would NOT recommend making the fetch array larger than 250, otherwise the database & BOBJ server will consume too much memory.   

The bigger question is WHY are you reporting against a transactional database?  You should extracting the transactional data and creating some sort of aggregated reporting database.  The first of BI / DW is NEVER to report against a transactional database.

Regards,

Ajay

[Former Member]

Hi Praveen,

• It is the number of records to be extracted from database at each fetch. E.g if your report query is supposed to fetch 5000 records and array fetch size is set to 100 then business object will connect to database 50 times and retrieve 100 records in each fetch. This will degrade the performance of the reports.
• It is therefore preferred to modify the array fetch size as per the adequate memory on the client side.
• Increase the array fetch size parameter to 1000 so that it will take 18 seconds to fetch 1228000 records from the database.