From 11.1.2.2.303 there is yet another planning property available called GRID_PARTIAL_FETCH_SIZE which provides the ability to set the size of the data grid at that is returned to client when a form is opened, this property has been added because there are possible performance issues when scrolling past the default size.
Please note this property is only aimed at ADF enabled applications and is set at application and not form level.
By default when a form is opened it will send 25 rows and 17 columns of data, if you then scroll past that size a message will be displayed saying “Fetching Data” and planning will send the next 25 rows and 17 columns to the client.
This loading of the next set of data can be potentially be slow which is no go for users wanting access to the data quickly.
When a form is opened the full dataset is retrieved from the Essbase database, if you look at the log you will see only one reference to a retrieve and no subsequent retrieves after scrolling past the grid size which means the data must be cached and pushed to the client as required.
[Sun Apr 14 18:49:07 2013]Local/PLANSAMP/Consol/EPMADMIN@FUSION/3448/Info(1020082)
Spreadsheet Extractor Elapsed Time : [0] seconds
To change the default grid size the property GRID_PARTIAL_FETCH_SIZE can be added and the value is based on “row size, column size” so 50, 30 would mean 50 rows and 30 columns are sent to the data grid.
Remember to restart the application server after making any property changes.
So you are probably asking what the magical setting is, well like many properties there is not one that will suit everyone and it is a trade-off between client processing times and fetching data delays.
If you take a large form and set the grid size based on that (you can find the size from running Tools > Diagnostics > Grids) then test the performance at the client side to see if it is acceptable, if is not acceptable keep reducing the grid size until a happy medium is reached. It is also worth mentioning again that this set at application level which means it will affect all forms and all users so be certain before implementing.
Please note this property is only aimed at ADF enabled applications and is set at application and not form level.
By default when a form is opened it will send 25 rows and 17 columns of data, if you then scroll past that size a message will be displayed saying “Fetching Data” and planning will send the next 25 rows and 17 columns to the client.
This loading of the next set of data can be potentially be slow which is no go for users wanting access to the data quickly.
When a form is opened the full dataset is retrieved from the Essbase database, if you look at the log you will see only one reference to a retrieve and no subsequent retrieves after scrolling past the grid size which means the data must be cached and pushed to the client as required.
[Sun Apr 14 18:49:07 2013]Local/PLANSAMP/Consol/EPMADMIN@FUSION/3448/Info(1020082)
Spreadsheet Extractor Elapsed Time : [0] seconds
To change the default grid size the property GRID_PARTIAL_FETCH_SIZE can be added and the value is based on “row size, column size” so 50, 30 would mean 50 rows and 30 columns are sent to the data grid.
Remember to restart the application server after making any property changes.
So you are probably asking what the magical setting is, well like many properties there is not one that will suit everyone and it is a trade-off between client processing times and fetching data delays.
If you take a large form and set the grid size based on that (you can find the size from running Tools > Diagnostics > Grids) then test the performance at the client side to see if it is acceptable, if is not acceptable keep reducing the grid size until a happy medium is reached. It is also worth mentioning again that this set at application level which means it will affect all forms and all users so be certain before implementing.
This is very helpful. "Fetching..." will display when picklist contain a large number of members. Is there a setting for this also?
ReplyDeletewill this setting help to increase the fetch size during dim view, Security set up if there are many groups to select from etc or does this only impact the forms?
ReplyDeleteThe fetch size is for forms.
ReplyDeleteWe have added the GRID_PARTIAL_FETCH_SIZE property and set the value to 768,24 yet there is no change at all to the "Fetching data" behavior in our forms following an app server restart. Presently on Planning 11.1.2.3. Is there some other setting/config that needs to be done?
ReplyDeleteIn 11.1.2.3 it is set through Administration, then Application, then Settings > Current Application Defaults > Display Options
ReplyDeleteI realize this is ancient news by now but I'm seeing the same behavior as Tim. I've set the property in Display Options to 100,50 but it's still fetching data practically every time a user scrolls. Users really don't like this behavior.
ReplyDeleteI don't want totally disable the ADF framework over something so minor. I suppose I'll be opening a ticket.
Patrick - what seemed to work for us was increasing the column number to a higher value than you would think based on the number of columns displayed in the form. Our form had several summary columns that were each in essence 12 members but only displaying 1; once I correctly accounted for those and increased the column number to 60, we started to see the correct behavior. One other thing to note, we are patched to 502 now, so maybe that had something to do with it as well.
ReplyDelete