SQL - Latihan mengoptimalkan kueri ke rdms

 

Tutorial Optimasi Kueri RDMS

Jika kamu baru belajar tentang database dan query SQL, jangan khawatir. Tutorial ini akan membimbingmu step by step menggunakan studi kasus nyata tentang aplikasi pelacakan jam kerja karyawan. Kita akan mulai dari dasar, lalu menganalisis kueri yang buruk, dan akhirnya mengoptimalkannya. Tujuannya: Kamu akan paham mengapa optimasi penting dan bagaimana melakukannya dengan mudah. Kita gunakan PostgreSQL sebagai contoh, tapi prinsipnya berlaku untuk database lain seperti MySQL.  

Optimasi Query di RDMS

Berikut adalah bagian "Provided Schema & Sample Data" dari studi kasus yang akan di bahas di sini, Saya menyediakannya persis seperti aslinya untuk kemudahan pembelajaran. Anda bisa menyalin dan menjalankannya di PostgreSQL untuk membuat tabel dan memasukkan data sampel. 

Pertama: Apa Itu Optimasi Kueri?

Bayangkan database seperti perpustakaan besar. Jika kamu mencari buku tanpa indeks (daftar isi), kamu harus memeriksa setiap rak satu per satu – itu lambat! Optimasi kueri adalah cara membuat database "pintar" mencari data cepat, seperti menggunakan indeks untuk langsung ke rak yang tepat.

• Mengapa Penting? Kueri yang buruk bisa membuat aplikasi lambat, terutama dengan data banyak (misalnya, jutaan catatan). Optimasi bisa membuat kueri 10-100x lebih cepat.
• Alat Utama: EXPLAIN ANALYZE, Ini seperti "peta" yang menunjukkan bagaimana database menjalankan kueri. Kita akan gunakan ini untuk melihat masalah.
• Prinsip Dasar: Gunakan join untuk menghubungkan tabel, tambahkan indeks untuk pencarian cepat, hindari subkueri yang berulang.

Sekarang, mari masuk ke studi kasus!

Kedua: Studi Kasus Aplikasi Pelacakan Jam Kerja

Aplikasi ini menyimpan data jam kerja karyawan di PostgreSQL. Ada 3 tabel:

• employee: Menyimpan data karyawan (id, nama).
• project: Menyimpan data proyek (id, nama).
• time_record: Menyimpan catatan waktu kerja (id, employee_id, project_id, time_from, time_to).

Data sampel kecil: 

2 karyawan (Tom, Jerry), 2 proyek, dan 3 catatan waktu.

Kueri asli (yang buruk) ingin menghitung total jam kerja per karyawan per proyek dalam sebulan terakhir. Tapi kueri ini lambat karena cara kerjanya tidak efisien.

 

Pertama Siapkan dulu skema database untuk praktek, SBB:

Schema (schema.sql) 

DROP TABLE IF EXISTS project;

CREATE TABLE project (

id INT8 PRIMARY KEY,

name VARCHAR(200)

);

DROP TABLE IF EXISTS employee;

CREATE TABLE employee (

id INT8 PRIMARY KEY,

name VARCHAR(60)

);

DROP TABLE IF EXISTS time_record;

CREATE TABLE time_record (

id INT8 PRIMARY KEY,

employee_id INT NOT NULL,

project_id INT NOT NULL,

time_from TIMESTAMP NOT NULL,

time_to TIMESTAMP NOT NULL

);

Sample Data (sample_data.sql)

INSERT INTO employee (id, name) VALUES

(101, 'Tom'),

(102, 'Jerry');

INSERT INTO project (id, name) VALUES

(1, 'Sample Project A'),

(2, 'Sample Project B');

INSERT INTO time_record (id, employee_id, project_id, time_from, time_to) VALUES

(1, 101, 1, '2024-02-01 08:00:00', '2024-02-01 17:00:00'),

(2, 102, 2, '2024-02-01 09:00:00', '2024-02-01 18:30:00'),

(3, 101, 1, '2024-02-02 08:15:00', '2024-02-02 17:10:00');

Ketiga: Menganalisis Kueri Buruk – Apa Masalahnya?

Lihat kueri yang tidak optimized ini:

SELECT

t.employee_id,

(SELECT name FROM employee e WHERE e.id = t.employee_id) AS employee_name,

(SELECT name FROM project p WHERE p.id = t.project_id) AS project_name,

SUM(EXTRACT(EPOCH FROM (t.time_to - t.time_from)) / 3600) AS total_hours

FROM time_record t

WHERE t.time_from >= NOW() - INTERVAL '1 month'

GROUP BY

t.employee_id,

(SELECT name FROM employee e WHERE e.id = t.employee_id),

(SELECT name FROM project p WHERE p.id = t.project_id)

ORDER BY

(SELECT name FROM employee e WHERE e.id = t.employee_id),

(SELECT name FROM project p WHERE p.id = t.project_id);

Mengapa Buruk? 

Mari kita uraikan step by step (seperti memecah masalah matematika):

