أداء أحمال العمل واقتصاديات التخزين

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.executescript("CREATE TABLE item(id INTEGER PRIMARY KEY,name TEXT); INSERT INTO item VALUES(1,'A'),(2,'B'),(3,'C');")
    statements = []
    db.set_trace_callback(statements.append)
    ids = [1, 2, 3]
    separate = [db.execute("SELECT name FROM item WHERE id=?", (key,)).fetchone()[0] for key in ids]
    first_count = sum(sql.startswith("SELECT") for sql in statements)
    statements.clear()
    placeholders = ",".join("?" for _ in ids)
    batched = [row[0] for row in db.execute(f"SELECT name FROM item WHERE id IN ({placeholders}) ORDER BY id", ids)]
    batch_count = sum(sql.startswith("SELECT") for sql in statements)
    db.set_trace_callback(None)
    print("names", batched)
    print("same", separate == batched)
    print("select_calls", first_count, batch_count)

names ['A', 'B', 'C']
same True
select_calls 3 1

ينتج المساران A وB وC. يرصد التتبع المحلي ثلاث عمليات SELECT مقابل واحدة، ولا يقيس كلفة التطبيق الكلية.

CREATE TABLE ordinary(code TEXT PRIMARY KEY NOT NULL,value TEXT);
CREATE TABLE packed(code TEXT PRIMARY KEY NOT NULL,value TEXT) WITHOUT ROWID;
INSERT INTO ordinary VALUES('A','one');
INSERT INTO packed VALUES('A','one');
SELECT name,wr FROM pragma_table_list WHERE name IN('ordinary','packed') ORDER BY name;
SELECT code,value FROM packed ORDER BY code;

name|wr
ordinary|0
packed|1

code|value
A|one

تميز البيانات الوصفية ordinary ذات wr=0 من packed ذات wr=1. ويبقى صف التطبيق A/one بغض النظر عن مقارنة التخطيط.