Possible reasons for SQL Timeout on loading a view on Import

[justin777]

Hi Guys, 

I am stuck on solving this problem. 

So I have 5 views, and 1 of them are always stuck on loading when I apply to changes. 
It is always stuck on "Waiting for (SQL Server & DB)" 

Here are my observations:

• It work fine when using Storage: DirectQuery (I need to use import because of DAX formulas) 
• It work fine on preview in Edit Query (It shows all the result and table, no error whatsoever. 
• It only has 9 results and has 96 columns (This should not matter since the other view has 165 columns and 18 results)
• I tried at least a dozen of times, I even adjusted the timeout to 60 with no avail. 
• I have access to SSMS, it works fine on loading and designing the view. Not sure what to look for that may cause the indefinite wait time
• Even when I try to create a new report, and load that View. After hitting the apply changes, I will be stuck on Apply Query Changes until it reaches an SQL Timeout. 

[v-frfei-msft · Accepted solution]

Hi @justin777,

Did you add some complex steps in query editor? If there are too many steps or too complex steps the issue might occur.

In addition, the option “Allow data preview to download in the background ” could affect the performance as well. You can disable it and have a try agagin.

 

By the way, what is your version of your desktop? Here I suggest to use the latest vision to work with. (Version: 2.60.5169.3201 (July 2018) )

Regards,
Frank

[justin777]

Hi Frank,

Thanks for the reply. The "Allow data preview to download in the background" actually help the performance a bit especially when just editting the query but I realize the bottleneck is really from the server. I thought it is some kind of bug or an option I have not yet discovered but the problem was pretty basic. The SQL Server can't handle the query with the default timeout limit, when I extended the timeout to 90 mins, it works fine after waiting for at least 20 mins. 

I'm using the latest Power BI Desktop just to be sure I don't get any weird bugs along the way