Filter expression VS query in SQL manager

[Q&A] General
1

In a REST data endpoint, we have a field called options, which is a list of objects:

image
image1365×961 121 KB

The expression filter for options is:

image
image1293×757 71 KB

boolean:and([
 this.response_scale.equals(parent.question().response_scale),
 boolean:or([
  parent.question_connection().is_filter_options.equals(yesno:load(0)),
  this.in({
   questionconnectionfilters,
   this.my_option,
   this.question_connection.equals(parent.parent.question_connection())
  })
 ]),
 boolean:if(
  recommendationanswers:first({
   recommendationanswers,
   this,
   boolean:and([
    this.question_connection.equals(parent.parent.question_connection()),
    this.recommendation.shopping_cart.equals(%shopping_cart)
   ])
  }).has_possible_response_options.equals(yesno:load(1)),
  this.in({
   possibleresponseoptions,
   this.response_option,
   boolean:and([
    this.recommendation_answer.equals(
     recommendationanswers:first({
      recommendationanswers,
      this,
      boolean:and([
       this.question_connection.equals(parent.parent.parent.question_connection()),
       this.recommendation.shopping_cart.equals(%shopping_cart)
      ])
     })
    )
   ])
  }),
  true
 )
])

Because of this filter, this endpoint takes around 15 seconds. When we remove this part:

 boolean:if(
  recommendationanswers:first({
   recommendationanswers,
   this,
   boolean:and([
    this.question_connection.equals(parent.parent.question_connection()),
    this.recommendation.shopping_cart.equals(%shopping_cart)
   ])
  }).has_possible_response_options.equals(yesno:load(1)),
  this.in({
   possibleresponseoptions,
   this.response_option,
   boolean:and([
    this.recommendation_answer.equals(
     recommendationanswers:first({
      recommendationanswers,
      this,
      boolean:and([
       this.question_connection.equals(parent.parent.parent.question_connection()),
       this.recommendation.shopping_cart.equals(%shopping_cart)
      ])
     })
    )
   ])
  }),
  true
 )

The endpoint takes around 2 sec.

When we extract this exact query and evaluate it in the Microsoft SQL studio it takes around 1 second. This is the query that we extracted:

SELECT q0t1.[id],
       q0t1.[reference],
       q0t1.[description],
       q0t1.[id],
       CASE
           WHEN (q0t1.[response_scale]=q1t3.[response_scale]
                 AND q0t1.[response_scale] IS NOT NULL
                 AND q1t3.[response_scale] IS NOT NULL) THEN 1
           ELSE 0
       END,
       CASE
           WHEN ((q1t4.[is_filter_options]=0
                  AND q1t4.[is_filter_options] IS NOT NULL)
                 OR (q0t1.[id] IN
                       (SELECT q2t1.[my_option]
                        FROM [questionconnectionfilters] AS q2t1
                        WHERE ((q2t1.[question_connection]=q1t2.[question_connection_1]
                                AND q2t1.[question_connection] IS NOT NULL
                                AND q1t2.[question_connection_1] IS NOT NULL))))) THEN 1
           ELSE 0
       END,
       CASE
           WHEN (q0t1.[id] IN
                   (SELECT q6t1.[response_option]
                    FROM [possibleresponseoptions] AS q6t1
                    WHERE ((q6t1.[recommendation_answer]=
                              (SELECT TOP 1 q7t1.[id]
                               FROM ([recommendationanswers] AS q7t1
                                     LEFT JOIN [recommendations] AS q7t2 ON q7t2.id=q7t1.[recommendation])
                               WHERE (((q7t1.[question_connection]=q1t2.[question_connection_1]
                                        AND q7t1.[question_connection] IS NOT NULL
                                        AND q1t2.[question_connection_1] IS NOT NULL)
                                       AND (q7t2.[shopping_cart]=q4t2.[shopping_cart]
                                            AND q7t2.[shopping_cart] IS NOT NULL
                                            AND q4t2.[shopping_cart] IS NOT NULL))))
                            AND q6t1.[recommendation_answer] IS NOT NULL
                            AND
                              (SELECT TOP 1 q7t1.[id]
                               FROM ([recommendationanswers] AS q7t1
                                     LEFT JOIN [recommendations] AS q7t2 ON q7t2.id=q7t1.[recommendation])
                               WHERE (((q7t1.[question_connection]=q1t2.[question_connection_1]
                                        AND q7t1.[question_connection] IS NOT NULL
                                        AND q1t2.[question_connection_1] IS NOT NULL)
                                       AND (q7t2.[shopping_cart]=q4t2.[shopping_cart]
                                            AND q7t2.[shopping_cart] IS NOT NULL
                                            AND q4t2.[shopping_cart] IS NOT NULL)))) IS NOT NULL)))) THEN 1
           ELSE 0
       END
FROM [responsescaleoptions] AS q0t1 CROSS APPLY ((
                                                    (SELECT 0 AS nr, 366 AS id)) AS q1t1
                                                 LEFT JOIN [questionsforpage] AS q1t2 ON q1t2.id=q1t1.[id]
                                                 LEFT JOIN [questions] AS q1t3 ON q1t3.id=q1t2.[question_1]
                                                 LEFT JOIN [questionconnections] AS q1t4 ON q1t4.id=q1t2.[question_connection_1]) CROSS APPLY ((
                                                                                                                                                  (SELECT 0 AS nr, 1482306 AS id)) AS q4t1
                                                                                                                                               LEFT JOIN [recommendations] AS q4t2 ON q4t2.id=q4t1.[id])
ORDER BY q0t1.[interpretation_score] ASC

Why is the filter not in the WHERE part of the query, but in the SELECT? This results in enormous amounts of records, only in the SQL manager. Is the processing after the query taking 14 seconds and why?

How can we optimize this in our REST data endpoint?

1 Like
2

Ik probeer even in mensentaal deze vraag te formuleren.
Selecteer response options die:

  • EN horen bij de response scale van mijn datadefinitie.question()
  • EN per optie kijk je OF je ze niet hoeft te filteren
    OF dat er een filter is van deze optie naar de eerder bekeken questionconnection
  • EN ALS er een random antwoord bij de winkelwagen een optie had (maakt niet uit welke, we nemen er zomaar eentje, ook als er een andere zou zijn die iets anders zou zeggen),
    DAN moet het de voorgestelde antwoordoptie zijn uit die cart,
    ANDERS doet deze voorwaarde niet mee.

In de WHERE zien we SQL naar de responsescaleoptions kijken. Daar ben ik het mee eens, dat is wat we zoeken blijkbaar. (Soms een, soms meer)
Maar we zien ook dat hij niet een link naar een cart maakt. Dat lijkt te komen omdat je namelijk soms een en soms meer antwoorden wilt, dus is dat verschil niet van te voren te maken. Je wilt voor alle opties bekijken of ze aan bovenstaande voorwaarden voldoen.
Anders zou ik in de definitie al verwachten dat er een filter op de cart zit.

Zou het niet handig zijn om dat laatste if stukje de sortering te maken?

1 Like