Wednesday, July 24, 2019

Dynamics GP: Does GL batch size affect posting performance? Yes, it does.

By Steve Endow

If you need to post 50,000 journal entries in Dynamics GP, should you create one batch with 50,000 JEs?  Or should you create 5,000 batches with 10 JEs each?

If you have the option to import 10,000 JEs with 2 lines each, or a single JE with 20,000 lines, which should you choose?

These are the exciting questions that I ask myself on a regular basis.

I imported a truckload of standard GL journal entries into Dynamics GP to test performance and record some posting benchmarks to understand how GL batch size and JE size affects Dynamics GP GL posting performance.

I was surprised at the results.

Note:  My tests were performed on the TWO / Fabrikam sample database.  In theory, tests on TWO should be reasonably valid and should extrapolate to larger production databases. But in reality, there could be production databases that exhibit very different behavior than what I found with TWO.  For example, any customizations, triggers, or third party products could dramatically change the results.

If you just want the results, here's the TL;DR:  GL batches with more JEs posted at a rate that was 2-3 times faster than batches with fewer JEs.  And journal entries with more lines also posted at a rate that was up to 50% faster than JEs with fewer lines (up to 300 lines per JE).

These are my results of posting different GL batches sizes.  All of the JEs had 4 distribution lines.

100 JEs (400 lines):  4 seconds - 25 trx/sec
500 JEs (2,000 lines):  11 seconds - 45 trx/sec
1000 JEs (4,000 lines):  16 seconds - 62 trx/sec
2000 JEs (8,000 lines):  30 seconds - 66 trx/sec
5000 JEs (20,000 lines):  55-63 seconds - 80-90 trx/sec **

** I repeated the test multiple times for each batch size and recorded very consistent results, except for the 5,000 JE batch.  Given it's size and longer posting time, there was some variance in posting time for the large batch, but the 5,000 JE batch definitely posted at a faster rate than the smaller batches.

Next, I tested posting a batch with 100 JEs, but increased the number of lines in each JE.

100 JEs with 100 lines each (10,000 lines):  17-18 seconds - 555-588 lines/sec
100 JEs with 200 lines each (20,000 lines):  26-31 seconds - 645-769 lines/sec
100 JEs with 300 lines each (30,000 lines):  41-48 seconds - 625-731 lines/sec
100 JEs with 500 lines each (50,000 lines):  91-124 seconds - 403-549 lines/sec **

I recorded multiple test results for these large JE tests, as I saw higher variance in posting times (similar to the 20,000 line batch presented above), so I have noted a range of posting times and rates.

For reference, here is the line info for 4 line JEs from my batch size tests:

500 JEs (2,000 lines):  11 seconds - 181 lines/sec
1000 JEs (4,000 lines):  16 seconds - 250 lines/sec
2000 JEs (8,000 lines):  30 seconds - 266 lines/sec
5000 JEs (20,000 lines):  55-63 seconds - 317-363 lines/sec

The posting rate of JE lines/sec increased with 200 line JEs, but seemed to plateau somewhere between 200 and 300 lines, as the 300 line JEs show a slightly lower rate.  And when I tested 500 line JEs, the performance dropped significantly.  Given how much lower the rate was with 500 lines, this may be due to some type of SQL issue that affected the testing.  But up until my test of 500 line JEs, I saw a clear trend of faster posting rates with larger JEs.

And compared to the test with 4 line JEs, you can see that the 100-200 line JEs post at a significantly faster rate.

Based on these non-comprehensive tests in TWO on one dev GP machine, it seems that the Dynamics GP GL posting process benefits from larger JE batches.  And it seems that the GL posting routine processes faster with larger JEs.  Perhaps there is some base overhead to the GL posting process, and the process gains efficiency with larger workloads?

If you have observed additional or different GL batch posting performance in Dynamics GP, comment below and let me know what you've observed.

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics GP, Dynamics 365 Business Central, SQL Server, .NET, Microsoft Flow, PowerApps, and Azure.

You can also find him on Twitter and YouTube

No comments:

Post a Comment

All comments must be reviewed and approved before being published. Your comment will not appear immediately.

How many digits can a Business Central Amount field actually support?

 by Steve Endow (If anyone has a technical explanation for the discrepancy between the Docs and the BC behavior, let me know!) On Sunday nig...