SQL Execution Timeout Expired

[Anonymous]

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:

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!

[sevenhills · 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 ....

[selimovd]

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

[Shahfaisal]

Did you try increasing the time-out setting under "Advanced options"?