Optimizing Web Reports Query for Better Performance

Reporting
2

The “go-to” explanation on this is at Efficient Session Relevance Query for Computer Properties

Basically, the way your query is written now behaves like a series of nested loops. In the outer loop, you are retrieving a list of bes fixlets matching your criteria. In the first inner loop, you are iterating through all of the results of each fixlet. Then futher within the inner loop, you retrieve a set of members of a computer group and check whether the current result is for a computer in that group.

In practice, what this means is that if there are a hundred fixlets that match your criteria, you have to lookup the computer group and retrieve its member set a hundred times.

This should be much more efficient if we lookup the fixlets first (creating a ‘bes fixlet set’), lookup the computers once (building a ‘bes computer set’, and then loop through the members of each set retrieving results (bes computer, bes fixlet).

Try changing the end of your query from

of results whose (
    computer of it is contained by set of (members of bes computer groups whose (id of it = 811))
) 
of bes fixlets whose (
    (   
        (
            source severity of it as lowercase = "critical"
        ) or 
        (
            source severity of it as lowercase = "important"
        )
    ) and 
    (
        source release date of it < (current date - 1 * week)
    )
)

to

of (results (elements of item 0 of it, elements of item 1 of it) ) of 
   (
  
   member set of bes computer groups whose (id of it = 811)

 , set of bes fixlets whose (
      (   
          (
              source severity of it as lowercase = "critical"
          ) or 
          (
              source severity of it as lowercase = "important"
           )
        ) and 
      (
          source release date of it < (current date - 1 * week)
      )
    )
  )
)
3 Likes