3 million Invoices in monthly Batch - CRM Online

(0) Share

Report

Posted on by Community Member

We have an CRM 2015 Online client that has an end of month Batch containing 3 million invoices. Each Invoice has 4 to 6 Product line items. Each Invoice has to be "processed" with custom business logic in C#.

So obviously a standard Workflow is much too slow to process these invoices because it is not inherently
"multi-threaded".

Has anybody encountered and engineered a solution to this huge performance issue Online?

Our initial ideas:

1) Create a custom "Master C# Workflow" that will split the 3 million invoice Batch into 1000 smaller Batches.

2) The "Master C# Workflow" will then spawn 1000 new C# "Slave Workflows" to simultaneously process the 1000 small Batches so we achieve faux "multithreading".

We have experimented with Azure WebJobs but the network latency kills the performance. So we are trying to keep everything "local" in the Online Sandbox by using only Workflows.

Before CRM Online we could do all kinds of tricks in the SQL Server to split large batches into small batches and do custom C# code without timeout issues. But Online is a different animal!

Has anybody cracked this nut yet? I have searched online and not found any solutions. Anybody want to share some ideas or even better a proven solution?

Just running tests/benchmarks is incredibly cumbersome with this staggering amount of Invoices in the Online environment.

Any help and guidance is greatly appreciated! Thank you!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Ramakanta 2,715 on at

Like (0)

Report

RE: 3 million Invoices in monthly Batch - CRM Online

Hi Tim,

How did you achieve your solution,i have same kind of situation where i am creating 10k Orders with 1Lakh order product associated product.Any help?

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

RE: 3 million Invoices in monthly Batch - CRM Online

In case it helps anybody in the future, here is our findings after weeks of development and testing...

According to this MS CRM Online document there is no timeout for CRM Online out-of-the-box "normal workflows". But custom .NET "workflow steps" are subject to the two minute timeout. We tested extensively this and proved it is not entirely correct in the real world.

We developed a long running "normal workflow" that started asynch "workflow steps" that processed HUGE batch jobs in chunks. We successfully tested it with 100k invoice batch jobs and it worked many times.

There is an undocumented bug in CRM Online that affects customers with a huge accounting/batch jobs... It seems that after a certain amount of resource usage, Microsoft will stop the CRM Online Asynchronous Service- and fails to notify the customer that the Asynch Service has been stopped.

Steps to replicate the bug with CRM Online:

1) Create a very long running normal workflow that spawns many small asynch .net workflow steps.
2) Run this normal workflow many times.
3) After certain amount of resource usage, the Asynch Service will mysteriously die with no notification.

If anybody can replicate the steps please reply here and hopefully MS can update their Architecture documentation so everything is more clear and developers can stop wasting weeks of time developing/testing.

CRM Online is not a very scalable cloud service! It is orders of magnitude less scalable then On-Prem for certain scenarios...

Hope this helps somebody in the future!

Thanks

Was this reply helpful? Yes No
qqqqqqq on at

Like (0)

Report

RE: 3 million Invoices in monthly Batch - CRM Online

Hi Tim,

You might want to look at our product North52 BPA which can make CRM Online handle these types of loads.

This is an old video from CRM 2011 but it shows us splitting up a batch process of 10,000 records into 10 sub-processes so spread out the load on CRM Online. We can also do this for workflows.

https://www.youtube.com/watch?v=mr7ekUdHgRw

Drop an email to support at north52.com if you have any questions.

Thanks

John

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: 3 million Invoices in monthly Batch - CRM Online

Thanks again... our on-prem solution does handle all kinds of errors and re-runs the batch processing job no problems. According to crmbusiness.wordpress.com/.../understanding-plugin-sandbox-mode now Workflows (both Custom and Vanilla) can be run in CRM Online in isolation mode to prevent the 2 minute timeout. We will experiment more.

If anybody has real-world experience solving this Online limitation please let us know!

Thanks!

Was this reply helpful? Yes No
Guido Preite 54,084 Moderator on at

Like (0)

Report

RE: 3 million Invoices in monthly Batch - CRM Online

Hi Tim,

