Slow performance in PAfE

[sg9489]

Hi All,

I am currently testing the performance of the PAfE reports. Below is a simplified simulation of the report:

Columns A to D → Excel formulas referencing below set of columns (e.g., A1 = G1, B1 = H1, etc.)
Columns G to J → Values copied from below set of columns and pasted as static values
Columns K to N → =IF(range=1, DBRW(...), 0)
range is a named range scoped within the sheet

Note: The Excel calcualtion is Manual and not automatic and above copy-paste happens after running a macro.

The last set of columns (K to N) contains a large number of cells, and I suspect this is contributing to the slow calculation time.

This setup was performing well on-prem with PAfe, but it is significantly slower on the cloud.

Is there a way to eliminate the IF condition and use direct DBRW calls instead, or any recommended approach to improve performance in this scenario?

Why are PAfE reports slow on cloud than on prem?

Thank you.

[jim wood]

Model performance aside, my guess based on what you asked would calculation order in the excel sheet. You've got a lot of stuff happening in a set order.

[burnstripe]

The slower performance of cloud vs on prem could be potentially down to increased latency between your machine and the server. The cloud environment will have a higher latency and thus there will be a greater delay between individual requests.

DBRW would typically be fetched in bundles minimising the frequency of this delay but if it is being enclosed by an IF then this is potentially forcing each cell to be fetched individually, meaning that the clouds longer delay is encountered frequently and can become time consuming

I suggest testing the performance with just dbrw in place without the surrounding if statement and see how that fares

Regarding advice on removing the if statement we would need a bit more clarity on what the if statement is looking to as from the example it's existing purpose isn't clear