# فصل ۰۷ — مرزها: کِی `sqlite3` بس نیست

<div dir="rtl">

# فصل ۰۷ — مرزها: کِی `sqlite3` بس نیست

## در این فصل چه یاد می‌گیری

شش فصل ابزارِ کارایی جمع کردیم و همه‌شان یک فرضِ نگفته داشتند: **`sqlite3` ابزارِ درستِ این کار است.** این فصل همان فرض را با عدد می‌سنجد.

چهار نخ را هم‌زمان به نوشتن می‌گذاریم و **کار کندتر تمام می‌شود** — بازدهیِ کمتر از یک، نه ۴ برابر. بعد همان چهار نخ را به خواندن می‌گذاریم و **نزدیک به سه برابر سریع‌تر** می‌شود. یک عدد، دو نتیجهٔ کاملاً متضاد، و همان مرزی که کلِ این فصل دربارهٔ آن است.

![یک لولهٔ باریک که چهار خطِ ورودی به آن می‌رسند و فقط یکی در هر لحظه از آن رد می‌شود، در کنارِ چهار لولهٔ موازیِ برداشت](/lesson-assets/t5-s07-limits-of-sqlite/prompt/01-image-one-writer-many-readers.png)

آخر این فصل می‌توانی:

- با عدد نشان بدهی چرا افزودنِ نویسنده سرعت نمی‌آورد و افزودنِ خواننده می‌آورد
- سقفِ تعدادِ پارامترهای یک پرسش را پیدا کنی و پیامِ خطایش را بشناسی
- رشدِ زمانِ اجرا با رشدِ داده را اندازه بگیری به‌جای اینکه فرضش کنی
- بگویی چه چیزی از این دوره به یک پایگاه دادهٔ سرور منتقل می‌شود و چه چیزی نمی‌شود

## قبل از شروع

**از فصلِ ۵:** تراکنش، `commit`، و `database is locked`. **از فصلِ ۱:** میانهٔ چند اجرا.

**دانه‌بندی:** یک سطر از `events` یک رویداد. جدول‌های `bulk` و `events_small` را همین فصل می‌سازد.

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-5-performance/session-07-limits-of-sqlite.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `events` | ۴۰۰٬۰۰۰ | چند ثانیه |
| `books` | ۴۰٬۰۰۰ | کمتر از یک ثانیه |
| `bulk` (همین فصل می‌سازدش) | ۲۰٬۰۰۰ | آنی |
| `events_small` (همین فصل می‌سازدش) | ۴۰٬۰۰۰ | آنی |

<div dir="rtl">

## ۱. چهار نویسنده، و سرعتی که کم شد

بیست هزار سطر را دو جور می‌نویسیم: یک بار چهار دستهٔ پنج‌هزارتایی پشتِ سرِ هم، و یک بار همان چهار دسته **هم‌زمان** روی چهار نخ.

**`threading` را لازم نیست بلد باشی و این فصل هم یادش نمی‌دهد.** فقط این‌قدر بدان که `Thread` یک تابع را می‌گیرد و اجرایش را کنارِ بقیه پیش می‌برد؛ اینجا صرفاً **ابزارِ آزمایش** است تا بشود چند نویسنده را هم‌زمان کرد.

</div>

