I would like to know the best approach to adopt when building a dynamic query. I have a Search table that captures various optional search criteria such as:
- Taxonomy (single selection) or Taxonomy code pattern e.g. YYFC
- Has list of Features (multiple select)
- Date range: date a product was lunched (date range or any date)
See screen-shot below:
Each one of the conditions can be optional so if nothing is entered all products would be returned.
One approach is to use virtual columns for each one of the conditions above. Then use list subtraction to remove products that do not match the criteria, starting from the entire list of products
 Pattern results:
 Feature results:
 Data range results:
The results virtual column applies the conditions using list subtraction as follows:
( Product[ProductUUID] - [Pattern Results] - [Feature results] ),
[Date range results]
I.e. start with entire population of ALL products.
Subtract those products that do not have the taxonomy code pattern selected
Subtract those products that do not have all the features selected
INTERSECT the remaining products with those launched within the date period.
Note when nothing is entered for one of the conditions the result of that condition = zero so there will be nothing to subtract.
Another approach is use the INTERSECT function on pairs of the conditions above, as you can only use the Intersect function with two conditions as a time.
Q1 Which of the two options outlined is more efficient, or is there a 3rd option which is better?
Q2 Is there an efficiency cost using virtual columns and hence should these be real columns in the DB?
At the moment the list subtraction approach works, but there appears to be a delay on all the results appearing. I.e. it quickly comes back with some results, then a 15-30 seconds later the full results set appears.