Forum Discussion
SQL Execution Timeout Expired
- 5 years ago
1. As far as the Power BI concerned, you only have the option of Increasing the timeout
2. If it is me, I will do query tuning.
2.1. Without knowing your details of the query,
looks like you are using View and then joining a table to filter i.e., v_InvoiceDetails and "Clinics".
Because you said the query without Clinics is taking more or less 10 minutes only. Typically, clinics data is kind of dim and less than view invoice details.
My guess is the view may have clinics already. If this is true, then you take a different approach
like
(Select * from view ... where view filters) left join the Clinics ... where
2.2. My second suspect is that You are doing date filter on two conditions for the same column
VID.[Date] > DATEADD(YEAR, -3, GETDATE())AND VID.[Date] >= CL.FirstDateThe SQL sounds as you are looking only for the last 3 years data and within them,
rows starting on or after Clinics First DateTo check if this is causing the issue, just hide the condition and load
i.e., include as join to Clinics table but remove this " AND VID.[Date] >= CL.FirstDate"
Try the join as inner join first and then as left join next
If there is no issues causing in joining the table
Then I suspect "Date" filtering is the issue. Can this be simplified? Yes, please rework on the query like above or different way.
3. Last is the indexes
hope this help ....
Hey Anonymous ,
as we don't know your database it's hard to tell you how to improve the query. In general it's just a few joins, this is not that bad, but it depends much on the database and data.
The easiest appraoch might be to increase the timeout value. This you can easily do in the advanced settings for SQL Server data source: