title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/guides/pandas-performance.md)

DataStore delivers significant performance improvements over pandas for many operations. This guide explains why and how to optimize your workloads.

## Why DataStore Is Faster[​](#why-faster "Direct link to Why DataStore Is Faster")

### 1. SQL Pushdown[​](#sql-pushdown "Direct link to 1. SQL Pushdown")

Operations are pushed down to the data source:

```
# pandas: Loads ALL data, then filters in memory
df = pd.read_csv("huge.csv")       # Load 10GB
df = df[df['year'] == 2024]        # Filter in Python

# DataStore: Filter at source
ds = pd.read_csv("huge.csv")       # Just metadata
ds = ds[ds['year'] == 2024]        # Filter in SQL
df = ds.to_df()                    # Only load filtered data
```

### 2. Column Pruning[​](#column-pruning "Direct link to 2. Column Pruning")

Only needed columns are read:

```
# DataStore: Only reads name, age columns
ds = pd.read_parquet("wide_table.parquet")
result = ds.select('name', 'age').to_df()

# vs pandas: Reads all 100 columns, then selects
```

### 3. Lazy Evaluation[​](#lazy-evaluation "Direct link to 3. Lazy Evaluation")

Multiple operations compile to one query:

```
# DataStore: One optimized SQL query
result = (ds
    .filter(ds['amount'] > 100)
    .groupby('region')
    .agg({'amount': 'sum'})
    .sort('sum', ascending=False)
    .head(10)
    .to_df()
)

# Becomes:
# SELECT region, SUM(amount) FROM data
# WHERE amount > 100
# GROUP BY region ORDER BY sum DESC LIMIT 10
```

---

## Benchmark: DataStore vs pandas[​](#benchmark "Direct link to Benchmark: DataStore vs pandas")

### Test Environment[​](#test-environment "Direct link to Test Environment")

- Data: 10 million rows
- Hardware: Standard laptop
- File format: CSV

### Results[​](#results "Direct link to Results")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation pandas (ms) DataStore (ms) Winner|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | GroupBy count 347 17 **DataStore (19.93x)**| Combined ops 1,535 234 **DataStore (6.56x)**| Complex pipeline 2,047 380 **DataStore (5.39x)**| MultiFilter+Sort+Head 1,963 366 **DataStore (5.36x)**| Filter+Sort+Head 1,537 350 **DataStore (4.40x)**| Head/Limit 166 45 **DataStore (3.69x)**| Ultra-complex (10+ ops) 1,070 338 **DataStore (3.17x)**| GroupBy agg 406 141 **DataStore (2.88x)**| Select+Filter+Sort 1,217 443 **DataStore (2.75x)**| Filter+GroupBy+Sort 466 184 **DataStore (2.53x)**| Filter+Select+Sort 1,285 533 **DataStore (2.41x)**| Sort (single) 1,742 1,197 **DataStore (1.45x)**| Filter (single) 276 526 Comparable|  |  |  |  | | --- | --- | --- | --- | | Sort (multiple) 947 1,477 Comparable | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Key Insights[​](#insights "Direct link to Key Insights")

1. **GroupBy operations**: DataStore up to **19.93x faster**
2. **Complex pipelines**: DataStore **5-6x faster** (SQL pushdown benefit)
3. **Simple slice operations**: Performance comparable - difference negligible
4. **Best use case**: Multi-step operations with groupby/aggregation
5. **Zero-copy**: `to_df()` has no data conversion overhead

---

## When DataStore Wins[​](#when-datastore-wins "Direct link to When DataStore Wins")

### Heavy Aggregations[​](#heavy-aggregations "Direct link to Heavy Aggregations")

```
# DataStore excels: 19.93x faster
result = ds.groupby('category')['amount'].sum()
```

### Complex Pipelines[​](#complex-pipelines "Direct link to Complex Pipelines")

```
# DataStore excels: 5-6x faster
result = (ds
    .filter(ds['date'] >= '2024-01-01')
    .filter(ds['amount'] > 100)
    .groupby('region')
    .agg({'amount': ['sum', 'mean', 'count']})
    .sort('sum', ascending=False)
    .head(20)
)
```

### Large File Processing[​](#large-file-processing "Direct link to Large File Processing")

```
# DataStore: Only loads what you need
ds = pd.read_parquet("huge_file.parquet")
result = ds.filter(ds['id'] == 12345).to_df()  # Fast!
```

### Multiple Column Operations[​](#multiple-column-operations "Direct link to Multiple Column Operations")

```
# DataStore: Combines into single SQL
ds['total'] = ds['price'] * ds['quantity']
ds['is_large'] = ds['total'] > 1000
ds = ds.filter(ds['is_large'])
```

---

## When pandas Is Comparable[​](#when-pandas-wins "Direct link to When pandas Is Comparable")

In most scenarios, DataStore matches or exceeds pandas performance. However, pandas may be slightly faster in these specific cases:

### Small Datasets (<1,000 rows)[​](#small-datasets "Direct link to Small Datasets (<1,000 rows)")

```
# For very small datasets, overhead is minimal for both
# Performance difference is negligible
small_df = pd.DataFrame({'x': range(100)})
```

### Simple Slice Operations[​](#simple-slice-operations "Direct link to Simple Slice Operations")

```
# Single slice operations without aggregation
df = df[df['x'] > 10]  # pandas slightly faster
ds = ds[ds['x'] > 10]  # DataStore comparable
```

### Custom Python Lambda Functions[​](#custom-python-functions "Direct link to Custom Python Lambda Functions")

```
# pandas required for custom Python code
def complex_function(row):
    return custom_logic(row)

df['result'] = df.apply(complex_function, axis=1)
```

Important

Even in scenarios where DataStore is "slower", performance is typically **on par with pandas** - the difference is negligible for practical use. DataStore's advantages in complex operations far outweigh these edge cases.

For fine-grained control over execution, see [Execution Engine Configuration](/docs/chdb/configuration/execution-engine).

---

## Zero-Copy DataFrame Integration[​](#zero-copy "Direct link to Zero-Copy DataFrame Integration")

DataStore uses **zero-copy** for reading and writing pandas DataFrames. This means:

```
# to_df() does NOT copy data - it's a zero-copy operation
result = ds.filter(ds['x'] > 10).to_df()  # No data conversion overhead

# Same for creating DataStore from DataFrame
ds = DataStore(existing_df)  # No data copy
```

**Key implications:**

- `to_df()` is essentially free - no serialization or memory copying
- Creating DataStore from pandas DataFrame is instant
- Memory is shared between DataStore and pandas views

---

## Optimization Tips[​](#tips "Direct link to Optimization Tips")

### 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']})
)
```