Customer aging snapshot batch job speed

(0) Share

Report

Posted on by Community Member

We are running the customer aging snapshot (sales ledger>Periodic>Collections>Customer ageing snapshot). I have already split so it is running for batches of 50,000 customers but is still taking over an hour to run, does anyone have any experience with this, or any ideas for it to run faster?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Brian Kinser 815 on at

Like (0)

Report

Only an hour.....it actually is doing a lot behind the scenes...you can add batch servers to your batch group, the more muscle you throw at it, etc,...many clients have separate batch AOS that only run these type of ong running tasks, many have multiple batch servers...I usually run the snapshot at night and only about once a week....otherwise i would create a BI cube for this type of activity (AP/AR aging are just as bad) and use MR or SSRS.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

I had never run it before, so I tried it a few times after reading this thread, and watched its performance with several tools.

It basically runs a customer account statement for every customer, which is not the most efficient process, but it does a lot under the hood as Brian mentioned.

When run in batch, it creates a batch task for every customer "bundle", which is defined in class CustAgingSnapshot method classDeclaration as [tag:define].CustomerBundleSize(120). Basically more customers means more batch tasks, so the easiest way to process them faster is to run more batch threads, provided that you have the infrastructure to support it (multiple AOS, with plenty of processors, etc.). You could also play a little with that CustomerBundleSize, but I doubt much can be gained from it.

Outside of potential blocking in SQL Server, it should scale in parallel pretty well. It just runs a huge number of single record select statements, for the most part. I didn't see much in the way of index opportunities or join optimizations. The whole thing is probably as CPU intensive in the AOS as it is in SQL Server, which means your options are limited without doing some heavy re-writing.

Was this reply helpful? Yes No
Suggested answer

bnorma01 119 on at

Like (0)

Report

If you look at the batch tasks/threads that get created do all the batches take that long ? Reason I ask is in our company we have a few customers that take all the time. I changed the code to be a little smarter on how it assembles the groups/bundles of customers for each thread which took our run time from 1-2 hours down to 20 minutes. Further if your run creates a bunch of threads but the batch server does not have that many threads then this could explain some of the time. I am attempting to create a blog post to demonstrate my change but have not been approved yet to post a blog.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

you can try use the tracing tool, to run the statement in SQL, and see which index you need to create in the query or tables, this will increase the performance almost 90 % if you use the correct index.

Was this reply helpful? Yes No
bnorma01 119 on at

Like (0)

Report

https://community.dynamics.com/ax/b/briannormantechtips/archive/2017/03/16/shorter-customer-aging-snapshot-batch-run-times-using-smarter-bundle-sizes

Was this reply helpful? Yes No
guk1964 10,888 on at

Like (0)

Report

Thank you, the blog article is clear. A little more information on this would be welcome.

Was this reply helpful? Yes No
cowboyott 204 on at

Like (0)

Report

The problem with the aging snapshot is that it wants to run for ALL customers. We have 2 million customers and this was not realistic. It would have taken weeks of continuous processing to run. My solution was to modify the custAgingSnapshot class and only run the snapshot for customers with Open Unsettled balances.

1. On CustAgingSnapshot (form) - Add some new Dialog Field parameters so you can choose to Query customers by open balance. Pass this parameter into the custAgingSnapshot class



2. On CustAgingSnapshot (class) - Add logic to query customers with open balances only based on the parameter you passed in. This is on the getCustomerPoolQuery (method)



3. Then we need some logic on the process (method) to skip the customer bundle creation, because it has a flaw that bundles customers that aren't in your query, at least in 2012 R3 CU9.

4. Lastly we need to Delete all records from the custAging table. Since we are only processing open customer balances, if a balance is paid it will not get removed from the next snapshot run, so we just delete them all out before the next run.



Now it only takes 30 minutes to run!  Hope this helps someone.

Was this reply helpful? Yes No