Power BI Data Load - How to set Memory Buffer size to fetch more number of rows per batch call

[makbee]

When trying to load tables from an ODBC Data Source into Power BI Desktop, I notice that Power BI fetches a different number of rows per batch/per call to the data source.

Wider tables with more columns fetch fewer rows per call resulting in higher number of calls to the data source - leading to extremely high load times.

A table with fewer columns loads 1250 rows per call whereas a wider table fetches only 117 rows per call during the data load process.

I have increased the MaxEvaluationWorkingSetInMB to 7385MB (max allowed) and yet, the fetch size per call to the Data Source remains unchanged for a given table.

I enabled tracing and I'm able to see a Mashup Engine Data Reader that processes data in Pages with a PageRowCount.

DataMashup.Trace Information: 24579 : {"Start":"2024-07-06T14:43:24.6805441Z","Action":"RemoteDocumentEvaluator/GetResult<Microsoft.Mashup.Engine.Interface.IDataReaderSource>","HostProcessId":"9200","identity":null,"evaluationID":"5","containerID":"1","PageRowCount1":"117","PageRowCount1":"117","ProductVersion":"2.105.923.0 (22.05)","ActivityId":"8c45147c-bd74-4ac5-b0d3-5b5e99a2c9db","Process":"msmdsrv","Pid":9200,"Tid":12,"Duration":"00:00:39.8562459"}

Using the same ODBC driver and Data Source with another ODBC client allows me to fetch the full table with 55500 rows in 28 seconds whereas Power BI ends up fetching 117 rows at a time and takes up over 12 minutes to load the same table.

I can imagine that the Stream Reader in PBI allocates a buffer to read a certain number of bytes per call (my best guess is 4MB), and uses the row width to dynamically determine the total number of rows to fetch in each call so it fits in the buffer.

Is there a way to increase the buffer size used to determine the number of rows to fetch in each call to the data source?

[Anonymous · Accepted solution]

Hi @makbee,

AFAIK, current it does not exist the properties allow you to modify these settings. I'd like to suggest you submit an idea for add support with customized these parameters, or you can try to deploy a web service as the bridge to getting data from driver and buffering and processing these data. Then you can use power bi to getting records from web service to get processed data.

Regards,

Xiaoxin Sheng