# Optimizing Complex Searches

Complex EQL queries, especially those involving multiple relationship checks, can sometimes be slow or time out. Computed properties can help by pre-calculating parts of these complex conditions, making the final query much faster.

## A Real-World Example

Let's look at a common use case: finding all dashboards that depend on table defined by a dby model but don't depend on any raw tables not defined by dbt.

### The Direct Approach

You might try to write this query directly:

```eql
type ~ 'dashboard' AND HAS upstream(type="table" and defined_by='dbt') AND NOT HAS upstream(type = 'table' and (defined_by is null or defined_by != "dbt"))
```

This query needs to:

1. Find all dashboards
2. For each dashboard, check all upstream dependencies for dbt models
3. For each dashboard, check all upstream dependencies for tables
4. Combine these conditions

With a large data model, this query can easily time out (default timeout is 10 seconds) because it needs to traverse the entire dependency graph multiple times.

### The Optimized Approach

Instead, we can break this into two parts:

1. First, create a computed boolean property that pre-calculates the expensive part of the query
2. Then use this property in a simpler query

#### Step 1: Create the Computed Property

Create a new computed boolean property:

1. Name: `has_table_in_upstream`
2. Description: "Indicates whether this resource has any raw tables in its upstream dependencies"
3. Type: Computed Boolean
4. Scope: `type ~ 'dashboard'`
5. Condition: `HAS upstream(type = 'table')`

\[Screenshot: Creating the computed property]

This property will be calculated once for each dashboard and stored, rather than being computed every time you run a query.

#### Step 2: Use the Property

Now you can write a much simpler and faster query:

```eql
type ~ 'dashboard' AND HAS upstream(type = 'dbt_model') AND has_table_in_upstream IS FALSE
```

This query is faster because:

* The expensive `HAS upstream(type = 'table')` check is pre-computed
* The query only needs to check for dbt model dependencies
* The boolean property check is a simple lookup

## Best Practices for Query Optimization

1. **Identify Expensive Parts**
   * Relationship checks (`HAS upstream`, `HAS downstream`) are expensive
   * Multiple relationship checks compound the performance impact
   * Negations of relationship checks (`NOT HAS`) are particularly expensive
2. **Create Focused Properties**
   * Use the `scope` field to limit where the property is calculated
   * This reduces computation time and storage needs
   * Makes the property's purpose clear to other users
3. **Document Your Properties**
   * Add clear descriptions explaining what the property represents
   * Include example queries in the description
   * Note any assumptions or limitations
4. **Monitor and Maintain**
   * Check if the property is still being used
   * Review if the property needs to be recalculated when data changes
   * Consider cleaning up unused properties

## When to Use This Pattern

Consider creating a computed property for optimization when:

* Your queries frequently time out
* You're using the same complex conditions in multiple queries
* You need to negate relationship checks (`NOT HAS`)
* You're combining multiple relationship checks in one query

## Example Use Cases

1. **Data Quality Monitoring**

   ```eql
   # Pre-compute
   name: has_quality_issues
   scope: type = 'dbt_model'
   condition: HAS downstream(type = 'quality_check' AND status = 'failed')
   ```
2. **Dependency Analysis**

   ```eql
   # Pre-compute
   name: is_isolated_dashboard
   scope: type = 'dashboard'
   condition: NOT HAS upstream(TRUE)
   ```
3. **Usage Tracking**

   ```eql
   # Pre-compute
   name: has_recent_usage
   scope: type IN ('dashboard', 'look')
   condition: total_views_30d > 0
   ```