1. Subkueri di SELECT: Untuk setiap baris di time_record, kueri menjalankan subkueri terpisah untuk ambil nama karyawan dan proyek. Jika ada 1000 baris, itu 2000 subkueri tambahan! Ini seperti pergi ke toko setiap kali butuh bahan – boros waktu.
2. Subkueri di GROUP BY dan ORDER BY: Ini memaksa database menghitung ulang nama berulang kali. Padahal, nama bisa diambil sekali saja.
3. Tidak Ada Join: Database tidak tahu hubungan antara tabel. Akibatnya, tidak bisa menggunakan "jalan pintas" seperti indeks.
4. Filter WHERE: Tanpa indeks pada time_from, database harus cek semua baris – seperti mencari jarum di tumpukan jerami.
5. Dampak: Untuk data kecil, OK. Tapi dengan 100.000 baris, bisa lambat 10-60 detik. Ini melanggar aturan dasar: "Gunakan join untuk hubungan tabel, bukan subkueri berulang."

Analoginya: Bayangkan kamu punya daftar tugas. Alih-alih tulis ulang nama teman setiap kali, cukup sebut ID teman dan lihat daftar nama sekali.

Keempat: Gunakan Alat Diagnosa EXPLAIN ANALYZE 

Jalankan EXPLAIN ANALYZE di PostgreSQL untuk lihat "peta" eksekusi. Ini menunjukkan langkah-langkah database dan waktu.

Contoh output (untuk data sampel):

• Seq Scan: Database memindai semua baris time_record (seperti baca buku dari awal).
• SubPlan: Subkueri dieksekusi berulang (loops 3 untuk 3 baris). Ini bottleneck utama!
• Waktu: Cepat untuk data kecil, tapi biaya (cost) tinggi untuk data besar.

Cara Jalankan: Di pgAdmin atau psql, ketik EXPLAIN ANALYZE di depan kueri. Cari kata "SubPlan" atau "Seq Scan" – itu tanda masalah. Pelajaran: EXPLAIN seperti dokter, menunjukkan sakitnya kueri.

 

EXPLAIN

 

EXPLAIN ANALYZE

 

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

 

Kelima: Mengoptimalkan Kueri – Step by Step

Sekarang, mari perbaiki! Kita buat kueri lebih cepat dengan prinsip dasar.

#Tambahkan Indeks (Seperti Daftar Isi Buku)

Indeks membantu pencarian cepat. Tambahkan ini:

• CREATE INDEX idx_time_record_time_from ON time_record (time_from); – Untuk filter bulan terakhir.
• CREATE INDEX idx_time_record_emp_proj_time ON time_record (employee_id, project_id, time_from); – Untuk join dan group.

Tanpa indeks, database "jalan kaki". Dengan indeks, langsung seperti "naik lift". 

#Ganti Subkueri dengan Join (Hubungkan Tabel dengan Benar)

Join seperti menyatukan puzzle. Kueri baru:

SELECT

e.id AS employee_id,

e.name AS employee_name,

p.name AS project_name,

SUM(EXTRACT(EPOCH FROM (t.time_to - t.time_from)) / 3600) AS total_hours

FROM time_record t

INNER JOIN employee e ON t.employee_id = e.id

INNER JOIN project p ON t.project_id = p.id

WHERE t.time_from >= NOW() - INTERVAL '1 month'

GROUP BY e.id, e.name, p.id, p.name

ORDER BY e.name, p.name;

Perubahan:

• INNER JOIN: Hubungkan time_record dengan employee dan project sekali saja. Tidak ada subkueri berulang!
• GROUP BY dan ORDER BY: Gunakan kolom langsung dari join, bukan subkueri.
• Hasil Sama: Tetap hitung jam kerja, tapi lebih cepat.

Teknik Lanjutan (Opsional untuk Pemula)

• Materialized View: Jika kueri sering dijalankan, buat view yang menyimpan hasil. Refresh malam hari.
• Partisi: Bagi tabel berdasarkan bulan untuk data besar.
• Test Ulang: Jalankan EXPLAIN ANALYZE lagi. Sekarang harus lihat "Index Scan" dan join cepat, waktu turun drastis.

Kesimpulan dan Pelajaran Utama

Dari buruk ke baik: Kueri asli lambat karena subkueri berulang dan kurang indeks. Yang dioptimalkan cepat karena join dan indeks.

Pelajaran untuk Kamu:

• Selalu Gunakan Join: Untuk hubungan tabel, bukan subkueri.
• Tambah Indeks: Untuk kolom yang difilter atau di-join.
• Gunakan EXPLAIN: Untuk cek performa sebelum dan sesudah.
• Praktik: Coba di databasemu sendiri. Ubah data sampel dan lihat perbedaannya.
• Tips Mahasiswa: Mulai dari kueri sederhana, lalu optimasi. Baca dokumentasi PostgreSQL tentang indeks dan join.

Sekarang, kamu sudah paham optimasi dasar! Jika ada pertanyaan, tanyakan. Selamat belajar!