Query Time Outs

[wc123]

I have a query that i am running in SSMS that takes about 3-4 Minutes to finish executing. I bring that same query into powerbi using a native query and it never finishes executing. What is going on here and how can I make this not an issue.  Please help this is beyond frustrating.

[v-dineshya]

Hi @wc123 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @krishnakanth240  and @parry2k , Thank you for your prompt responses.

Power BI has a less default Timeout, It does not run your query like SSMS. It often runs it to get schema, to preview data, to validate metadata, during refresh and during relationship discovery. If you use Value.NativeQuery, Power BI may wrap it with extra SQL, this can break indexes prevent parallel plans. It Loads everything into memory, applies data type conversions and Compresses the model.

SSMS has No timeout by default. It streams results and minimal transformation

Please try below things to fix the issue.

1. Increase Power BI Timeout, In Power Query:

Source = Sql.Database(
"Server_name",
"Database_name",
[
Query = "YOUR QUERY",
CommandTimeout = #duration(0, 0, 30, 0)
]
)

Note: Above "30" indicates 30 minutes.

2. Use Value.NativeQuery

let
Source = Sql.Database("Server", "DB"),
Data = Value.NativeQuery(
Source,
"YOUR QUERY",
null,
[EnableFolding=false]
)
in
Data

3.Disable Power BI Preview Load, Power BI Desktop:

File --> Options --> Data Load.

Enable data preview to download in the background

Note: This prevents double execution.

Please refer below snap for connection time out for Power bi.

Please refer below snap for connection time out for SSMS.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @wc123 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[krishnakanth240]

Hi @wc123 

Power BI is not just running your query like SSMS and it might break query folding, adding transformations, and hitting the timeouts where it becomes much slower and never finishes. So, ensure query folding is enabled, moving logic to SQL View, and reducing the data with essential columns.

[parry2k]

@wc123 are you doing any further transformation in PQ? Do you see query folding happening?