Tez serving qatlami

import pandas as pd
show(pd.DataFrame([
    {"": "So'rovlar soni", "Tahlil (OLAP)": "Kuniga o'nlab", "Serving": "Sekundiga minglab"},
    {"": "Har so'rov o'qiydi", "Tahlil (OLAP)": "Millionlab qator", "Serving": "O'nlab qator"},
    {"": "Kutish chegarasi", "Tahlil (OLAP)": "Soniyalar", "Serving": "Millisekundlar"},
    {"": "So'rov shakli", "Tahlil (OLAP)": "Oldindan noma'lum", "Serving": "Oldindan ma'lum"},
    {"": "Nima muhim", "Tahlil (OLAP)": "Moslashuvchanlik", "Serving": "Kechikish (p99)"},
]))

python3 -c "
import duckdb, pandas as pd
d = pd.read_csv('../datasets/ijara.csv').drop_duplicates('elon_id')
con = duckdb.connect('serving.db')
con.execute('DROP TABLE IF EXISTS elon')
con.execute('CREATE TABLE elon AS SELECT * FROM d')
n = con.execute('SELECT count(*) FROM elon').fetchone()[0]
print(f'elonlar: {n:,}')
print(f'tumanlar: {con.execute(\"SELECT count(DISTINCT tuman) FROM elon\").fetchone()[0]}')
con.close()
"

python3 -c "
import duckdb, time
import numpy as np
con = duckdb.connect('serving.db')

SORQV = '''
SELECT elon_id, tuman, xonalar, narx_mln, maydon
FROM elon
WHERE tuman = ? AND xonalar = ? AND narx_mln <= ?
ORDER BY narx_mln
LIMIT 20
'''

def olch(n=300):
    con.execute(SORQV, ['Yunusobod', 2, 5.0]).fetchall()
    v = []
    for _ in range(n):
        t0 = time.perf_counter()
        con.execute(SORQV, ['Yunusobod', 2, 5.0]).fetchall()
        v.append((time.perf_counter()-t0)*1000)
    return np.array(v)

v = olch()
print(f'p50 : {np.percentile(v,50):6.2f} ms')
print(f'p95 : {np.percentile(v,95):6.2f} ms')
print(f'p99 : {np.percentile(v,99):6.2f} ms')
print()
print(f'natija: {len(con.execute(SORQV, [\"Yunusobod\", 2, 5.0]).fetchall())} elon')
con.close()
"

python3 -c "
import duckdb, time
import numpy as np
con = duckdb.connect('serving.db')

con.execute('DROP TABLE IF EXISTS katta')
con.execute('''CREATE TABLE katta AS
    SELECT e.* FROM elon e, generate_series(1, 84) g''')
n = con.execute('SELECT count(*) FROM katta').fetchone()[0]
print(f'katta jadval: {n:,} qator')

SORQV = '''SELECT elon_id, tuman, xonalar, narx_mln FROM {t}
           WHERE tuman = ? AND xonalar = ? AND narx_mln <= ?
           ORDER BY narx_mln LIMIT 20'''

def olch(t, n=60):
    q = SORQV.format(t=t)
    con.execute(q, ['Yunusobod', 2, 5.0]).fetchall()
    v = []
    for _ in range(n):
        t0 = time.perf_counter()
        con.execute(q, ['Yunusobod', 2, 5.0]).fetchall()
        v.append((time.perf_counter()-t0)*1000)
    return np.array(v)

a, b = olch('elon'), olch('katta')
print()
print(f'{\"jadval\":10s} {\"qatorlar\":>12s} {\"p50\":>9s} {\"p99\":>9s}')
print('-' * 44)
print(f'{\"elon\":10s} {12000:>12,} {np.percentile(a,50):>8.2f}ms {np.percentile(a,99):>8.2f}ms')
print(f'{\"katta\":10s} {n:>12,} {np.percentile(b,50):>8.2f}ms {np.percentile(b,99):>8.2f}ms')
print()
print(f'{n/12000:.0f}x koproq malumot -> {np.percentile(b,50)/np.percentile(a,50):.1f}x sekin')
con.close()
"

python3 -c "
import duckdb, time
import numpy as np
con = duckdb.connect('serving.db')

t0 = time.perf_counter()
con.execute('DROP TABLE IF EXISTS tayyor')
con.execute('''CREATE TABLE tayyor AS
    SELECT tuman, xonalar,
           CAST(ceil(narx_mln / 2.5) * 2.5 AS DOUBLE) AS narx_chegara,
           count(*) AS elonlar,
           round(avg(narx_mln), 2) AS ort_narx,
           round(avg(maydon), 1) AS ort_maydon,
           round(avg(broker), 3) AS broker_ulushi
    FROM katta
    GROUP BY tuman, xonalar, narx_chegara''')
