Forum Discussion

jPinhao's avatar
jPinhao
Advocate II
10 years ago

Queries over large data tables

We have a large dataset of unstructured data (Azure Blob) and have started noticing that refreshing our model gets quite slow after a few thousand records are being loaded.   Our current query stru...
Need Help
Tips and Tricks
pqian's avatar
pqian
Microsoft Employee
10 years ago

jPinhao Referenced queries will execute once per query loaded, so it could be possible that they will hit the data source multiple times - in parallel loading cases they certainly will. It's a function of how each referencing queries ended up folding to the source and how the data is been cached.

 

For a more detailed explanation, see this post:

http://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables/m-p/23901/highlight/true#M7607

pqian's avatar
pqian
Microsoft Employee
10 years ago

jPinhao

While we are discussing options to turn off parallel processing with the teams involved, there's a workaround for your scenario. You can set an environment variable in the PBIDesktop.exe process to go back to sequential loading. 

 

For example, in the command prompt, you can:

 

> SET PBI_EnableMultithreadedDataLoad=0

> PBIDesktop.exe

  • jPinhao's avatar
    jPinhao
    Advocate II
    10 years ago

    Thanks pqian!

    This was quite helpul to better follow the execution order, and even helped us pinpoint a few inneficiencies in our queries :) As it stands I don't think what we're trying to do with Power BI is quite possible, but it helps to know we do need an extra layer to process our data!