Satu Klausa SQL Ini Membuat Query Saya 10x Lebih Cepat

Semuanya berawal dari hambatan - dan sakit kepala.

Saat itu Kamis sore, dan saya sedang tenggelam dalam laporan performa untuk klien fintech. Basis pengguna mereka melonjak pada Q1, yang berarti dashboard kami yang dulunya responsif kini bergerak selambat modem dial-up.

Saya ditugaskan untuk mengoptimalkan query kunci yang menjalankan analisis pengeluaran pelanggan. Tidak terlalu rumit - hanya menarik total nilai transaksi berdasarkan segmen pelanggan untuk kuartal terakhir.

Namun inilah tantangannya: tabel transaksi memiliki lebih dari 150 juta baris.

Dan query saya? Butuh waktu lebih dari 2 menit untuk dijalankan. Terkadang bahkan timeout.

Query Awal yang Lambat

Berikut adalah query yang saya mulai dengan:


SELECT
    c.customer_id,
    c.segment,
    SUM(t.amount) AS total_spent
FROM customers c
JOIN transactions t ON c.customer_id = t.customer_id
WHERE t.transaction_date >= '2024-01-01'
GROUP BY c.customer_id, c.segment;

Sekilas, terlihat bersih. Join dengan foreign key yang sudah diindex. Klausa WHERE untuk memfilter transaksi terbaru. GROUP BY untuk mendapatkan total pengeluaran per pelanggan.

Jadi apa masalahnya?

Ternyata, meskipun saya memfilter transaction_date, join diproses sebelum filter.

Artinya, engine database melakukan full scan pada semua 150 juta transaksi sebelum mempersempit data.

Saya mencoba menambahkan lebih banyak index, menulis ulang join, menambahkan LIMIT. Tidak ada yang memberikan peningkatan yang saya butuhkan.

Game-changer: Klausa WITH (Common Table Expression)

Karena frustasi, saya menulis ulang query menggunakan CTE - sebagian besar untuk memudahkan debugging.

Penulisan ulang itu mengubah segalanya.


WITH recent_transactions AS (
    SELECT *
    FROM transactions
    WHERE transaction_date >= '2024-01-01'
)

SELECT
    c.customer_id,
    c.segment,
    SUM(r.amount) AS total_spent
FROM customers c
JOIN recent_transactions r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.segment;

Sekarang, alih-alih memindai 150 juta baris dan kemudian memfilter, query memfilter 20 juta baris yang relevan terlebih dahulu, lalu melakukan join dengan tabel pelanggan.

🚀 Waktu eksekusi turun dari 127 detik menjadi 11.4 detik.

Tanpa perubahan lain. Data yang sama. Hanya struktur yang lebih baik.

Mengapa Teknik Ini Berhasil

1. Filter lebih awal = Data yang di-join lebih sedikit

Join menjadi jauh lebih cepat ketika dataset sudah dikurangi sebelumnya. Bayangkan seperti memilah cucian Anda sebelum memasukkannya ke mesin cuci.

2. Execution plan yang lebih bersih

Query optimizer modern (seperti di PostgreSQL 12+, BigQuery, dan SQL Server) dapat mengubah CTE menjadi inline views - yang membantu mereka membuat keputusan yang lebih cerdas selama optimasi.

3. Logika modular = debugging lebih mudah

Memecah logika Anda menjadi CTE yang lebih kecil dan terfokus memudahkan untuk menemukan inefisiensi dan menguji bagian-bagian query secara terpisah.

Optimasi Tambahan: Agregasi di Dalam CTE

Ingin melangkah lebih jauh? Berikut versi di mana saya melakukan agregasi dalam CTE itu sendiri:


WITH recent_spend AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM transactions
    WHERE transaction_date >= '2024-01-01'
    GROUP BY customer_id
)

SELECT
    c.customer_id,
    c.segment,
    r.total_spent
FROM customers c
JOIN recent_spend r ON c.customer_id = r.customer_id;

Sekarang saya melakukan join pada hasil yang sudah diagregasi, mengurangi baik jumlah baris maupun waktu pemrosesan. Dalam kasus saya, versi ini menurunkan waktu eksekusi menjadi kurang dari 6 detik.

Pelajaran dari Pengalaman Lapangan

Setelah bertahun-tahun menulis SQL, saya telah belajar bahwa perbedaan antara query yang baik dan query yang hebat bukanlah sintaksisnya - melainkan strukturnya.

Menggunakan klausa WITH seperti menambahkan komentar pada logika Anda. Ini memperjelas maksud, memisahkan perhatian, dan dalam banyak kasus, meningkatkan performa secara dramatis.

Namun ada catatannya:

