اول اندازه بگیر

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

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

بعد یک آزمایشِ دوم می‌کنیم که نتیجه‌اش «هیچ فرقی نکرد» است، و همان را هم می‌نویسیم — چون در کارِ اندازه‌گیری، آزمایشی که چیزی نشان نداد خودش یک خبر است.

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

• دادهٔ ساختگیِ بزرگ و بازتولیدپذیر بسازی و اندازه‌اش را اعلام کنی
• زمانِ اجرای یک پرسش را با time.perf_counter بگیری
• به‌جای یک عدد، کمینه و میانه و بیشینهٔ چند اجرا را گزارش کنی
• بگویی چرا fetchall بخشی از هزینه است و بدونِ آن عددت بی‌معنی است
• یک جدولِ «قبل و بعد» بنویسی که ادعا را از حدس جدا می‌کند

قبل از شروع#

از ترمِ ۴: اسکیما، کلید، و اینکه یک سطر یعنی چه. از سرنخ: time و حلقهٔ for و تابع.

دانه‌بندیِ جدولِ اصلیِ این ترم: یک سطر از events یک رویداد است — یا بردنِ یک کتاب (out) یا برگرداندنش (in). یک امانتِ کامل معمولاً دو سطر است. این جمله را نگه دار؛ در هر هشت فصلِ این ترم به کارت می‌آید.

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

۱. دادهٔ این ترم، و چرا ساختگی است#

سلولِ راه‌اندازی همین حالا این چهار جدول را ساخت و زمانِ ساختش را خودش چاپ کرد. داده ساختگی است، با SEED ثابت، و این یک انتخابِ آگاهانه است نه یک میان‌بر:

1. اندازه‌اش را ما تعیین می‌کنیم. درسِ این ترم روی هزار سطر اصلاً دیده نمی‌شود؛ اثرِ یک index وقتی معنا پیدا می‌کند که خواندنِ کلِ جدول با ساعت قابلِ اندازه‌گیری باشد.
2. بازتولیدپذیر است. هر کسی این نوت‌بوک را اجرا کند، دقیقاً همین سطرها را می‌گیرد — پس شمارش‌های این فصل روی ماشینِ او هم همان است.
3. هیچ دانلودی لازم ندارد.

اولین کار، مثلِ همیشه، شمردن است:

print(q("""
    SELECT 'events'   AS table_name, COUNT(*) AS n FROM events
    UNION ALL SELECT 'books',    COUNT(*) FROM books
    UNION ALL SELECT 'members',  COUNT(*) FROM members
    UNION ALL SELECT 'branches', COUNT(*) FROM branches
"""))
print()
print(q("""
    SELECT COUNT(*) AS all_rows,
           SUM(CASE WHEN kind = 'out' THEN 1 ELSE 0 END) AS out_rows,
           SUM(CASE WHEN kind = 'in'  THEN 1 ELSE 0 END) AS in_rows
    FROM events
"""))
print()
print(q("SELECT MIN(happened_at) AS first_event, MAX(happened_at) AS last_event FROM events"))

  table_name       n
0     events  400000
1      books   40000
2    members   12000
3   branches       8

   all_rows  out_rows  in_rows
0    400000    207831   192169

           first_event           last_event
0  2022-01-01 00:02:34  2024-12-31 23:44:44

۲۰۷٬۸۳۱ به‌علاوهٔ ۱۹۲٬۱۶۹ می‌شود دقیقاً ۴۰۰٬۰۰۰ — پس هیچ سطری kind نامعلوم ندارد و شمارشِ دومِ ما همان‌جا انجام شد.

تفاوتِ آن دو عدد ۱۵٬۶۶۲ است. و همین‌جا اولین تمرینِ صداقتِ این ترم را می‌کنیم: در یک دادهٔ واقعی این عدد یعنی «هنوز این‌قدر کتاب برنگشته»، ولی دادهٔ ما ساختگی است و نوعِ هر رویداد مستقل قرعه خورده، پس هیچ جفت شدنی بینِ out و in وجود ندارد و این تفاوت اینجا هیچ معنایی ندارد.

عددی که معنایش را نمی‌دانی، در گزارش نمی‌رود — حتی اگر دقیق محاسبه شده باشد.

۲. یک اجرا شاهد نیست#

حالا یک پرسشِ واقعی: در ماهِ سومِ سالِ اول چند رویداد ثبت شده؟

SQL_MONTH = """
    SELECT COUNT(*) AS n
    FROM events
    WHERE happened_at >= ? AND happened_at < ?
"""
MONTH = ("2022-03-01", "2022-04-01")

print("پاسخ پرسش:", int(q(SQL_MONTH, MONTH).iloc[0, 0]), "رویداد")
print("کل جدول  :", int(q("SELECT COUNT(*) AS n FROM events").iloc[0, 0]), "رویداد")

پاسخ پرسش: 11421 رویداد
کل جدول  : 400000 رویداد

جواب گرفته شد. حالا سؤالِ این ترم: چقدر طول کشید؟

for run in range(1, 6):
    start = time.perf_counter()
    con.execute(SQL_MONTH, MONTH).fetchall()
    print(f"اجرای {run}: {(time.perf_counter() - start) * 1000:7.2f} میلی‌ثانیه")

اجرای 1:   33.28 میلی‌ثانیه
اجرای 2:   33.01 میلی‌ثانیه
اجرای 3:   33.45 میلی‌ثانیه
اجرای 4:   33.43 میلی‌ثانیه
اجرای 5:   34.48 میلی‌ثانیه

پنج بار همان پرسش، همان داده، همان ماشین — و پنج عددِ متفاوت.

اگر فقط یکی از این پنج را برداری و بنویسی «این پرسش فلان‌قدر طول می‌کشد»، عددت درست است ولی شاهدت نیست: نمی‌دانی آن عدد وسطِ دامنه بود یا لبه‌اش. و روزی که بعد از یک تغییر دوباره اندازه بگیری و عددت کمی کمتر دربیاید، نمی‌توانی بگویی تغییر کار کرد یا فقط قرعه بهتر افتاد.

قاعدهٔ ثابتِ این ترم: هر زمانی که گزارش می‌کنی، میانهٔ چند اجراست، نه یک اجرا.

۳. کمینه، میانه، بیشینه#

پس یک تابعِ کوچک می‌نویسیم که هر سه را بدهد:

def spread(sql, params=(), repeat=9):
    """کمینه و میانه و بیشینهٔ چند اجرا، برحسب میلی‌ثانیه."""
    times = []
    for _ in range(repeat):
        start = time.perf_counter()
        con.execute(sql, params).fetchall()
        times.append((time.perf_counter() - start) * 1000)
    times.sort()
    return times[0], times[len(times) // 2], times[-1]


low, mid, high = spread(SQL_MONTH, MONTH)
print(f"کمینه : {low:7.2f}")
print(f"میانه : {mid:7.2f}")
print(f"بیشینه: {high:7.2f}")
print(f"پهنای نوسان: {high - low:7.2f} میلی‌ثانیه")

کمینه :   33.13
میانه :   39.06
بیشینه:   47.03
پهنای نوسان:   13.90 میلی‌ثانیه

آن «پهنای نوسان» مهم‌ترین عددِ این فصل است. هر بهبودی که ادعا می‌کنی و کوچک‌تر از این پهناست، ادعا نیست — نویز است.

تابعِ timed که در سلولِ راه‌اندازی داری دقیقاً همین کار را می‌کند و فقط عددِ وسط را برمی‌گرداند. از این به بعد همه‌جای این دوره از همان استفاده می‌کنیم؛ ولی هر بار که تفاوتِ دو عدد کوچک بود، برگرد و پهنای نوسان را هم چاپ کن.

✅ چک کن: spread را دو بار پشتِ سرِ هم صدا بزن و دو «میانه» را با هم بسنج. اگر اختلافشان از پهنای نوسانِ خودشان بیشتر بود، ماشینت وسطِ کار مشغولِ کارِ دیگری بوده و هیچ‌کدام از اندازه‌گیری‌های امروزت قابلِ اتکا نیست.

۴. سرد و گرم: آزمایشی که جواب نداد#

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

import sqlite3

SQL_ALL = "SELECT COUNT(*) AS n, SUM(days) AS s FROM events"
print(q(SQL_ALL))

        n        s
0  400000  8200275

پاسخ در هر دو حالت باید همین باشد. حالا زمان‌ها:

cold = []
for _ in range(5):
    fresh = sqlite3.connect(DB_PATH)
    start = time.perf_counter()
    fresh.execute(SQL_ALL).fetchall()
    cold.append(round((time.perf_counter() - start) * 1000, 2))
    fresh.close()

warm = []
for _ in range(5):
    start = time.perf_counter()
    con.execute(SQL_ALL).fetchall()
    warm.append(round((time.perf_counter() - start) * 1000, 2))

print("اتصال تازه در هر اجرا:", cold)
print("همان اتصال، پنج بار  :", warm)

اتصال تازه در هر اجرا: [32.44, 34.98, 32.25, 31.29, 32.37]
همان اتصال، پنج بار  : [30.51, 29.99, 29.14, 27.83, 28.93]

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

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

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

۵. fetchall هم بخشی از هزینه است#

حالا خطایی که تقریباً هر کسی اولین بار مرتکب می‌شود. con.execute(...) پرسش را شروع می‌کند و یک cursor می‌دهد؛ سطرها فقط وقتی واقعاً خوانده می‌شوند که تو بخواهی‌شان.

BIG = "SELECT * FROM events WHERE happened_at < ?"
CUT = ("2022-02-01",)

start = time.perf_counter()
cursor = con.execute(BIG, CUT)
execute_ms = (time.perf_counter() - start) * 1000

start = time.perf_counter()
rows = cursor.fetchall()
fetch_ms = (time.perf_counter() - start) * 1000

print("سطرهایی که برگشت:", len(rows))
print("ستون‌های هر سطر  :", len(rows[0]))

سطرهایی که برگشت: 11347
ستون‌های هر سطر  : 6

print(f"execute  — فقط شروع کار : {execute_ms:8.2f} میلی‌ثانیه")
print(f"fetchall — کشیدن سطرها  : {fetch_ms:8.2f} میلی‌ثانیه")

execute  — فقط شروع کار :     0.18 میلی‌ثانیه
fetchall — کشیدن سطرها  :    42.96 میلی‌ثانیه

اگر فقط execute را زمان می‌گرفتی، به این نتیجه می‌رسیدی که این پرسش تقریباً آنی است. خطا نمی‌گرفتی، هشدار نمی‌گرفتی، و عددت غلط بود — بدترین شکلِ خطا در کارِ با داده.

به همین دلیل timed در سلولِ راه‌اندازی fetchall() را داخلِ اندازه‌گیری دارد. هر ابزارِ زمان‌سنجی که خودت می‌سازی هم باید همین کار را بکند.

۶. اولین جدولِ قبل و بعد#

حالا همان پرسشِ اولِ فصل — «چند رویداد داریم؟» — را دو جور می‌پرسیم. اولی سطرها را می‌کشد بیرون و در پایتون می‌شمارد؛ دومی از پایگاه داده می‌خواهد که بشمارد.

PULL_ALL = "SELECT * FROM events"
COUNT_IN_DB = "SELECT COUNT(*) AS n FROM events"

print("شمارش در پایتون    :", len(con.execute(PULL_ALL).fetchall()))
print("شمارش در پایگاه داده:", con.execute(COUNT_IN_DB).fetchone()[0])

شمارش در پایتون    : 400000
شمارش در پایگاه داده: 400000

pull = timed(PULL_ALL)
count = timed(COUNT_IN_DB)
print(f"کشیدن همه و شمردن در پایتون: {pull:8.2f} میلی‌ثانیه")
print(f"شمردن در خود پایگاه داده   : {count:8.2f} میلی‌ثانیه")
print(f"نسبت                       : {pull / count:8.1f} برابر")

کشیدن همه و شمردن در پایتون:   515.84 میلی‌ثانیه
شمردن در خود پایگاه داده   :    11.00 میلی‌ثانیه
نسبت                       :     46.9 برابر

همان عدد، ده‌ها برابر ارزان‌تر. و توجه کن که اینجا هیچ‌چیزی در پایگاه داده عوض نشد — نه indexی ساخته شد و نه پرسشی بازنویسی شد. تنها چیزی که عوض شد این بود که کدام طرف کار را انجام می‌دهد.

قالبِ جدولی که از این به بعد در هر فصل تکرار می‌شود این است:

ستونِ آخر عمداً خالی است، و این تنها جدولِ کلِ این ترم است که حق دارد خالی باشد.

۷. چیزی که این فصل عمداً نگفت#

دو عددِ بالا می‌گویند چقدر طول کشید. هیچ‌کدام نمی‌گویند چرا.

آن پرسشِ ماهِ سوم که سی‌وچند میلی‌ثانیه طول کشید، ۱۱٬۴۲۱ سطر برگرداند از جدولی که ۴۰۰٬۰۰۰ سطر دارد. آیا پایگاه داده هر ۴۰۰٬۰۰۰ سطر را خواند و ۱۱٬۴۲۱ تا را نگه داشت، یا راهی داشت که مستقیم برود سرِ همان بازه؟ با ساعت نمی‌شود فهمید. و تا وقتی نفهمی، هر تلاشی برای سریع‌تر کردنش حدس است.

🔧 اگر کار نکرد: رایج‌ترین خطای این فصل وقتی است که پرسش را از یک سلولِ دیگر کپی می‌کنی و مقدارهایش را جا می‌گذاری:

try:
    con.execute("SELECT COUNT(*) FROM events WHERE kind = ?").fetchall()
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)