```python
import sqlite3
import threading

con.execute("CREATE TABLE bulk (id INTEGER PRIMARY KEY, who INTEGER NOT NULL)")
con.commit()


def write_batch(tag, first_id, count):
    """یک اتصال جدا، یک تراکنش، پنج هزار سطر."""
    own = sqlite3.connect(DB_PATH, timeout=30)
    own.executemany("INSERT INTO bulk VALUES (?, ?)",
                    [(first_id + step, tag) for step in range(count)])
    own.commit()
    own.close()


start = time.perf_counter()
for tag in range(4):
    write_batch(tag, 1 + tag * 5000, 5000)
serial_ms = (time.perf_counter() - start) * 1000
print("سطرها بعد از نوشتنِ پشتِ‌سرِهم:", int(q("SELECT COUNT(*) AS n FROM bulk").iloc[0, 0]))

con.execute("DELETE FROM bulk")
con.commit()

threads = [threading.Thread(target=write_batch, args=(tag, 1 + tag * 5000, 5000))
           for tag in range(4)]
start = time.perf_counter()
for thread in threads:
    thread.start()
for thread in threads:
    thread.join()
parallel_ms = (time.perf_counter() - start) * 1000
print("سطرها بعد از نوشتنِ هم‌زمان :", int(q("SELECT COUNT(*) AS n FROM bulk").iloc[0, 0]))
print("دسته‌های متمایز در جدول    :",
      int(q("SELECT COUNT(DISTINCT who) AS n FROM bulk").iloc[0, 0]))
```

```text
سطرها بعد از نوشتنِ پشتِ‌سرِهم: 20000
سطرها بعد از نوشتنِ هم‌زمان : 20000
دسته‌های متمایز در جدول    : 4
```

```python
print("پشتِ سرِ هم:", round(serial_ms, 1), "میلی‌ثانیه")
print("هم‌زمان   :", round(parallel_ms, 1), "میلی‌ثانیه")
print("بازدهی    :", round(serial_ms / parallel_ms, 2), "برابر")
```

```text-approx
پشتِ سرِ هم: 39.4 میلی‌ثانیه
هم‌زمان   : 56.8 میلی‌ثانیه
بازدهی    : 0.69 برابر
```

<div dir="rtl">

**بازدهی کمتر از یک است — یعنی هم‌زمانی نه‌تنها کمکی نکرد، ضرر هم زد.**

دلیلش همان قانونِ فصلِ ۵ است: **در هر لحظه فقط یک اتصال می‌تواند بنویسد.** چهار نخ نوبتی نوشتند، و هزینهٔ قفل و انتظار به کارِ اصلی اضافه شد. **این یک ضعفِ پیاده‌سازی نیست؛ تصمیمِ طراحیِ `SQLite` است** و با هیچ تنظیمی رفع نمی‌شود.

## ۲. چهار خواننده، و نتیجهٔ کاملاً برعکس

حالا همان آزمایش با خواندن:

</div>

```python
def read_batch(rounds):
    """یک اتصال جدا، چند بار یک پرسشِ سنگین."""
    own = sqlite3.connect(DB_PATH)
    for _ in range(rounds):
        own.execute("SELECT COUNT(*) FROM events WHERE kind = ?", ("out",)).fetchall()
    own.close()


start = time.perf_counter()
for _ in range(4):
    read_batch(5)
read_serial_ms = (time.perf_counter() - start) * 1000

readers = [threading.Thread(target=read_batch, args=(5,)) for _ in range(4)]
start = time.perf_counter()
for reader in readers:
    reader.start()
for reader in readers:
    reader.join()
read_parallel_ms = (time.perf_counter() - start) * 1000

print("پرسش‌های اجراشده در هر دو حالت:", 4 * 5)
print("پاسخ همان پرسش:",
      int(q("SELECT COUNT(*) AS n FROM events WHERE kind = ?", ("out",)).iloc[0, 0]))
```

```text
پرسش‌های اجراشده در هر دو حالت: 20
پاسخ همان پرسش: 207831
```

```python
print("پشتِ سرِ هم:", round(read_serial_ms, 1), "میلی‌ثانیه")
print("هم‌زمان   :", round(read_parallel_ms, 1), "میلی‌ثانیه")
print("بازدهی    :", round(read_serial_ms / read_parallel_ms, 2), "برابر")
```

```text-approx
پشتِ سرِ هم: 713.6 میلی‌ثانیه
هم‌زمان   : 257.3 میلی‌ثانیه
بازدهی    : 2.77 برابر
```

<div dir="rtl">

