Forum Discussion
Query Time Outs
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.
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
Data3.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
5 Replies
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.
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
Data3.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
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
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
