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).

C# and SQL and Dynamics GP: Sending a List to a Stored Procedure

By Steve Endow

This is a discussion of how to send a list of values to a SQL stored procedure parameter.

Specifically, how can a .NET application send a comma delimited list of values to a stored procedure to be used in an "IN" operator in a WHERE clause.

Using a parameter with a WHERE clause IN operator

TL;DR: If you want to jump straight to the solution, read this excellent post on Arrays and Lists in SQL Server by legendary SQL Server MVP Erland Sommarskog.

If you crave to learn about all of the exciting details, read on...