**همان چهار نخ، همان فایل، و این بار نزدیک به سه برابر سریع‌تر.**

پس جملهٔ درست دربارهٔ `SQLite` این نیست که «هم‌زمانی ندارد». این است: **هر تعداد خواننده، دقیقاً یک نویسنده.**

و همین دو عدد، مرزِ تصمیم را می‌سازند: **اگر بارِ کارت خواندن است، این محدودیت شاید هرگز به تو نخورد. اگر نوشتنِ هم‌زمان است، از روزِ اول به آن خواهی خورد.**

> ✅ **چک کن:** در آزمایشِ بخشِ ۱ هر دو راه باید **دقیقاً** ۲۰٬۰۰۰ سطر از ۴ دستهٔ متمایز ساخته باشند. اگر شمارشِ حالتِ هم‌زمان کمتر درآمد، یکی از نخ‌ها خطای قفل گرفته و بی‌صدا رد شده — و آن‌وقت داری زمانِ کارِ ناتمام را با زمانِ کارِ کامل می‌سنجی.

## ۳. سقفی که پیامِ خطا دارد

بعضی مرزها را می‌شود دقیقاً پیدا کرد. این یکی از آن‌هاست: **حداکثر تعدادِ پارامترِ `?` در یک پرسش.**

> 🔧 **اگر کار نکرد:** این خطا معمولاً وقتی می‌آید که فهرستی از شناسه‌ها را از پایتون به یک `IN` می‌دهی و فهرست بزرگ می‌شود:

</div>

```python
for size in (32766, 32767):
    ids = list(range(1, size + 1))
    sql = "SELECT COUNT(*) AS n FROM books WHERE id IN (%s)" % ",".join("?" * size)
    try:
        print(size, "پارامتر →", con.execute(sql, ids).fetchone()[0], "سطر")
    except Exception as error:
        print(size, "پارامتر →", type(error).__module__ + "." + type(error).__name__)
        print("           ", error)
```

```text
32766 پارامتر → 32766 سطر
32767 پارامتر → sqlite3.OperationalError
            too many SQL variables
```

<div dir="rtl">

**۳۲٬۷۶۶ قبول، ۳۲٬۷۶۷ رد.** یک مرزِ تیز، با یک پیامِ روشن. این عدد سقفِ پیش‌فرضِ خودِ `SQLite` است و سازندهٔ هر نسخه می‌تواند عوضش کند، پس روی ماشینِ تو شاید جای دیگری باشد؛ `con.getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER)` مقدارِ همان ماشین را می‌گوید.

**رفعش این نیست که فهرست را تکه‌تکه کنی** — آن راهِ‌حلِ درجه‌دو است. راهِ درست این است که آن فهرست را اصلاً از پایگاه داده بیرون نیاوری: اگر شناسه‌ها خودشان از یک پرسشِ دیگر آمده‌اند، همان پرسش را به‌شکلِ زیرپرسش داخلِ `IN` بگذار و **هیچ پارامتری لازم نمی‌شود.**

و نکتهٔ کلی‌ترش برای هر پایگاه داده‌ای صادق است: **هر جا داری هزاران مقدار را از برنامه به پرسش می‌فرستی، احتمالاً کاری را در برنامه می‌کنی که کارِ پایگاه داده است.**

## ۴. ده برابر داده، چند برابر زمان؟

یک نسخهٔ ده‌درصدی از `events` می‌سازیم — چهل هزار رویدادِ **اولِ** دوره — و همان پرسش را روی هر دو می‌گیریم:

</div>

```python
con.executescript("""
    CREATE TABLE events_small (
        id          INTEGER PRIMARY KEY,
        book_id     INTEGER NOT NULL,
        member_id   INTEGER NOT NULL,
        happened_at TEXT    NOT NULL,
        kind        TEXT    NOT NULL,
        days        INTEGER NOT NULL
    );
    INSERT INTO events_small SELECT * FROM events WHERE id <= 40000;
""")
con.commit()

SMALL = "SELECT COUNT(*) AS n, SUM(days) AS s FROM events_small"
FULL = "SELECT COUNT(*) AS n, SUM(days) AS s FROM events"
print(q(SMALL))
print(q(FULL))
print(q("SELECT MIN(happened_at) AS first, MAX(happened_at) AS last FROM events_small"))
```

```text
       n       s
0  40000  819976
        n        s
0  400000  8200275
                 first                 last
0  2022-01-01 00:02:34  2022-04-20 02:08:42
```

```python
small_ms = timed(SMALL)
full_ms = timed(FULL)
print("۴۰ هزار سطر :", small_ms, "میلی‌ثانیه")
print("۴۰۰ هزار سطر:", full_ms, "میلی‌ثانیه")
print("نسبتِ داده   : 10 برابر")
print("نسبتِ زمان   :", round(full_ms / small_ms, 1), "برابر")
```

```text-approx
۴۰ هزار سطر : 1.75 میلی‌ثانیه
۴۰۰ هزار سطر: 28.23 میلی‌ثانیه
نسبتِ داده   : 10 برابر
نسبتِ زمان   : 16.1 برابر
```

<div dir="rtl">

**ده برابر داده، و بیش از ده برابر زمان.**

نسبتِ زمان بزرگ‌تر از نسبتِ داده درآمد و باید همان‌طور که هست نوشته شود: **«خطی رشد می‌کند» یک فرض است، نه یک قانون.** جدولِ کوچک‌تر تقریباً کامل در حافظه جا می‌شود و جدولِ بزرگ نه، و همین تفاوت خودش را در عدد نشان می‌دهد.

**و این تنها راهِ درستِ پاسخ به پرسشِ «داده‌ام ده برابر شود چه می‌شود؟» است: یک نسخهٔ کوچک‌تر بساز، هر دو را اندازه بگیر، و نسبت را بنویس.** برون‌یابی از یک عدد، حدس است.

## ۵. نوعی که قید نیست

یک تفاوتِ بنیادی با پایگاه دادهٔ سرور، که هیچ ربطی به سرعت ندارد ولی روزی گرانت تمام می‌شود:

</div>

```python
con.executescript("""
    CREATE TABLE loose (n INTEGER);
    CREATE TABLE tight (n INTEGER) STRICT;
""")
con.commit()

con.execute("INSERT INTO loose VALUES (?)", ("سلام",))
con.commit()
print(q("SELECT n, typeof(n) AS t FROM loose"))
print()
try:
    con.execute("INSERT INTO tight VALUES (?)", ("سلام",))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
      n     t
0  سلام  text

sqlite3.IntegrityError
cannot store TEXT value in INTEGER column tight.n
```

<div dir="rtl">

**ستون `INTEGER` اعلام شده بود و یک رشتهٔ فارسی را بی‌صدا قبول کرد.**

در `SQLite` نوعِ ستون یک **تمایل** است نه یک قید: اگر مقدار قابلِ تبدیل باشد تبدیل می‌شود، و اگر نباشد همان‌طور که هست ذخیره می‌شود. در `PostgreSQL` و `MySQL` همین `INSERT` از روزِ اول خطا می‌دهد.

**راهِ‌حلش از نسخه‌های جدیدِ `SQLite` هست و اسمش `STRICT` است** — همان‌طور که جدولِ دوم نشان داد. اگر جدولی می‌سازی که قرار است سال‌ها بماند، `STRICT` بنویس.

و همین‌جا چند تفاوتِ گویشِ دیگر، که **فقط برای خواندن‌اند** و نیازی به اجرا ندارند:

</div>

