Forum Discussion
Incremental Refresh on Dataflow sourced from SharePoint
Hi Odiadri
The reason for putting the range start and range end filter early in the steps is that because all of these subsequent steps thereafter will only be using the filtered data and not the entire data set. The reason for the speed increase is because under the hoods the incremental refresh means it does not have to reload the entire data set every day, but only the new data that is incoming and that is why you are seeing the performance gains.
Ah, we haven't mentioned caches yet, have we? Most likely all of your files are only read once from source, and then fetched from cache for the other partitions.
Thank you very much for your insights.
I understand why incremental refresh is faster than full refresh in general, and SharePoint Excel files do not support query folding, and the filter being late is not ideal (yes infuriating!).
However, I still do not fully understand the source side of the process.
My setup is:
- 1 Excel file per day in SharePoint
- Dataflow Gen1: Incremental refresh policy = refresh last 3 days
If the query still has to read all SharePoint files for each partition because there is no folding, then I would expect: Full refresh = read all files once + Incremental refresh for 3 days = read all files 3 times (once per partition).
That should theoretically be slower, not faster, no?
So is the main performance gain simply that only the last 3 partitions are rewritten into storage, while historical partitions are left untouched?
Thank you all!
Super UserAh, we haven't mentioned caches yet, have we? Most likely all of your files are only read once from source, and then fetched from cache for the other partitions.