Optimal Value of Array Fetch Parameter

building semantic layer
1

Hi,

How to check which is the most optimal value for Array Fetch Parameter.
Right now in my connection its 250.I was wondering whether to increment by multiples of 50 or 100 or any other vlaue like 5 or 10 ?

Regards,
Arun

binnu23 :india: (BOB member since 2010-05-21)

2

You just have to try the various settings. They are different for each installation, as the following factors need to be taken into account.

Size of report data providers.
Network bandwidth.
Database Server Memory.
Complexity and number of queries executing at the same time.

Bear in mind that you have to test this over a number of reports. Some may execute faster with one value set, some may execute slower with the same setting.

Mak 1 :uk: (BOB member since 2005-01-06)

3

Depending on how your universe is configured the value on the connection may be ignored. According to SAP Note 1464707 the value you enter on the connection is ignored unless you turn off the fetch size optimizer with a universe parameter.

I tested this and found that the fetch size was different with every query when using the optimizer.

jwhite9 :us: (BOB member since 2006-07-28)

4

Thats very interesting to know, thanks for sharing :slight_smile: .

Mak 1 :uk: (BOB member since 2005-01-06)

5

it means value for “DISABLE ARRAY FETCH SIZE OPTIMIZER” should be “YES”.

Am I correct in saying this ?

Can I keep value of array fetch size as 500 ?
What would be its impact ? Any negative impact of keeping this big value ?

binnu23 :india: (BOB member since 2010-05-21)

6

I’ve discussed all the elements above. You need to test this thoroughly, as you could impact the performance of the whole system, BO and data base.

Mak 1 :uk: (BOB member since 2005-01-06)

7

This is good information. :+1: I was unaware of this also. I’ll definitely have to take a look at our universes because we have a lot that were migrated up and probably don’t have this setting.

8

:?: How did you test this? I want to run some tests on some of our universes.

I am looking into this because we have several universes that were created prior to version XIR2fp3.7 and don’t have this setting. I’m not so interested in putting it in to limit the array fetch size in the connection as much as I want to make sure it is enabled to see if the performance improves.

:?: In your testing, did you see any array fetch sizes close to 1000?
:?: Did you test changing the parameter *.COD file for your database software?

I know every use case will be different, I’m just looking for other’s experience on this to gauge how much effort I want to put into exploring this.

If it improves performance, I’m all for it. :mrgreen:

9

Hello,

Please go through following thread and specifically look at the explanation given by Dave Rathbun…

https://bobj-board.org/t/15651

Kind of explanation that Dave has given is Too Good :smiley:

Regards,
Chinmay Athavale

chinmay1383 :india: (BOB member since 2010-05-11)

10

My testing was very simple. All I did was look to see whether the array size changed with different queries and, if I set the parameter to disabled, that the array fetch size matched what was set on the connection. I have not had time to test whether performance was any better/worse with the optimizer enabled.

My universes were migrated from XI R2 and do not have the parameter defined. To see the fetch size for a given query I enabled tracing on the Webi Processing Servers and reviewed the log files.

I did not change any files related to the database software. Here is a sample message from the Webi trace logs

Optimization of connection array fetch: Original: 1, Optimized: 1260

jwhite9 :us: (BOB member since 2006-07-28)