title_wip

### 1. Enable Performance Mode for Heavy Workloads[​](#use-performance-mode "Direct link to 1. Enable Performance Mode for Heavy Workloads")

For aggregation-heavy workloads where you don't need exact pandas output format (row order, MultiIndex columns, dtype corrections), enable performance mode for maximum throughput:

```
from chdb.datastore.config import config

config.use_performance_mode()

# Now all operations use SQL-first execution with no pandas overhead:
# - Parallel Parquet reading (no preserve_order)
# - Single-SQL aggregation (filter+groupby in one query)
# - No row-order preservation overhead
# - No MultiIndex, no dtype corrections
result = (ds
    .filter(ds['amount'] > 100)
    .groupby('region')
    .agg({'amount': ['sum', 'mean', 'count']})
)
```

**Expected improvement**: Up to 2-8x faster for filter+groupby workloads, reduced memory usage for large Parquet files.

See [Performance Mode](/docs/chdb/configuration/performance-mode) for full details.

### 2. Use Parquet Instead of CSV[​](#use-parquet "Direct link to 2. Use Parquet Instead of CSV")

```
# CSV: Slower, reads entire file
ds = pd.read_csv("data.csv")

# Parquet: Faster, columnar, compressed
ds = pd.read_parquet("data.parquet")

# Convert once, benefit forever
df = pd.read_csv("data.csv")
df.to_parquet("data.parquet")
```

**Expected improvement**: 3-10x faster reads

### 3. Filter Early[​](#filter-early "Direct link to 3. Filter Early")

```
# Good: Filter first, then aggregate
result = (ds
    .filter(ds['date'] >= '2024-01-01')  # Reduce data early
    .groupby('category')['amount'].sum()
)

# Less optimal: Process all data
result = (ds
    .groupby('category')['amount'].sum()
    .filter(ds['sum'] > 1000)  # Filter too late
)
```

### 4. Select Only Needed Columns[​](#select-only-needed-columns "Direct link to 4. Select Only Needed Columns")

```
# Good: Column pruning
result = ds.select('name', 'amount').filter(ds['amount'] > 100)

# Less optimal: All columns loaded
result = ds.filter(ds['amount'] > 100)  # Loads all columns
```

### 5. Leverage SQL Aggregations[​](#leverage-sql-aggregations "Direct link to 5. Leverage SQL Aggregations")

```
# GroupBy is where DataStore shines
# Up to 20x speedup!
result = ds.groupby('category').agg({
    'amount': ['sum', 'mean', 'count', 'max'],
    'quantity': 'sum'
})
```

### 6. Use head() Instead of Full Queries[​](#use-head "Direct link to 6. Use head() Instead of Full Queries")

```
# Don't load entire result if you only need a sample
result = ds.filter(ds['type'] == 'A').head(100)  # LIMIT 100

# Avoid this for large results
# result = ds.filter(ds['type'] == 'A').to_df()  # Loads everything
```

### 7. Batch Operations[​](#batch-operations "Direct link to 7. Batch Operations")

```
# Good: Single execution
result = ds.filter(ds['x'] > 10).filter(ds['y'] < 100).to_df()

# Bad: Multiple executions
result1 = ds.filter(ds['x'] > 10).to_df()  # Execute
result2 = result1[result1['y'] < 100]       # Execute again
```

### 8. Use explain() to Optimize[​](#use-explain "Direct link to 8. Use explain() to Optimize")

```
# View the query plan before executing
query = ds.filter(...).groupby(...).agg(...)
query.explain()  # Check if operations are pushed down

# Then execute
result = query.to_df()
```

---

## Profiling Your Workload[​](#profiling "Direct link to Profiling Your Workload")

### Enable Profiling[​](#enable-profiling "Direct link to Enable Profiling")

```
from chdb.datastore.config import config, get_profiler

config.enable_profiling()

# Run your workload
result = your_pipeline()

# View report
profiler = get_profiler()
profiler.report()
```

### Identify Bottlenecks[​](#identify-bottlenecks "Direct link to Identify Bottlenecks")

```
Performance Report
==================
Step                    Duration    % Total
----                    --------    -------
SQL execution           2.5s        62.5%     <- Bottleneck!
read_csv                1.2s        30.0%
Other                   0.3s        7.5%
```

### Compare Approaches[​](#compare-approaches "Direct link to Compare Approaches")

```
# Test approach 1
profiler.reset()
result1 = approach1()
time1 = profiler.get_steps()[-1]['duration_ms']

# Test approach 2
profiler.reset()
result2 = approach2()
time2 = profiler.get_steps()[-1]['duration_ms']

print(f"Approach 1: {time1:.0f}ms")
print(f"Approach 2: {time2:.0f}ms")
```

---

## Best Practices Summary[​](#summary "Direct link to Best Practices Summary")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Practice Impact|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Enable performance mode 2-8x faster for aggregation workloads|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Use Parquet files 3-10x faster reads|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Filter early Reduce data processing|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Select needed columns Reduce I/O and memory|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Use GroupBy/aggregations Up to 20x faster|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Batch operations Avoid repeated execution|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Profile before optimizing Find real bottlenecks|  |  |  |  | | --- | --- | --- | --- | | Use explain() Verify query optimization|  |  | | --- | --- | | Use head() for samples Avoid full table scans | | | | | | | | | | | | | | | | | | | |

---

## Quick Decision Guide[​](#decision "Direct link to Quick Decision Guide")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Your Workload Recommendation|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | GroupBy/aggregation Use DataStore|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Complex multi-step pipeline Use DataStore|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Large files with filters Use DataStore|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Simple slice operations Either (comparable performance)|  |  |  |  | | --- | --- | --- | --- | | Custom Python lambda functions Use pandas or convert late|  |  | | --- | --- | | Very small data (<1,000 rows) Either (negligible difference) | | | | | | | | | | | | | |

Tip

For automatic optimal engine selection, use `config.set_execution_engine('auto')` (default).
For maximum throughput on aggregation workloads, use `config.use_performance_mode()`.
See [Execution Engine](/docs/chdb/configuration/execution-engine) and [Performance Mode](/docs/chdb/configuration/performance-mode) for details.