I understand your needs and that you are looking for a generic solution, but we need to be realistic.

If you write "We cannot split the huge Batch into smaller Batches as that will trouble the accounting rules and cause more complex problems when it is time to reconcile."

you are assuming that you are in a perfect world where errors never happen and all goes smooth, are you telling me that your current software implementation doesn't handle errors or doesn't have a rollback or a retry mechanism?

also if you are with OnPremise, never happens that SQL server is down due to a restart or the .exe simply crashes because out of memory or there is a networking issue?

About the PDF you are right, the timeout limit (known by developers) is not specified inside that document, probably it's somewhere inside MSDN.

Trust me that I'm a big fan of "putting all the data in one place" but maybe the business process you need to handle can't be implemented with the required performance when using CRM Online.

hope it helps

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: 3 million Invoices in monthly Batch - CRM Online

Thanks for your reply Guido. In my first message I was over simplifying things. We actually have multiple customers (some have different ERP systems, some just use our offering) and so we need a generic solution that will work in CRM Online the same way it works fine in CRM On-Prem.

We cannot split the huge Batch into smaller Batches as that will trouble the accounting rules and cause more complex problems when it is time to reconcile.

Things that are simple in On-Prem like high-performance c# Paralell.ForEach error out Online (not even a timeout issue, general connection issues!)

The "processing logic" is just Accounting logic written in C# that cannot be done in a vanilla workflow. It is currently done in a plugin that times out after 2 minutes online.

According to the hugely ambiguous PDF at blogs.msdn.com/.../microsoft-dynamics-crm-online-patterns-amp-principles-for-solution-builders.aspx it states that Workflows do not have timeout. But the PDF fails to say that a C# Custom Workflow Activity WILL have timeout, while vanilla Workflows do NOT have the timeout! I don't think the authors of this PDF have ever dealt with huge enterprise datasets.

Do you or anybody else have any other ideas? It needs to be a generic solution that can be implemented for our multiple Online customers.

We have already tried WebJobs and other "offloading" attempts and the network latency is hugely problematic and unstable. Any kind of solution passing millions of invoice record data going over Ethernet/Network will be unacceptably slow.

We need to come up with a solution that is "local" as possible. Perhaps there could be a "watchdog" in Azure to control things? Any insights are appreciated! But an "all local" idea would be even better.

Thanks!

Was this reply helpful? Yes No
Guido Preite 54,084 Moderator on at

Like (2)

Report

RE: 3 million Invoices in monthly Batch - CRM Online

Hi Tim,

I think you can't ask to a generic solution but this needs to be tuned based on your exact requirements. Which kind of "custom business logic" do you perform on these invoices?

It's a logic that needs the CRM or can be made offline (offline meaning also caching previously some values from CRM like the list of products)

3 million records monthly is a big number and you will have 12-18 million invoice details records as well.

Personally with these numbers I will not store threse records inside CRM, I would suggest to store/process them inside an ERP (and maybe the company is already doing this) and put the aggregate inside CRM (and I think that the "custom business logic" maybe is related to this). However this is my idea and sure there are reasons why it's not suitable for your customer.

This is my opinion: normally I don't trust too much workflows and definitely I don't trust them with so high numbers.

You need to consider the invoices by month, but I don't think that they are created all of them in the same day, so what you can do is to create a SQL DB where you will put a copy of your invoices when they are created and updated (you can trigger plugin on create and update, and you can demand the pulling of the data from an external service instead pushing the data directly from plugin to the DB).

When arrive the day to process your invoices, you retrieve the relevant data you need from CRM (list of products, customer information, etc etc) and perform the custom business logic.

Then we arrive to the final part, updating the CRM, if you just need to update some aggregate data and not the single invoice you are fine, if you need to update all the invoices (for example just to set a flag "processed") you will hit the limits of CRM API, you can be fast (using ExecuteMultiple, optimizing the batches sizes, etc etc) but not as faster as you probably need, and depending on which kind of update you want to perform you can also trigger the workflow execution on the records from outside (but at least triggering them from outside you can monitor the success or the failure of the workflow)

hope it helps

Was this reply helpful? Yes No