I have the following error when I try to import a large view using a SQL Query. While evaluating the query, it evaluates for 10 mins exactly and throws this error.
To provide further context, it reached a point where I was presented an option to load/transform data and I clicked on "Load" which is when it evaluated my query for 10 mins exactly and threw this error.
I've used a similar query in the past which worked flawlessly and still works. The only thing I added to the query are the two lines in bold in the code below.
Query 1: Here's the SQL Query I used which resulted in the error:
SELECT
VID.[ClinicID],
CONCAT(VID.[InvoiceNo], VID.[ClinicID]) AS [InvoiceNo-ClinicID],
CONCAT(ISNULL(C.PMSCLientID,VID.[ClientNo]), VID.[ClinicID]) AS [ClientNo-ClinicID],
CONCAT(IsNULL(PT.PMSPatientID, VID.[AnimalNo]), VID.[ClinicID]) AS [AnimalNo-ClinicID],
VID.[Date],
SUM(VID.[Amount]) AS [Amount_Sum],
SUM(VID.[LastCost]) AS [LastCost_Sum],
VID.[Provider],
VID.[Code],
VID.[Description],
SUM(VID.[Quantity]) AS [Quantity_Sum],
VID.[GroupingID],
VID.[Grouping]
FROM v_InvoiceDetails VID
LEFT OUTER JOIN FX_Patients PT WITH (NOLOCK) ON VID.Animal = PT.OriginID AND VID.ClinicID = PT.ClinicID AND VID.OriginDatabase = PT.OriginDatabase
LEFT OUTER JOIN FX_Clients C WITH (NOLOCK) ON VID.ClientNo = C.OriginID AND VID.ClinicID = C.ClinicID AND VID.OriginDatabase = C.OriginDatabase
LEFT OUTER JOIN Clinics CL WITH (NOLOCK) ON VID.ClinicID = CL.ClinicID
WHERE VID.[Date] > DATEADD(YEAR, -3, GETDATE())
AND VID.[Date] >= CL.FirstDate
AND VID.[Amount] <> '0'
GROUP BY VID.[ClinicID],VID.[Date], CONCAT(VID.[InvoiceNo], VID.[ClinicID]), CONCAT(ISNULL(C.PMSCLientID,VID.[ClientNo]), VID.[ClinicID]), CONCAT(IsNULL(PT.PMSPatientID, VID.[AnimalNo]), VID.[ClinicID]), VID.[Provider], VID.[Code], VID.[Description], VID.[GroupingID], VID.[Grouping]
Query 2: Here's the SQL Query I used which worked flawlessly more than 10 times in the last few weeks:
SELECT
VID.[ClinicID],
CONCAT(VID.[InvoiceNo], VID.[ClinicID]) AS [InvoiceNo-ClinicID],
CONCAT(ISNULL(C.PMSCLientID,VID.[ClientNo]), VID.[ClinicID]) AS [ClientNo-ClinicID],
CONCAT(IsNULL(PT.PMSPatientID, VID.[AnimalNo]), VID.[ClinicID]) AS [AnimalNo-ClinicID],
VID.[Date],
SUM(VID.[Amount]) AS [Amount_Sum],
SUM(VID.[LastCost]) AS [LastCost_Sum],
VID.[Provider],
VID.[Code],
VID.[Description],
SUM(VID.[Quantity]) AS [Quantity_Sum],
VID.[GroupingID],
VID.[Grouping]
FROM v_InvoiceDetails VID
LEFT OUTER JOIN FX_Patients PT WITH (NOLOCK) ON VID.Animal = PT.OriginID AND VID.ClinicID = PT.ClinicID AND VID.OriginDatabase = PT.OriginDatabase
LEFT OUTER JOIN FX_Clients C WITH (NOLOCK) ON VID.ClientNo = C.OriginID AND VID.ClinicID = C.ClinicID AND VID.OriginDatabase = C.OriginDatabase
WHERE VID.[Date] > DATEADD(YEAR, -3, GETDATE())
AND VID.[Amount] <> '0'
GROUP BY VID.[ClinicID],VID.[Date], CONCAT(VID.[InvoiceNo], VID.[ClinicID]), CONCAT(ISNULL(C.PMSCLientID,VID.[ClientNo]), VID.[ClinicID]), CONCAT(IsNULL(PT.PMSPatientID, VID.[AnimalNo]), VID.[ClinicID]), VID.[Provider], VID.[Code], VID.[Description], VID.[GroupingID], VID.[Grouping]
Any help is much appreciated!
Potential solutions I'm looking for:
Potential solutions I'm looking for:
1. Increase query evaluation time out limit.
2. Modify Query 1 in a way in which PowerBI Desktop would let me import it flawlessly.
3. Any other useful/helpful suggestions are welcome.
Thanks in advance!
1 ACCEPTED SOLUTION
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.FirstDate
The SQL sounds as you are looking only for the last 3 years data and within them,
rows starting on or after Clinics First Date
To 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 ....
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.FirstDate
The SQL sounds as you are looking only for the last 3 years data and within them,
rows starting on or after Clinics First Date
To 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:
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
