Need Help to make my Relevance Query Faster

Reporting
10

I should also say, this query

q: (name of item 0 of it, concatenation ";" of (ip addresses of item 0 of it as string), item 1 of it, item 2 of it, (100 - (item 1 of it as floating point / item 2 of it as floating point * 100)) as integer as string & "%25") of (item 0 of it, number of results (item 0 of it, elements of item 1 of it) whose (relevant flag of it), number of results (item 0 of it, elements of item 1 of it) whose (remediated flag of it or relevant flag of it)) of (elements of item 0 of it, item 1 of it) of (set of subscribed computers of bes sites whose (name of it = "Enterprise Security"), set of fixlets whose (globally visible flag of it = TRUE and exists actions of it and exists source severity whose (it does not contain "Unspecified" and it does not contain "N/A" and it is not "") of it and (source release date of it) > date "01 Jan 2000" and exists results whose (remediated flag of it or relevant flag of it) of it) of bes sites whose (name of it = "Enterprise Security") )

gave me results through the Web Reports QNA page in about 12 seconds. @jgstew, on your first query to measure performance I’m seeing times of three minutes or more:

number of (results it) whose(remediated flag of it OR relevant flag of it) of (subscribed computers of it, fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "") of it ) of bes sites whose (name of it = "Enterprise Security")

I believe this is because the tuple (subscribed computers of it, fixlets whose ()...) operates as two loops…in script language style, this would work like

for (each of the 1,400 subscribed computers)
  for (each of the 13,277 fixlets in the site)
     // This part evaluates 1400x13277 times, for 18,587,800 comparisons
     
     determine whether fixlet is interesting (severity checks)
     
     if the fixlet is interesting based on the severity check...
          get the results for this fixlet on this computer
     end if
  next // next fixlet
next // next computer

The issue with that, is that if there are a thousand computers in the deployment, it has to loop a thousand times through the site just to see which fixlets are interesting.

By using (set of computers, set of fixlets) we create exactly one result - and have only looped through the list of computers one time, and only looped through the fixlets in the site one time.

When I examine the results (elements of set of computers, elements of set of fixlets), at this point I’m only examining the 1,703 fixlets that were once relevant, instead of examining all 13,277 fixlets in the site.

…come to think of it, we should probably also include fixlet flag of it in the fixlets filter, so we can exclude all of the Tasks.

4 Likes