```sql
-- کلیدِ خودافزا
INTEGER PRIMARY KEY               -- SQLite
GENERATED ALWAYS AS IDENTITY      -- PostgreSQL
AUTO_INCREMENT                    -- MySQL

-- بریدنِ تاریخ به ماه
strftime('%Y-%m', happened_at)    -- SQLite
date_trunc('month', happened_at)  -- PostgreSQL
DATE_FORMAT(happened_at, '%Y-%m') -- MySQL

-- چسباندنِ دو رشته
first_name || ' ' || last_name    -- SQLite و PostgreSQL
CONCAT(first_name, ' ', last_name)-- MySQL
```

<div dir="rtl">

## ۶. چه چیزی منتقل می‌شود و چه چیزی نمی‌شود

</div>

| چیزی که یاد گرفتی | به پایگاه دادهٔ سرور منتقل می‌شود؟ |
|---|---|
| `SELECT`، `WHERE`، `GROUP BY`، `JOIN`، تابعِ پنجره، `CTE` | بله، تقریباً کلمه‌به‌کلمه |
| `NULL` و منطقِ سه‌ارزشی، تلهٔ `NOT IN` | بله، عیناً — این‌ها از استانداردند |
| دانه‌بندی، شمارشِ کنترلی، اعلامِ اینکه چه کسی از قلم افتاد | بله، و مهم‌ترین چیزی است که می‌بری |
| ایدهٔ `index`، گزینشگری، `covering index` | بله، ولی نامِ دستورها و جزئیاتِ نقشه فرق می‌کند |
| `EXPLAIN QUERY PLAN` | نه با همین نام — در `PostgreSQL` می‌شود `EXPLAIN ANALYZE` و خروجی‌اش کاملاً متفاوت است |
| `PRAGMA` و `journal_mode` و `user_version` | نه؛ مخصوصِ `SQLite` است |
| نوعِ ستون به‌عنوان تمایل | نه؛ در بقیه یک قیدِ واقعی است |
| «یک نویسنده» | نه؛ پایگاه دادهٔ سرور برای نویسندهٔ هم‌زمان ساخته شده |

<div dir="rtl">

**سطرِ سوم مهم‌ترین سطرِ این جدول است.** نحوِ `SQL` را در چند روز می‌شود دوباره یاد گرفت؛ عادتِ اینکه هر عدد را با یک شمارشِ دوم بسنجی، چیزی است که کلِ این دوره برای ساختنش نوشته شده.

**و کِی باید کوچ کرد؟** سه نشانه، هر سه اندازه‌گرفتنی:

1. **چند نویسندهٔ هم‌زمان داری** و آزمایشِ بخشِ ۱ روی بارِ واقعی‌ات بازدهیِ کمتر از یک می‌دهد.
2. **داده‌ات آن‌قدر بزرگ است** که آزمایشِ بخشِ ۴ نشان می‌دهد زمان از بودجه‌ات بیرون زده.
3. **چند برنامهٔ جدا روی چند ماشین** باید به همان داده وصل شوند — که `SQLite` اصلاً برایش ساخته نشده، چون یک فایل است نه یک سرویس.

**و اگر هیچ‌کدام از این سه نشانه را نداری، کوچ نکن.** یک فایل که پشتیبان‌گیری‌اش کپی کردنِ همان فایل است، سادگیِ کمی نیست.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در `bulk` یک سطرِ ساختگی از یک نخ، در `events_small` یک رویداد از هر ده رویداد. **با کدام شمارشِ دوم سنجیدی؟** با سه‌تا: هر دو راهِ نوشتن دقیقاً ۲۰٬۰۰۰ سطر از ۴ دستهٔ متمایز ساختند؛ هر دو حالتِ خواندن دقیقاً ۲۰ پرسش اجرا کردند؛ و جمعِ `days` در نسخهٔ کوچک ۸۱۹٬۹۷۶ شد که تقریباً یک‌دهمِ ۸٬۲۰۰٬۲۷۵ است. **چه کسی از قلم افتاد؟** نسخهٔ کوچک فقط چهل هزار رویدادِ **اولِ** دوره را دارد — یعنی حدودِ سه ماه و نیمِ اول، نه یک نمونهٔ تصادفی از سه سال. برای اندازه‌گیریِ **زمان** بی‌خطر است، چون فقط تعدادِ سطر مهم است؛ برای هر ادعای آماری دربارهٔ خودِ داده نه.

