Forum Discussion
Never-ending incremental refresh in the Service
Hi Federico , good point. Let me note down some insights I've had since:
- as suggested below, incremental refresh might not work at all with direct query/direct SQL. This said, it shouldn't be a problem. I learned since, that I don't need a select statement, and [no lock] is not really required. So you should be well off connecting to your SQL source in a standard way.
- please check my other thread here: https://community.powerbi.com/t5/Desktop/Incremental-Refresh-is-there-a-way-to-handle-record-updates/m-p/2543821. It basically shows that incremental refresh cannot be used in some very common scenarios of updating past data. If you have frequent updates to old data, incremental refresh will simply not work and it may take you months to realise why. Some details are in the other thread.
- from my observations, if you use "detect data changes", provide many partitions (e.g. daily or weekly partition) and the nature of your data updates is that many partitions may be affected, then incremental refresh can turn out to be slower than the full refresh each time. That's not scientifically proven, just my experience.
- and finally, rebuild your indexes (needless to say, make sure that you have proper indexes on the column used for the incremental refresh AND for detect data changes if you use it). This turned out to be the main reason for slowliness in my case.
My final point is (again speaking from experience): connecting 2 tables with 25+M records in a relationship is going to kill your CPU & memory. Not even a premium capacity is going to help you. I ended up denormalizing the data and solving all the memory problems by that. This may be irrelevant in your case, I'm only saying this because after I "solved" the incremental refresh problem (i.e. turned it off), I immediatelly found myself having memory problems. There might be better solutions of course.
Good luck 🙂
Hi, I would like to know if you can solve your problem and if you acn share the solution you found. Is happening the same to me. I have 25 million registers from two different origins and it keeps refreshing until the time-out error occurs.
Thank you
Hi Federico , good point. Let me note down some insights I've had since:
- as suggested below, incremental refresh might not work at all with direct query/direct SQL. This said, it shouldn't be a problem. I learned since, that I don't need a select statement, and [no lock] is not really required. So you should be well off connecting to your SQL source in a standard way.
- please check my other thread here: https://community.powerbi.com/t5/Desktop/Incremental-Refresh-is-there-a-way-to-handle-record-updates/m-p/2543821. It basically shows that incremental refresh cannot be used in some very common scenarios of updating past data. If you have frequent updates to old data, incremental refresh will simply not work and it may take you months to realise why. Some details are in the other thread.
- from my observations, if you use "detect data changes", provide many partitions (e.g. daily or weekly partition) and the nature of your data updates is that many partitions may be affected, then incremental refresh can turn out to be slower than the full refresh each time. That's not scientifically proven, just my experience.
- and finally, rebuild your indexes (needless to say, make sure that you have proper indexes on the column used for the incremental refresh AND for detect data changes if you use it). This turned out to be the main reason for slowliness in my case.
My final point is (again speaking from experience): connecting 2 tables with 25+M records in a relationship is going to kill your CPU & memory. Not even a premium capacity is going to help you. I ended up denormalizing the data and solving all the memory problems by that. This may be irrelevant in your case, I'm only saying this because after I "solved" the incremental refresh problem (i.e. turned it off), I immediatelly found myself having memory problems. There might be better solutions of course.
Good luck 🙂