This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Issue 'Execution Time Out Expired' Faced after pass SWQL through API

We try to retrieve Nodes information from database table named 'Orion.NodesCustomProperties', 'Orion.Nodes', and 'Orion.ResponseTime' through REST API. However, we encountered 'Execution Timeout Expired' issue after passing SWQL in the API. We found it may due to some database configuration or other related configuration. We are looking forward to contact and get advice from your technical team. Attached error occurs.

  • generally this occurs when the query you're using is too complex to return data within the default timeout window (30s or 60s if I recall)

    the work around would likely be one of the following options

    • validate that this isn't a network issue by testing the query(ies) directly on your Orion server
    • remove mathematical calculations from your query and return raw data that you manipulate in your display layer
    • limit your query return with a targeted 'WHERE' clause to reduce the amount of data being processed
    • work with SolarWinds Support to ensure that your current database is working without underlying issues that could cause timeouts like this
  • Hi, thanks for your reply. I did try to simplify query and limit records return, but, result still the same. Is that any way to tune the time window?

  • There are several ways to limit the number of records that come back to avoid timeouts.  You can add use TOP to limit the maximum number of records to return.  You can add a WHERE clause to filter the records that come back.  An example for Orion.ResponseTime follows.  You can also add an ORDER BY DESC clause to ensure that the most recent records come back first.  Depending on what you are trying to accomplish, you might also want to perform a GROUP BY to aggregate your results.

    SELECT TOP 1000 rt.NodeID, rt.DateTime, rt.AvgResponseTime, rt.PercentDown, rt.Availability
    FROM Orion.ResponseTime rt
    WHERE rt.DateTime >= ADDDAY(-1, GETDATE()) -- Within the past day
    ORDER BY rt.DateTime DESC
  • Thanks for your reply. I did add the filtering to my SWQL but it still return me the error. Following are my SWQL query.

    SELECT  TOP 10

    Nodes.NodeID AS NodeID,

    AVG(ResponseTime.Availability) AS Availability

    FROM Orion.Nodes AS Nodes

    INNER JOIN Orion.NodesCustomProperties AS Custom

    ON (Nodes.NodeID = Custom.NodeID)

    INNER JOIN Orion.ResponseTime AS ResponseTime

    ON (Nodes.NodeID = ResponseTime.NodeID)

    WHERE

    Custom.Agency Is NOT NULL

    AND Custom.Department <> 'ABC'

    AND Custom.Network = 'WLAN'

    AND YEAR(ResponseTime.DateTime) = 2019 

    AND MONTH(ResponseTime.DateTime) = 7 

    AND WeekDay(ResponseTime.DateTime) >= 2 

    AND WeekDay(ResponseTime.DateTime) <= 6 

    AND HOUR(ResponseTime.DateTime) >= 08 

    AND HOUR(ResponseTime.DateTime) <= 17 

    GROUP BY NodeID,Availability

    Hope can get help from you all. Thanks.

  • How long does that same query take to execute in SWQL Studio?

  • I ran your query against my Orion instance (after commenting out the filters on the Agency and Network custom properties, which don't exist in my environment).  It executed in sub-second time (0.41 seconds).  I don't believe the problem is with the query itself, though it is true that there are a lot of conditions in your WHERE clause.  As zackm​ suggested, it would be good to know how long it takes to execute in SWQL Studio for you.

  • pastedImage_0.png

    I still facing the same error while I am using SWQL studio to execute it. I did ran similar query in Orion Report Writer and it takes me around 3 minutes to run the query. May it be the database issue? or have anyway to extend the execution timeout?

  • So this is probably going to get a bit in the technical weeds.

    I've done a LOT of business hours custom reports and they can be a big problem due to the performance considerations for clients with larger environments or longer retention periods.

    A simple way to get a sense of scale the problem here is to maybe take a look at the size of the data sets yeoshihh is​ working with, as I would expect that its probably a lot larger than what we all have in our labs.

    I'd start just to see how these two queries go inside swql studio, in terms of count of rows returned and how long it takes to get them.

    select count(*) as Nodes from Orion.Nodes -- i got 61 nodes and about 2 seconds in my little lab

    select count(*) as Timestamps from Orion.ResponseTime --i got 802,683 and about 2.5 sec to execute in my lab

    After running those two I ran your query and it took about about 3 sec even though a chunk of the data was probably already in the cache from the earlier queries

    I expect the second one might come back with something painful, especially keeping in mind that the standard timeout for SWQL queries is something like 120 seconds.

    The other bit to consider is that when we use the datepart functions on the where conditions the way you are doing it means the SQL server is not going to be able to take advantage of any indexes to speed up the query, so it is probably generating a full table scan of the entire responsetime history of the environment, and then having to perform the math to figure out what each of the 6 dateparts is, and how those values compare to the operators.  This is likely burning up a lot of CPU cycles.

    This article explains how datepart type functions can jam up an index and how to write queries to do them better.

    https://littlekendra.com/2016/03/01/sql-servers-year-function-and-index-performance/

    You *might* have better luck by rewriting the SWQL this way, but it still probably won't be enough to push the performance where you need it (in my lab it seemed to run a bit faster, but since I have such a small dataset its hard to be sure).

    SELECT

    Nodes.NodeID AS NodeID,

    AVG(ResponseTime.Availability) AS Availability

    FROM Orion.Nodes AS Nodes

    JOIN Orion.ResponseTime AS ResponseTime

    ON (Nodes.NodeID = ResponseTime.NodeID)

    WHERE

    Nodes.CustomProperties.Agency Is NOT NULL

    AND Nodes.CustomProperties.Department <> 'ABC'

    AND Nodes.CustomProperties.Network = 'WLAN'

    AND ResponseTime.DateTime > DATETRUNC('month',GETUTCDATE()) --this gets us this month of this year in one shot and preserves the ability use indexes, but the weekday and hour functions below might cause sqlserver to still ignore them, only way to be sure is testing it out

    AND WeekDay(ResponseTime.DateTime) between 2 and 6

    AND HOUR(ResponseTime.DateTime) between 08 and 17

    GROUP BY NodeID

    When I have had to wrestle these issues in the past with larger clients we ended up finding it was WAY more effective for us to do it in regular SQL because first of all we wouldn't be constrained by the timeout limits in SWQL, and secondly we could build a datedimension table to let us know what the business hours were for every day.  Joining against that ended up being a lot more performant than trying to break up each timestamp with functions the ways its being done here.  IIRC we built something that had a column with the date of every day for the year on it, then another column for the close and open times, and we also had timezone offsets to calculate in, and when we generated it we went ahead and precalculated out all the dateparts like year, day of week, day of month, week of year, day of year, etc so that we never had to calculate them in our reports.  Having this made it super easy because we could just join all the responsetime timestamps to the calendar table and it handled all the business hours stuff like 100x faster than what we had tried before.

    This article explains the concept of a datedimension table

    https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

    In the end we still wanted it to run faster so we ended up wrapping the whole mess into a few stored procedures and sql agent tasks to just dump the whole result set into another database table and when managers wanted to view the report the data was already precalculated and the report would show up in a couple seconds, bypassing the fact that it took the db server like 10+ minutes to compile the data when we had to do it live.

    Hope that helps you, or others struggling with their business hours reports.

    -Marc Netterfield

        Loop1 Systems: SolarWinds Training and Professional Services

  • Hi,

    the query is pretty simple and should work. In case there's problem with locking you can try siffix WITH NOLOCK

    SELECT TOP 10  Nodes.NodeID AS NodeID,  AVG(ResponseTime.Availability) AS Availability

    FROM Orion.Nodes AS Nodes

        INNER JOIN Orion.NodesCustomProperties AS Custom ON (Nodes.NodeID = Custom.NodeID)

        INNER JOIN Orion.ResponseTime AS ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

    WHERE ResponseTime.DateTime >= '2017-07-25 12:00:00'

    GROUP BY NodeID

    WITH NOLOCK

    Another factor can be limitations. Do you have them?

  • to be clear; this was run using SWQL studio on the polling engine, or from a remote location?

    test it local to your polling engine; the idea is to eliminate the risk of network vs application in this scenario.

    if it runs on the polling engine without issue, but fails to run from a remote location, then it's almost assuredly a network issue. if it fails on both, then I would work through the steps mesverrum​ outlines below.