Array Fetch Size and its effect on Performance of Web Intelligence Report
Degraded performance is the major worry of Business Objects Administrator and Universe Designer. Many times its seen that Performance Optimization is considered as the last activity and given least importance, which some times results in re-work in Universe Design. Its always good to follow Universe Design Best Practices, Performance Tuning Best Practices while designing the Universe. Well there are few Parameters in Universe which can be set any time ( Array Fetch Size should be set at the end of Design with lots of trial-error so that best value can be derived.) Array Fetch Size is one of them.
What is Array Fetch Size?
Array fetch Size defines the number of record to be extracted from database during each fetch.
e.g. If your report query is supposed to fetch 5000 records and Array fetch Size is set to 100 then Business Objects will connect to Database 50 times and retrieve 100 record in each fetch.
Business Objects fetched data from Database in following steps
1. Connect to Database
2. Submit SQL query to execute
3. Retrieve records from Database and submit it to Web Intelligence Server for processing
Considering above example, Business Objects will repeat above steps for 50 times to get 5000 records which is definitely Performance degrading.
If you set Array fetch Size to 5000, these 50 fetch cycle will be reduced to 1 and you will see a Performance gain.
Consideration Before using Array fetch Size
However before setting up Array Fetch Size to higher value make sure that you have adequate memory on Client side to process this large result set.
Default value of Array fetch Size is 20 and when this parameter is not set Business Objects will retrieve data row by row.
Array fetch Size works with all major databases however few folks experienced that setting up Array fetch Size more than 1 for SQL Server databases resulted in wrong results in Reports. So you should check your report as well after setting up Array Fetch Size.
In addition to Array Fetch Size you can also consider setting up Array Bind Size to the optimal value. Well I will have to play with parameter to understand its effect and possible gains.
Subscribe to Email to receive all the FREE updates and Business Objects Interview Questions and Answers FREE!!
Related posts:
{ 1 trackback }