Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I have built a view (an aggregate) on a 35 billion row SQLServer table, 7 years of history. Every day around 14 million rows are added. The view is used in Incremental Refresh (1 Days)
The table has clustered columnstore index. And a unique, non-clustered primary key where creation-date (CRT_DT) is the first column. It is also partitioned on creation-date.
When I refresh today, is SQLServer then reading those 35 billion rows, aggregate them and then select the rows of today to pass through to Power BI?
Or is it immediately selecting only the rows of today (around 14 million) , aggregating them and passing them through to Power BI?
I am asking this for a better understanding. But also because, if I do a select query on the 35 billion row table for a specific creation-date and that is equal to the select in the view I get answer within 20 seconds
When I refresh one day partition in Analysis Services in SSMS, the query goes to status suspended, wait type CXCONSUMER.
And stays that way for a very long time.
Regards
Ron
Hi, query folding is there. The problem is that the main query is in a Common Table Expression, meaning I presume it will read everytime the whole table. And I think the wait type CXCONSUMER is there because it is waiting for data to consume. But I am not sure
Hello,
I'm not sure. You can probably take the native query from Power Query (I'm guessing it will add the date filters to the WHERE clause?) and check the query plan in SQL and compare it to an unfilitered query plan to see what changes when it runs through incremental refresh (or at least when it's filtered between RangeStart and RangeEnd, hah).
Just some thoughts.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Hello,
I had two initial thoughts on your question:
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |