SQL tuple filter performance

create.sql

drop table if exists store_items;
create table store_items (
    store_id  int,
    item_id   int,
    constraint store_items__pk primary key (store_id, item_id)
);


with recursive

items(item_id) as (select 1 union all select item_id + 1 from items where item_id < 10000),
stores(store_id) as (select item_id from items where item_id <= 1500)

insert into store_items
    select store_id, item_id
    from stores, items
;

main.py

# pip install duckdb==1.0.0 db_query_profiler==0.0.6
import sqlite3

import db_query_profiler
import duckdb


def main() -> None:
    with open("create.sql") as f:
        create_sql = f.read()

    sqlite_connector = sqlite3.connect(":memory:")
    duckdb_connector = duckdb.connect()

    print("Creating tables...")
    sqlite_connector.executescript(create_sql)
    duckdb_connector.execute(create_sql)

    print("\nTiming SQLite queries...")
    db_query_profiler.time_queries(
        conn=sqlite_connector.cursor(),
        repeat=10_000,
        directory="queries",
    )

    print("\nTiming DuckDB queries...")
    db_query_profiler.time_queries(
        conn=duckdb_connector.cursor(),
        repeat=10_000,
        directory="queries",
    )


if __name__ == "__main__":
    main()

queries_query-1.sql

/* put into a `queries` directory */
select *
from store_items
where store_id = 999 and item_id = 42

queries_query-2.sql

/* put into a `queries` directory */
select *
from store_items
where (store_id, item_id) = (999, 42)

SQL tuple filter performance

Creating tables...

Timing SQLite queries...
Start time: 2024-08-31 09:23:05.697240
----------------------------------------
100%|██████████| 10000/10000 [00:02<00:00, 3370.72it/s]
query-1.sql: 0.00008558s (50.1%)
query-2.sql: 0.00008527s (49.9%)
----------------------------------------
End time: 2024-08-31 09:23:08.676550

Timing DuckDB queries...
Start time: 2024-08-31 09:23:08.676550
----------------------------------------
100%|██████████| 10000/10000 [00:17<00:00, 581.43it/s]
query-1.sql: 0.00050362s (32.0%)
query-2.sql: 0.00106974s (68.0%)
----------------------------------------
End time: 2024-08-31 09:23:25.878387

This is part of the following LinkedIn post:

• https://www.linkedin.com/posts/bill-wallis_sqlwithbill-dataengineering-sql-activity-7236659323903381504-p9BC