Forum Discussion
Data refresh slower over time SQL server
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
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.⚡
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
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.⚡