question

ChristopherMhl-1161 avatar image
0 Votes"
ChristopherMhl-1161 asked ChristopherMhl-1161 commented

Azure SQL to Dataverse - performance issue with dataflows

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

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered ChristopherMhl-1161 commented

Hello @ChristopherMhl-1161,
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 .

198988-image.png

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


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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



image.png (19.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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)

199199-dataflow-dynamics.png

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?


0 Votes 0 ·

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

0 Votes 0 ·