qurish = (time.perf_counter()-t0)*1000
n = con.execute('SELECT count(*) FROM tayyor').fetchone()[0]
print(f'oldindan hisoblash: {qurish:.0f} ms (bir marta)')
print(f'natija jadvali    : {n:,} qator')
print()

def olch(q, args, n=200):
    con.execute(q, args).fetchall()
    v = []
    for _ in range(n):
        t0 = time.perf_counter()
        con.execute(q, args).fetchall()
        v.append((time.perf_counter()-t0)*1000)
    return np.array(v)

xom_q = '''SELECT count(*), avg(narx_mln) FROM katta
           WHERE tuman = ? AND xonalar = ? AND narx_mln <= ?'''
tayyor_q = '''SELECT sum(elonlar), avg(ort_narx) FROM tayyor
              WHERE tuman = ? AND xonalar = ? AND narx_chegara <= ?'''

a = olch(xom_q, ['Yunusobod', 2, 5.0])
b = olch(tayyor_q, ['Yunusobod', 2, 5.0])
print(f'xom jadvaldan  : p50 {np.percentile(a,50):7.3f} ms | p99 {np.percentile(a,99):7.3f} ms')
print(f'tayyor jadvaldan: p50 {np.percentile(b,50):7.3f} ms | p99 {np.percentile(b,99):7.3f} ms')
print()
print(f'{np.percentile(a,50)/np.percentile(b,50):.0f}x tez')
con.close()
"

python3 -c "
import duckdb, time, functools
import numpy as np
con = duckdb.connect('serving.db')

Q = '''SELECT count(*), avg(narx_mln) FROM katta
       WHERE tuman = ? AND xonalar = ? AND narx_mln <= ?'''

def xom(tuman, xona, narx):
    return con.execute(Q, [tuman, xona, narx]).fetchall()

@functools.lru_cache(maxsize=256)
def keshli(tuman, xona, narx):
    return con.execute(Q, [tuman, xona, narx]).fetchall()

# haqiqiy trafik: bir nechta filtr juda ko'p takrorlanadi
rng = np.random.default_rng(42)
tumanlar = [r[0] for r in con.execute('SELECT DISTINCT tuman FROM elon').fetchall()]
# 80% sorov eng mashhur 3 ta filtrga tushadi
mashhur = [(tumanlar[0], 2, 5.0), (tumanlar[1], 3, 8.0), (tumanlar[2], 1, 4.0)]
sorovlar = []
for _ in range(400):
    if rng.random() < 0.8:
        sorovlar.append(mashhur[rng.integers(0, 3)])
    else:
        sorovlar.append((tumanlar[rng.integers(0, len(tumanlar))],
                         int(rng.integers(1, 5)), float(rng.integers(3, 12))))

for nom, f in (('keshsiz', xom), ('keshli', keshli)):
    if nom == 'keshli': keshli.cache_clear()
    t0 = time.perf_counter()
    for s in sorovlar: f(*s)
    dt = (time.perf_counter()-t0)*1000
    print(f'{nom:8s}: {len(sorovlar)} sorov {dt:8.1f} ms -> bitta sorov {dt/len(sorovlar):.3f} ms')

i = keshli.cache_info()
print()
print(f'kesh topildi : {i.hits}')
print(f'kesh topilmadi: {i.misses}')
print(f'topilish ulushi: {i.hits/(i.hits+i.misses)*100:.1f}%')
con.close()
"

import pandas as pd
show(pd.DataFrame([
    {"Vosita": "PostgreSQL + indeks", "Kuchli tomoni": "Oddiy, allaqachon bor",
     "Qachon": "Sekundiga yuzlab so'rov"},
    {"Vosita": "Redis", "Kuchli tomoni": "Xotirada, juda tez",
     "Qachon": "Kalit bo'yicha o'qish"},
    {"Vosita": "ClickHouse", "Kuchli tomoni": "Ustunli, yig'ish uchun tez",
     "Qachon": "Real vaqt tahlil paneli"},
    {"Vosita": "Apache Pinot / Druid", "Kuchli tomoni": "Oqimdan darhol o'qiydi",
     "Qachon": "Hodisa kelishi bilan panelda"},
]))

python3 -c "
import duckdb, pathlib
con = duckdb.connect('serving.db')
con.execute('DROP TABLE IF EXISTS katta')
con.close()
print('katta jadval ochirildi, serving.db qoldi')
"