Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gracie
Helper II
Helper II

How to load data faster or apply query change faster from SQL server to Power BI?

Hi,

 

I want to connect a table from SQL server to Power BI. The table contains 3.5 million rows. Firstly, it takes very very very long time to load. And I also need to filter some rows. Then it takes even longer time to apply query changes.  What acan I do to increase the load and apply query changes speed. Just lik adding index in sql server makes query execution faster. Can I use the index in sql server to load table faster and apply query changes faster?

 

Let's say, I want to get table Deal and filter out ACCOUNT column where is null and 0, also filter out CLIENTID column where is 2309, 7898 and 7607. Below is what I currently have in query editor. How can I edit it to achieve the goal?

 

let
Source = Sql.Database("am1db", "Link"),
dbo_Deal = Source{[Schema="dbo",Item="Deal"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [ACCOUNT] <> null and [ACCOUNT] <> 0 and [CLIENTID] <> null and [CLIENTID] <> "7898" and [CLIENTID] <> "2309" and [CLIENTID] <> "7607")
in
#"Filtered Rows"

Thanks,

1 ACCEPTED SOLUTION

During the design-process of your query, you can reduce wait-times by applying this method: http://www.thebiccountant.com/2016/11/08/speed-powerbi-power-query-design-process/

 

In general, applying filters to a sql-query should speed it up instead of slowing it down - so this definitely sounds strange.

Normally, your filters should "fold back" to the server, meaning that the amount of rows retrieved from the server should be less than without the filter and it should therefore be faster.

 

You can check if folding takes place by using the Profiler tool for SQL-server or a pretty easy method in PowerBI: Check if the "View native query"-field is active:

 

PBI_QueryFolding_ViewNativeQuery.jpg

 

When you click on it you will see the SQL-command that will be sent to the server. This should end with a "WHERE" clause where your filters are applied.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
GilbertQ
Super User
Super User

Hi @Gracie

 

As @Anonymous suggested you could change it to use a TSQL Select which can improve the data with the following:

 

Source = Sql.Database("am1db", [Query="Select ColumnName1, ColumnName2, ColumnName3 from dbo.Deal where ACCOUNT is not null and ACCOUNT <> 0 and CLIENTID is not null and CLIENTID not in ('7898','2309','7607'")

As you can see above I have used the column names, which will not only increase the query performance, but also ensure that you bring the columns you require.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Gracie
Helper II
Helper II

Since the table contains 3.5 millon rows. It takes a long long time each time I apply query changes. Is there a method that can increase the speed. Like the indexing fuction in sql server.

Anonymous
Not applicable

If you know some SQL you could add an SQL statement into your Power Query to reduce the number of rows your database needs to return.  This could be done by changing

 

Source = Sql.Database("am1db", "Link", [Query="<SQL Query Here>"]),

During the design-process of your query, you can reduce wait-times by applying this method: http://www.thebiccountant.com/2016/11/08/speed-powerbi-power-query-design-process/

 

In general, applying filters to a sql-query should speed it up instead of slowing it down - so this definitely sounds strange.

Normally, your filters should "fold back" to the server, meaning that the amount of rows retrieved from the server should be less than without the filter and it should therefore be faster.

 

You can check if folding takes place by using the Profiler tool for SQL-server or a pretty easy method in PowerBI: Check if the "View native query"-field is active:

 

PBI_QueryFolding_ViewNativeQuery.jpg

 

When you click on it you will see the SQL-command that will be sent to the server. This should end with a "WHERE" clause where your filters are applied.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.