> 🤖 **از دستیارت بپرس:** «`SQLite` برای چه نوع کاری مناسب است و برای چه نوعی نیست؟» بعد این را بپرس: **«آیا با روشن کردنِ `WAL` می‌توانم چند نویسندهٔ هم‌زمان داشته باشم؟»** — جوابِ درست می‌گوید نه: `WAL` خواننده‌ها را از نویسنده جدا می‌کند، ولی نویسنده همچنان یکی است. اگر جواب گفت بله، با بازدهیِ بخشِ ۱ ردش کن.

## واژه‌های تازهٔ این فصل

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| concurrency | کانکارنسی | هم‌زمانی؛ چند کار که در یک بازه پیش می‌روند |
| throughput | تروپوت | بازدهی؛ چند کار در واحدِ زمان تمام می‌شود |
| contention | کانتنشن | رقابت بر سرِ یک منبعِ مشترک، مثلِ حقِ نوشتن |
| `STRICT` table | استریکت تیبل | جدولی که نوعِ ستون‌هایش قیدِ واقعی است |
| dialect | دایلکت | گویش؛ تفاوت‌های `SQL` بینِ پایگاه داده‌های مختلف |
| client-server | کلاینت-سرور | معماری‌ای که در آن پایگاه داده یک سرویسِ جداست، نه یک فایل |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا چهار نخِ نویسنده کار را کندتر کردند ولی چهار نخِ خواننده سریع‌تر؟

**تمرینِ اجباری:** آزمایشِ بخشِ ۱ را با دو نخ و با هشت نخ تکرار کن — همان بیست هزار سطر، فقط تقسیم‌شده به دسته‌های متفاوت. **برای هر سه حالت، زمان و شمارشِ سطرها را چاپ کن.** حالا جوابِ سؤالِ اصلی را بنویس: **با زیاد شدنِ نخ‌ها بازدهی بهتر می‌شود یا بدتر، و این دربارهٔ هزینهٔ قفل چه می‌گوید؟**

**تمرینِ ستاره‌دار ⭐:** یک نسخهٔ یک‌درصدی از `events` بساز (`id % 100 = 0`) و آزمایشِ بخشِ ۴ را با **سه** اندازه تکرار کن: چهار هزار، چهل هزار، و چهارصد هزار سطر. **هر سه زمان را چاپ کن و دو نسبت را حساب کن.** حالا سؤالِ سخت را جواب بده: **آیا دو نسبت با هم برابرند؟** اگر نیستند، بگو کدام بازه بیشتر از ده برابر شد و یک دلیلِ ممکن بنویس — راهنمایی: کدام‌یک از سه جدول کامل در حافظه جا می‌شود؟

## در فصل بعد

هفت فصل ابزار جمع کردیم. فصلِ آخر هیچ ابزارِ تازه‌ای ندارد و به‌جایش کارِ کامل را انجام می‌دهد: **یک پرسشِ گزارشیِ واقعی و کند، از اندازه‌گیری تا نتیجه.**

نقشهٔ اجرا را می‌خوانیم، یک فرضیه می‌نویسیم، یک تغییر می‌دهیم، و یک باندِ زمانی چاپ می‌کنیم. **یکی از فرضیه‌ها پرسش را کندتر می‌کند و یکی تفاوتی کوچک‌تر از نوسان می‌سازد، و هر دو را دقیقاً با همان جزئیاتِ فرضیهٔ موفق گزارش می‌کنیم** — چون گزارشی که فقط موفقیت‌ها را دارد، گزارش نیست.

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t5/s07-limits-of-sqlite (یادستان, Yaadestan)
