Performance enhancement tricks when using Direct Query to get data from SQL server

[aviku]

Hi,

I am using PBI Desktop to create PBIX files which I later upload to Power BI Embedded (PaaS solution). The PBIX gets data from Azure SQL server in Direct Query mode. I want to increase the efficiency of queries that Power BI Embedded sends to SQL for getting my data.

Please advice if the following options will help me increase the efficiency of queries, thus reducing the time taken by the pbix to load: -

1. Using Advanced Options in the Get Data dialog box : Inserting a SQL statement here will get only specific data instead of the entire table. This will reduce the data I see in PBI Desktop, but will it really increase the efficiency of queries sent to SQL for the creation of charts? Eg: Say PBIX needs to create a join between two tables. If I use the advanced options, will the Join be done on reduced data?

2. Using Filters to filter out unwanted rows of the table : Again like above option, this will reduce the data I see in PBI Desktop, but will it really increase the efficiency of queries sent to SQL for the creation of charts? Eg: If I use filters, will the Join be done on reduced data?

Please redirect this to appropriate forums as necessary

[v-ljerr-msft]

Hi @aviku,

Based on my understanding both the options above will help increase the efficiency of queries, as all queries sent to the source database will run against only specific data instead of the entire table in this scenario, thus reducing the time that back-end source takes to respond.

Regards