SWQL Query timing out, but the same query runs when i put it through compliance report.

hi Everyone,

i have this query which is suppose to provide the nodes which has percentage utilization above 75% for the last month, this query is run on a monthly basis, it was working previously but now the query has started to time out for some reason.

SELECT   DISTINCT ( InterfaceID) AS Nuddles,NodeID
FROM Orion.NPM.Interfaces
WHERE Alias LIKE '%ISP%' AND ( Interfaces.Traffic.DateTime BETWEEN '2022-03-01' AND '2022-04-01' )
AND ( (Interfaces.Traffic.OutPercentUtil >75 )OR (Interfaces.Traffic.InPercentUtil >75 ))

Find more posts tagged with

swql query timeout

Accepted answers

sum_giais

Do either of these queries make a difference for you?

The first query here, is yours just re-written essentially with minor syntax changes.

SELECT DISTINCT
     i.InterfaceID AS [Nuddles]
    ,i.NodeID
FROM Orion.NPM.Interfaces AS i
WHERE ( 
        i.Alias LIKE '%ISP%'
    AND ( i.Traffic.DateTime BETWEEN '2022-03-01' AND '2022-04-01' )
    AND ( i.Traffic.OutPercentUtil > 75 OR i.Traffic.InPercentUtil > 75 )
)

The second should get you the same resulting data, but looking directly at the traffic data source instead of just including it as a where clause. I think this will be the better of the two in terms of performance. Sometimes using native SWQL navigational properties can get us into scenarios where we're not accessing the data in the best way possible which is my hunch as to what's occurring here.

SELECT DISTINCT
     t.InterfaceID AS [Nuddles]
    ,t.NodeID
FROM Orion.NPM.InterfaceTraffic AS t
WHERE ( 
        t.Interface.Alias LIKE '%ISP%'
    AND ( t.DateTime BETWEEN '2022-03-01' AND '2022-04-01' )
    AND ( t.OutPercentUtil > 75 OR t.InPercentUtil > 75 )
)

Hope one of these can help -

All comments

jackjones10

for more context, if you shorten the time period to 7 days, the query works.

jackjones10

sum_giais

Do either of these queries make a difference for you?

The first query here, is yours just re-written essentially with minor syntax changes.

SELECT DISTINCT
     i.InterfaceID AS [Nuddles]
    ,i.NodeID
FROM Orion.NPM.Interfaces AS i
WHERE ( 
        i.Alias LIKE '%ISP%'
    AND ( i.Traffic.DateTime BETWEEN '2022-03-01' AND '2022-04-01' )
    AND ( i.Traffic.OutPercentUtil > 75 OR i.Traffic.InPercentUtil > 75 )
)

SELECT DISTINCT
     t.InterfaceID AS [Nuddles]
    ,t.NodeID
FROM Orion.NPM.InterfaceTraffic AS t
WHERE ( 
        t.Interface.Alias LIKE '%ISP%'
    AND ( t.DateTime BETWEEN '2022-03-01' AND '2022-04-01' )
    AND ( t.OutPercentUtil > 75 OR t.InPercentUtil > 75 )
)

Hope one of these can help -

jackjones10

Hi sum_giais,

i tried both the queries , 1st was timing out but the 2nd query worked like a charm !! thank you very much for the help. very much appreciated.

i have even extended the query duration and it doesn't take much time at all, huge difference in terms of time taken[ comparing to old query when it was working in the past]

thank you once again sum_giais !!

sum_giais

Perfect glad to help!

KMSigma.SWI

You really want to blow you mind? Add:

WITH QUERYSTATS

to the end of your query.

jackjones10

Hi KMSigma,

i do get some information back from it.

SET DATEFIRST 7;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT DISTINCT [T1].[InterfaceID] AS C1
      , [T1].[NodeID] AS C2
FROM (
      SELECT it.*
            , CASE
                  WHEN ISNULL(i.InBandwidth, 0) = 0
                        THEN 0
                  ELSE ISNULL(it.In_Averagebps, 0) / i.InBandwidth * 100
                  END InPercentUtil
            , CASE
                  WHEN ISNULL(i.OutBandwidth, 0) = 0
                        THEN 0
                  ELSE ISNULL(it.Out_Averagebps, 0) / i.OutBandwidth * 100
                  END OutPercentUtil
      FROM [dbo].[InterfaceTraffic] it
      LEFT JOIN [dbo].[Interfaces] i
            ON it.InterfaceID = i.InterfaceID
      ) AS T1
INNER JOIN dbo.Interfaces AS T2
      ON [T1].[InterfaceID] = [T2].[InterfaceID]
            AND 0 = 0
WHERE CAST('2022-02-01' AS DATETIME2) <= [T1].[DateTime]
      AND [T1].[DateTime] <= CAST('2022-04-01' AS DATETIME2)
      AND ([T2].[InterfaceAlias] LIKE '%ISP%')
      AND (
            [T1].[OutPercentUtil] > 75
            OR [T1].[InPercentUtil] > 75
            )

but don't know what to make of it.