Di versi MySQL yang lebih lama, CTE mungkin dimaterialisasi, menciptakan temporary tables dan mengurangi performa.
Di PostgreSQL, gunakan versi 12 atau lebih baru untuk memanfaatkan optimasi inline.
Di BigQuery, CTE umumnya direkomendasikan untuk visibilitas biaya dan performa.

Studi Kasus: Pengoptimalan CTE pada Dataset Besar

Mari kita lihat contoh nyata lainnya. Bayangkan Anda memiliki tabel sales dengan 50 juta transaksi penjualan dan tabel products dengan 100,000 produk. Anda perlu melaporkan penjualan bulanan per kategori produk.

Pendekatan awal (lambat):

SELECT p.category, EXTRACT(MONTH FROM s.sale_date) AS month, SUM(s.amount) AS monthly_sales FROM sales s JOIN products p ON s.product_id = p.product_id WHERE s.sale_date BETWEEN '2024-01-01' AND '2024-12-31' GROUP BY p.category, month ORDER BY p.category, month;

Pendekatan dengan CTE (lebih cepat):

WITH filtered_sales AS ( SELECT product_id, EXTRACT(MONTH FROM sale_date) AS month, amount FROM sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31' ), monthly_by_product AS ( SELECT product_id, month, SUM(amount) AS product_monthly_sales FROM filtered_sales GROUP BY product_id, month ) SELECT p.category, m.month, SUM(m.product_monthly_sales) AS monthly_sales FROM monthly_by_product m JOIN products p ON m.product_id = p.product_id GROUP BY p.category, m.month ORDER BY p.category, m.month;

Dalam contoh ini, kita menggunakan multiple CTEs untuk:

Pertama memfilter hanya transaksi tahun 2024
Kemudian melakukan agregasi per produk dan bulan
Terakhir melakukan join dengan tabel products yang jauh lebih kecil

Struktur bertingkat ini dapat mengurangi waktu eksekusi dari menit menjadi detik pada dataset yang besar.

Petunjuk Praktis untuk Optimasi Query dengan CTE

Identifikasi filter kunci yang dapat diterapkan lebih awal
Filter tanggal dan ID biasanya sangat efektif untuk mengurangi volume data.
Lakukan agregasi sebelum join jika memungkinkan
Mengurangi jumlah baris sebelum join akan menghasilkan performa yang jauh lebih baik.

Gunakan EXPLAIN untuk memahami execution plan


EXPLAIN ANALYZE
WITH filtered_data AS (
    SELECT * FROM large_table WHERE date > '2025-01-01'
)
SELECT * FROM filtered_data;

Pecah query kompleks menjadi beberapa CTE
Untuk query dengan banyak subquery dan join, memecahnya menjadi beberapa CTE meningkatkan keterbacaan dan memudahkan optimasi.
Perhatikan kondisi database
Pada PostgreSQL 12+ dan BigQuery, CTE cenderung mengoptimalkan performa, sementara pada MySQL versi lama mungkin memberikan hasil sebaliknya.

Poin Kunci

Query saya pada 150 juta baris berjalan 10x lebih cepat hanya dengan menggunakan CTE
Filter lebih awal menggunakan klausa WITH untuk mengurangi beban join
Uji performa sebelum/sesudah - engine SQL Anda berpengaruh
SQL yang bersih = SQL yang lebih cepat = analis yang lebih bahagia

Glossary: Terminologi Penting untuk Data Analyst

Common Table Expression (CTE): Bentuk temporary result set yang didefinisikan dalam query SQL menggunakan klausa WITH, memungkinkan Anda untuk membuat subquery bernama yang dapat direferensikan dalam query utama.
Execution Plan: Urutan langkah yang direncanakan oleh query optimizer database untuk mengeksekusi query SQL, termasuk bagaimana tabel akan diakses dan diproses.
Query Optimizer: Komponen database yang bertanggung jawab untuk menentukan cara paling efisien untuk mengeksekusi query SQL dengan memilih execution plan terbaik.
Materialized CTE: Proses di mana hasil CTE disimpan dalam temporary table di memory atau disk, yang dapat menguntungkan atau merugikan performa tergantung pada mesin database.
Inline View: Subquery yang muncul dalam klausa FROM dan bertindak seperti tabel sementara atau view virtual selama eksekusi query.
Foreign Key: Kolom yang menghubungkan dua tabel dengan mereferensikan primary key tabel lain, digunakan dalam operasi join.
Full Scan: Ketika database harus memeriksa setiap baris dalam tabel untuk menemukan yang memenuhi kriteria query, biasanya menandakan kurangnya indeks yang tepat.
Index: Struktur data yang meningkatkan kecepatan operasi pengambilan data pada tabel database dengan mengorbankan penulisan dan ruang penyimpanan tambahan.

Citations:

in Data Analyst

70 Query SQL Penting yang Harus Dikuasai Data Analyst di 2025