Azure SQL to Dataverse - performance issue with dataflows

Question

Azure SQL to Dataverse - performance issue with dataflows

Christopher Mühl 111

Hello community,

I want to transfer data from an Azure SQL to Dataverse via Azure Data Factory. I have 2 questions about this:

Which connector is the latest and which one should be used for data transfer?
a) Dynamics 365 b) Dynamics CRM c) Dataverse

My first attempts were made with the Dataverse connector and I noticed the following:
First I tried a DataFlow with first of all no transformations, only source and sink. With this I could process about 20 rows per second or about 80k rows per hour. After that I ran into a connection reset error.

After this, I tried a copy activity. I was able to transfer 100k lines in under 9 minutes. The Linked Service was the same.

In my case I need a few data transformations, but also e.g. Conditions before the data is transferred to Dataverse. Therefore I would prefer the way over the dataflows.

How does this performance difference come about if I use the same connector? Is there anything I can do to improve the performance in the dataflow? In the CopyActivity I still had settings like BatchSize. In the DataFlow I do not have this option.

Many thanks in advance!
Christopher

1 answer

Your answer

Answer 1

HimanshuSinha-msft 19,486 Microsoft Employee Moderator

Hello @Christopher Mühl ,
Thanks for the question and using MS Q&A platform.

As we understand the ask here is if we can do to improve the performance in the dataflow, please do let us know if its not accurate.
I think you can definitely try to update the compute type & Core count and you should see performance benefits .

Please do let me if you have any queries.
Thanks
Himanshu

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
- If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Christopher Mühl 111 Reputation points

2022-05-05T12:23:22.403+00:00

I'll try to make my question more specific again.

I am faced with the task of transferring data from an Azure SQL to Dataverse in a performant way. The largest table has about 800k rows.

Since few data transformations are necessary in advance, I would prefer to use a dataflow instead of a copy activity.

To compare the performance between Dataflow and Copy Activity I made a test.
I created a dataflow only with source and sink and without further transformations. (see screenshot)

The source is an Azure SQL table and the sink is a table in Dataverse.
With the dataflow I have a very bad performance. Only about 20 rows per second are written to the Dataverse table.

But if I use a Copy Activity with the same source and the same sink and the same connectors, I get a much better performance. Here I can transfer over 100k rows to the dataverse table in under 9 minutes.
For comparison: with the Dataflow, it takes me about the same amount of time for only 10k rows. (Factor 10 slower)

Increasing the size of the data flow cluster does not change anything.

So my question: why is the Dataflow so much slower than the Copy Activity, when the same connectors are used?
Christopher Mühl 111 Reputation points

2022-05-05T12:26:12.373+00:00

I achieve good performance in dataflows with other sinks (e.g. Azure SQL). Only when using the Dataverse connector the performance disappoints me.

Share via

Azure SQL to Dataverse - performance issue with dataflows

1 answer

Your answer