Dataflow refresh on simple queries extremely slow

[richard-powerbi]

I have a Power BI Pro license and a super simple dataflow to throw here as an example.

I have more complex ones, but the terrible performance on this simple one surprises me and makes it obvious there is something wrong. All Dataflows including this one take around 3-4 min to finish.

The simple Dataflow contains 6 queries. See code below.

It doesn't need a Gateway for as far as I know (Json), but it doesn't give the notification that it doesn't need a gateway. See screenshot 1 below.

I have another Dataflow that connects to SharePoint and it does give a notification that it does't need a gateway. See screenshot 2 below.

Edit: after some more testing I find that it doesn't matter how small I make a Dataflow, faster than 30 sec refresh for basically nothing seems the fastest it can go. Which would explain 6x30sec=3min for a Dataflow with 6 small queries.

4 small queries comparable to this:

let
  Json = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSopKk0FUsqGRkbGlq5KsTrRSkZAflpiTjFE3NDCxc1NKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Definitief = _t, #"HEX-kleur" = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Json, {{"Index", Int64.Type}, {"Definitief", type logical}}),
  MetKey = Table.AddKey(#"Changed Type", {"Index"}, true)
in
  MetKey

1 small query like this:

let
  Json = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouyS8qUYrViVYyAvL8c5PyS8uVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ACTIVITEITTYPE_SEQNO = _t, Activiteit = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Json, {{"ACTIVITEITTYPE_SEQNO", Int64.Type}, {"Activiteit", type text}}),
  MetKey = Table.AddKey(#"Changed column type", {"ACTIVITEITTYPE_SEQNO"}, true)
in
  MetKey

1 slightly larger like this:

let
  Json = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdBBDoMgFEXRvTjWhPcBwR006aQDHBn3v41KWrDcTkxOIOT6jmN6+Gm+Pktxms751wZ7OMARXj8O1eF+v9tgDwc4wnw/wRneRsvB6BP6hD6hT+gT+oQ+oU/oM/QZ+uzbF6vjvW83zz0c4AivcIIzvI1u+3ajT+gT+oQ+oU/oE/qEPqHP0Nf2TdVpeTraYA+HwQX3y995hFc4wRneRre9uwWjRx5Gn9CnsW/HPnv731ydl9deBpeL5xs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Hal = _t, Plaats = _t]),
  MetKey = Table.AddKey(Json, {"Plaats"}, true)
in
  MetKey

Screenshot 1:

Screenshot 2:

[richard-powerbi]

The funny thing is that when I click refresh on some Dataflows, the 'LAST REFRESH' timestamp is almost instantly updated, but the refresh keeps spinning, and after the spinning stops (3+ min), the 'LAST REFRESH' timestamp is updated again.

Btw, the refresh of the simple Dataflow mentioned by me now takes up to 5 min sometimes.

Ridiculous. Should be 5 seconds max.

[v-joesh-msft]

Hi @richard-powerbi ,

According to my experience, the minimum refresh time of dataflow is designed to be 30s, but I can't find related documents at present, if I find it, I will update it here.
However, I think this does not affect the use of dataflow. When you get data from dataflow using Power BI Desktop, and then publish to power bi service, you will find that the dataset refresh will be fast.

For more information, you can refer to the following article:

How to Use Dataflow to Make the Refresh of Power BI Solution FASTER!

Regarding Blank Query doesn't give the notification that it doesn't need a gateway, It is also by design.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[richard-powerbi]

@v-joesh-msft thx for the reply, but isn't it even at a lower level, 30 sec per query, and not per dataflow? Because if it was at dataflow level, my tiny dataflow would refressh in 30-35 sec or so....

[v-joesh-msft]

Hi @richard-powerbi ,

Yes, you are right, maybe what I described is not very accurate. The minimum time for a query refresh is designed to be 30s, so the minimum time for a dataflow refresh is 30s.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[richard-powerbi]

"so the minimum time for a dataflow refresh is 30s"

I believe you make the same mistake again.. 😉

According to my testing, that's the minimum per query. So if a Dataflow has 6 queries, no matter how tiny they are, the Dataflow refresh is 3m min.