Forum Discussion
Helper IIHow 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,
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:
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.
4 Replies
- GilbertQ
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.
GracieSince 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.
- ImkeF
Community Champion
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:
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.
