article 19207

Power BI DirectQuery, Gateways And SQL Queries That Return Lots Of Rows

Recently I was working with a customer using DirectQuery mode and where all traffic to the data source had to go through an on-premises data gateway for security reasons. They noticed that report performance got worse when traffic went through the gateway and this was particularly true when Power BI generated SQL queries that returned hundreds of thousands of rows. Looking in the gateway logs we found that spooling (see here and here for more details on spooling, and here for information on monitoring spooling in the logs) was taking place, so I advised the customer to set the StreamBeforeRequestCompletes property on the gateway to true. This eliminated the delays from spooling on the gateway (I’m told it helps performance inside the model too) and resulted in some substantial improvements in report performance. As a result, if you’re using DirectQuery and a gateway, you should experiment with the StreamBeforeRequestCompletes property to see if it can help you too.

That said, if you’re using DirectQuery mode you should try to avoid situations where Power BI generates SQL queries that return a large number of rows such as table visuals with scrollbars. Regardless of whether you’re using a gateway or not, moving large amounts of data from your data source to your semantic model can be slow – even if your data source tells you the SQL query itself is fast. You can use Performance Analyzer in Power BI Desktop (see here) to find out how many rows the SQL queries Power BI generates return and how long it takes to read that data.