Data refresh slower over time SQL server

[Anonymous]

Hi all!

I am experiencing a bit of a weird issue, and I wonder if others have run into it. I have a dataset of about 1.4M rows on an SQL server DB.

My powerquery merges this data with another set of similar size, left joining the two to create my table.

Now When the refresh starts at first, it will be nice and fast, about 15K rows per second, I think. But as time progresses, and we get to around 1.15M of 1.4M rows, it slows down....and slower..and slower, until there is only about 400 rows per second being ingested.

Does anyone have an idea why this is happening? Does it have to do with my merge? Or could there be something else at play?

Thanks!

Jaap

[parry2k]

@Anonymous why you are doing merge? I found the merge is expensive? Although in this case I guess query folding is happening. Try to avoid merge and then check. You can always load both the tables and set the relationship (better approach) rather than merging.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous · Accepted solution]

Hey Parry!

The reason I'm using merge instead of a relation, is that I do not want all data from the second table in my model. (And the first table determines what I need from the second) The second table is very big, and I only need a small part of the data. I don't want my file size to explode :). Unfortunately this is not a transactional system that I can just apply a star schema to 😞

Anyway, I checked, and the query folding broke after a "change type" step. I moved that "change type" to the end, and now the folding survives the merge. It does have an effect on the loading though. It now starts out slow, and then suddenly becomes very fast at the end. By patiently staring at my screen for theduration of the refresh, I found that it sped up right after running out of rows to merge with. So the speed difference is caused by the merge. The part where it has things to merge will be slow, the part where it does not (where the left join joins with nothing) will be fast.

It still seems faster and more space efficient to merge here than to use a relationship, very counter-intuitive....

Thanks anyway, your mention of the query folding did help me through the process 🙂

Jaap

[parry2k]

@Anonymous yes there always many parts to everything, glad it improved, yes, one should seriously pay attention to query steps and take advantage of query folding where possible. Good luck! and I hope you are good for now.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