Performance of bc2adls

Performance of bc2adls

In my deep dive session on Areopa Webinars (Areopa – Dynamics 365 Business Central Expert Training) I had told that I will do a performance test on the bc2adls (microsoft/bc2adls: Exporting data from Dynamics 365 Business Central to Azure data lake storage (github.com)) extension.

BTW the deep dive session you can find here:
20220426 – Sync your Business Central data with Azure Data Lake – YouTube

The test setup:
I have created a setup that export 107 tables from Business Central to Azure Data Lake.
Also my Azure Synapse runs on the default.

I have added the Performance Toolkit to generate allot of data and runned it three times to measure any differences.

The first initial run exported 42 (there were some errors in the tables) tables with in total 72.201 records. The total time for exporting the records to the Azure Data lake took 2 minutes.
The total time in Azure Synapse took up to 03:49.

The second run exported 55 tables with in total 303.481 records.
The total time for exporting the records to the Azure Data lake took 9 minutes.
In this run I had a couple of deadlocks on tables:

The activity was deadlocked with another user who was modifying the ADLSE Current Session table. Please retry the activity.
"ADLSE Current Session"(Table 82565).IsAnySessionActiveForOtherExports line 3 - Azure Data Lake Storage Export 4PS by 4PS B.V.
"ADLSE Execute"(CodeUnit 82561).TrySetStateFinished line 8 - Azure Data Lake Storage Export 4PS by 4PS B.V.
"ADLSE Execute"(CodeUnit 82561).SetStateFinished line 2 - Azure Data Lake Storage Export 4PS by 4PS B.V.
"ADLSE Execute"(CodeUnit 82561).OnRun(Trigger) line 88 - Azure Data Lake Storage Export 4PS by 4PS B.V.

and

The activity was deadlocked with another user who was modifying the ADLSE Setup table. Please retry the activity.
"ADLSE Execute"(CodeUnit 82561).AcquireLockonADLSESetup line 3 - Azure Data Lake Storage Export 4PS by 4PS B.V.
"ADLSE Execute"(CodeUnit 82561).TrySetStateFinished line 6 - Azure Data Lake Storage Export 4PS by 4PS B.V.
"ADLSE Execute"(CodeUnit 82561).SetStateFinished line 2 - Azure Data Lake Storage Export 4PS by 4PS B.V.
"ADLSE Execute"(CodeUnit 82561).OnRun(Trigger) line 88 - Azure Data Lake Storage Export 4PS by 4PS B.V.

I run this export three times. So the 9 minutes is a combined calculation.
The total time in Azure Synapse took up to 05:40.

The third and last run the export was 107 tables with in total 419.824 records.
The total time for exporting the records to the Azure Data lake took 8 minutes.
The total time in Azure Synapse took up to 06:15.

In a short overview:

#Total tablesTotal recordsDuration BCDuration Synapse
14272.2012 minutes03:49
255303.4819 minutes05:40
3107419.8248 minutes06:15

Conclusions

Well overall the export to Azure Data Lake from Business Central was very impressive. There were some deadlocks in it. But they are resolved by adding a commit:

Commit added to ensure that the session record does not hold on to th… by DuttaSoumya · Pull Request #18 · microsoft/bc2adls (github.com)

I have done it before the third run and after that I hadn’t any deadlocks anymore.

My only concerns is the duration of Azure Synapse. I have done it with the standard configuration. So maybe you can tweak that (Pricing – Azure Synapse Analytics | Microsoft Azure). Also you can run in over night

But overall got a good feeling that this will perform very well.

Below is the detailled information about the performance test.


5 COMMENTS

Chris

This is a great post; thanks. I’m curious, do you have any resources for setting up and configuring BC2ADSL?

    Bert Verbeek

    There is an perfect guide on https://github.com/microsoft/bc2adls/blob/main/.assets/Setup.md

    Very easy to install and configure it.

      Christian

      How do you recommend scheduling this process? It looks like the Export to Azure needs to be launched in BC and the pipeline needs to be run in Azure Snyapse (and there is a note in the BC2ADLS to not run the pipeline when an export is running).

Christian

Have you given any though on how you would recommend scheduling this process? It looks like the Export to Azure needs to be launched in BC and the pipeline needs to be run in Azure Snyapse. I suspect that we could schedule the task in BC but there is a note in the BC2ADLS doc to not run the pipeline when an export is running – which would require a bit of coordination. What have you don’t to over come this?

    Bert Verbeek

    Sorry for my late reply.

    Yes indeed that is a little but complicated.
    Is depends on how many tables you are exporting. But mostly a pipeline will run for 6 minutes.
    So you can do a little calculation on that.

    But be careful. Because it also get larger when you export more data overtime and your data in the data lake is getting larger.

    Looking also into a way that don’t to have the whole data lake storage to look for deleted records.

    Also with the API you can schedule the export with power automate.

Leave a Reply

Your email address will not be published.