# Filter expression VS query in SQL manager

**URL:** <https://community.novulo.com/t/filter-expression-vs-query-in-sql-manager/770>\
**Category:** \[Q&A\] General\
**Created:** [July 30, 2025, 1:52pm UTC](https://community.novulo.com/t/filter-expression-vs-query-in-sql-manager/770 "2025-07-30T13:52:22Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joeri](https://yyz2.discourse-cdn.com/flex008/user_avatar/community.novulo.com/joeri/32/428_2.png) [@Joeri](https://community.novulo.com/u/Joeri)\
**Post date:** [July 30, 2025, 1:52pm UTC](https://community.novulo.com/t/filter-expression-vs-query-in-sql-manager/770/1 "2025-07-30T13:52:22Z")

</div>

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

 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/b/b280523c5e5049b4545eef67cf1492adb6c05689.png)

The expression filter for options is:

 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/8/8aa1898e1da618992fc73c570e380487e3eff850.png)

```auto
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:

```auto
 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:

```sql
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?

---

<div class="post-metadata">

**Author:** ![Helena](https://avatars.discourse-cdn.com/v4/letter/h/c68b51/32.png) [@Helena](https://community.novulo.com/u/Helena)\
**Post date:** [July 31, 2025, 9:36am UTC](https://community.novulo.com/t/filter-expression-vs-query-in-sql-manager/770/2 "2025-07-31T09:36:09Z")

</div>

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?