sqlite3.ProgrammingError
Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.

پیام دقیقاً می‌گوید چه شد: پرسش یک ? دارد و تو صفر مقدار دادی. رفعش هم همان قاعدهٔ همیشگی است: con.execute(sql, ("out",)) — مقدار همیشه با پارامتر می‌رود، حتی وقتی داری چیزی را فقط زمان‌سنجی می‌کنی.

📏 اندازه بگیر: یک سطر یعنی چه؟ در events یک سطر یک رویداد است، نه یک امانت — و چون هر امانتِ کامل دو رویداد دارد، هر عددی از این جدول تقریباً دو برابرِ چیزی است که به آن «امانت» می‌گویی. با کدام شمارشِ دوم سنجیدی؟ با دوتا: ۲۰۷٬۸۳۱ به‌علاوهٔ ۱۹۲٬۱۶۹ دقیقاً ۴۰۰٬۰۰۰ شد، و دو راهِ کاملاً متفاوتِ شمردن در بخشِ ۶ هر دو همان ۴۰۰٬۰۰۰ را دادند. چه کسی از قلم افتاد؟ هیچ سطری — ولی در سمتِ اندازه‌گیری دو چیز از قلم افتادند: حالتِ سرد، که بخشِ ۴ نتوانست بسازدش، و معنیِ تفاوتِ ۱۵٬۶۶۲، که چون داده ساختگی است اصلاً معنایی ندارد و ما هم به‌جای تفسیرش، همان را نوشتیم.

🤖 از دستیارت بپرس: «چرا میانهٔ چند اجرا از میانگینشان برای زمان‌سنجی بهتر است؟» بعد این را بپرس: «اگر زمانِ execute را بگیرم ولی fetchall را بیرونِ اندازه‌گیری بگذارم، عددی که می‌گیرم چه چیزی را اندازه گرفته؟» — جوابِ درست می‌گوید تقریباً هیچ‌چیز، چون سطرها هنوز خوانده نشده‌اند. اگر جواب گفت «همان زمانِ کلِ پرسش است»، با عددهای بخشِ ۵ ردش کن.

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

در فصل بعد#

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

و همان پرسشِ ماهِ سومِ این فصل را با همان می‌سنجیم. جواب یک کلمه است — SCAN — و از آن لحظه دیگر لازم نیست حدس بزنی که چرا سی‌وچند میلی‌ثانیه طول می‌کشد.