Antipola SQL yang Harus Dihindari

• Antipola SQL membuat query dan pipeline data lebih sulit dipelihara, serta menyebabkan performa yang lebih lambat dari perkiraan
• Contoh yang umum meliputi penggunaan berlebihan CASE WHEN, menerapkan fungsi pada kolom berindeks, SELECT *, penggunaan DISTINCT berlebihan, view dan subquery bertingkat, serta struktur dependensi yang dalam
• Sebagian besar masalah berasal dari solusi sementara karena tekanan kecepatan dan tenggat waktu, yang dalam jangka panjang merusak keandalan data sekaligus kecepatan pengembangan
• Solusinya mencakup definisi join yang jelas, pemanfaatan dimension table, penghapusan nesting yang tidak perlu, dan pembersihan view secara berkala
• SQL harus diperlakukan bukan sebagai sekadar skrip, melainkan sebagai kode produksi yang dikelola pada tingkat tim, dan desain awal yang mempertimbangkan keterbacaan dapat mengurangi pengerjaan ulang

---

Pengenalan

• Hari ini akan dibahas beberapa contoh antipola SQL yang sering muncul dan berdampak besar
• Masalah-masalah ini memicu lingkaran setan seperti menurunnya keandalan data dan melambatnya kecepatan pengembangan query
• Daftar di bawah ini tidak mencakup semua kasus, dan jika ingin pemahaman yang lebih mendalam, buku Bill Karwin sangat direkomendasikan

CASE WHEN yang Terlalu Kompleks

• Dalam sistem berskala besar, klausa CASE WHEN sering digunakan untuk mengubah kode status (misalnya 1=stok habis) agar dapat dibaca manusia
• Menambahkan logika CASE WHEN tersebut hanya ke satu View demi pengembangan dashboard atau laporan yang cepat merupakan sebuah antipola dalam jangka panjang
• Ini memicu duplikasi logika lewat salin-tempel, masalah ketidaksesuaian interpretasi, dan membuat keseluruhan lingkungan query menjadi berantakan
• Solusinya adalah membuat dimension table atau view bersama secara terpisah untuk mengubah kode status sehingga reusability dapat terjaga

Menggunakan Fungsi pada Kolom Berindeks

• Jika fungsi diterapkan pada kolom yang memiliki indeks, seperti dalam “WHERE UPPER(name) = 'ABC'”, efisiensi indeks akan hilang
  • Pada SQL Server dan lain-lain, hal ini dapat menyebabkan full table scan yang tidak perlu
• Solusinya adalah mengindeks kolom hasil penerapan fungsi secara terpisah atau mengubah nilai input agar kondisi query menjadi lebih sederhana

Menggunakan SELECT * di View

• Saat mengembangkan view, penggunaan SELECT * memang tampak praktis, tetapi jika struktur (skema) berubah, view dapat dengan mudah rusak
• Karena kolom yang tidak perlu ikut terbawa, ini bisa menimbulkan dependensi yang tidak disengaja serta masalah performa, sehingga pemilihan kolom harus dilakukan secara eksplisit

Menyelesaikan Duplikasi dengan DISTINCT secara Berlebihan

• Ketika hasil duplikat muncul karena join yang salah, menyelesaikannya sementara dengan SELECT DISTINCT justru menyembunyikan masalah integritas data
  • Akar masalahnya adalah ketidaklengkapan kondisi join atau kesalahan dalam definisi relasi (1:1, 1:N, dan sebagainya)
  • Solusi yang benar adalah memperjelas definisi relasi dengan memperkuat logika join dan memastikan konsistensi relasi sebelum agregasi atau pelaporan

View Bertingkat Berlebihan (Excessive View Layer Stacking)

• Jika beberapa tim terus menambahkan view baru sambil menggunakan ulang view yang sudah ada, rantai dependensi menjadi kompleks dan performa menurun tajam
  • Debugging menjadi sulit, dan perluasan query bisa terasa sesulit “penggalian arkeologis”
• Diperlukan strategi untuk secara berkala meratakan (flatten) logika transformasi, dan untuk operasi yang kompleks, mematerialisasikannya (materialize) ke base view atau tabel yang jelas

Subquery yang Terlalu Dalam

• Subquery yang bersarang terlalu dalam, lebih dari 3–4 tingkat, menurunkan keterbacaan dan menyulitkan debugging
  • Bahkan ada kasus yang menangani subquery dengan panjang lebih dari 5000 baris
• Dengan memanfaatkan CTE(Common Table Expression), pemisahan tahap logis menjadi lebih mudah dan kejelasan (readability) query meningkat

Kesimpulan

• SQL di permukaan memang tampak sederhana, tetapi seiring membesarnya sistem, kompleksitasnya juga meningkat
• Sebagian besar antipola muncul bukan karena niat buruk, melainkan sebagai hasil kompromi demi ‘hasil cepat’ (kecepatan, tenggat, solusi sementara)
• Jika SQL dikelola seperti kode (version control, code review, desain yang jelas), dalam jangka panjang produktivitas dan keandalan dapat diperoleh sekaligus
• Dengan meluangkan beberapa menit untuk desain awal dan memikirkan kejelasan serta konsistensi, pengerjaan ulang dan kebingungan di masa depan dapat sangat dikurangi

[aer0700]

Karena sekarang tidak ada waktu, kita bilang cukup padamkan dulu masalah yang mendesak lalu tulis ulang nanti, tapi kalau itu menumpuk akhirnya jadi neraka kueri yang mengerikan. Saya juga sudah membuat banyak yang seperti itu. Meski tahu bahwa kata "nanti" untuk menulis ulang itu tidak akan pernah benar-benar datang.

[firefoxsaiko123]

Duh...

[ilikeall]

“Sebagian besar masalah adalah solusi sementara yang muncul karena tekanan kecepatan dan tenggat waktu”
Hiks..

[GN⁺]

Komentar Hacker News

• Kalau saya melihat DISTINCT dipakai dalam query, saya curiga penulisnya tidak benar-benar paham model data atau teori himpunan, atau mungkin keduanya
  • Kadang DISTINCT juga bisa menjadi sinyal skema yang terlalu dinormalisasi. Misalnya, saya rasa tidak perlu sampai membuat tabel seperti addresses_cities hanya untuk mencegah nama kota ganda tercatat
  • Pengalaman saya juga hampir sama. Tapi belakangan saya pernah menemui kasus di mana semua join sudah benar, namun ketika DISTINCT dimasukkan ke dalam CTE, performanya meningkat drastis. Sepertinya query planner bisa melakukan optimisasi jika keunikan record terjamin
  • Saya pernah menambahkan LIMIT 1 dalam query karena mengira hasilnya paling banyak hanya satu, lalu mendapat umpan balik bahwa itu juga kurang baik. Namun pada tabel besar, setelah menemukan record yang diinginkan pun DB cenderung tetap terus menelusuri seluruh tabel (sqlite, mysql, postgresql semua begitu)
  • Saya bertanya bagaimana cara mengetahui apakah DISTINCT aman dihilangkan pada query SELECT x FROM t. Meski dari skema t terlihat bahwa x punya constraint PRIMARY atau UNIQUE, seseorang bisa saja segera menghapus constraint UNIQUE itu. Lalu duplikasi muncul dan kita jadi bingung kenapa. SQL bukan bahasa himpunan (set), melainkan bahasa bag. Saat rel t dan atribut x ditemukan di runtime, SQL tinggal mengembalikannya apa adanya. Bisa saja ada duplikasi, atau tipenya berubah. Jika yang diinginkan adalah Set, maka DISTINCT harus dinyatakan secara eksplisit. Query planner lalu tidak akan melakukan deduplikasi saat runtime jika memang UNIQUE atau PRIMARY
  • Di Cypher justru kebalikannya. Saat menangani data kompleks dengan neo4j, node duplikat sangat mudah ikut masuk ke hasil, jadi DISTINCT itu wajib. Terutama saat memakai relasi panjang variabel, tanpa DISTINCT hasilnya jadi lambat dan penuh duplikasi
• Saya pernah menulis tutorial sekitar 9000 kata dalam dua bagian tentang cara merancang struktur query yang benar tanpa DISTINCT
  https://kb.databasedesignbook.com/posts/systematic-design-of-join-queries/
  • Artikel yang bagus. Saya bookmark. Dan baru sadar ini ternyata juga sebuah buku
• Salah satu yang jarang dibahas adalah query untuk mencari "yang tidak ada". Misalnya, penggunaan != atau NOT IN (...) hampir selalu tidak efisien (meski bisa saja oke jika himpunan hasil sudah cukup dipersempit oleh kondisi lain). Dan penting juga memahami bagaimana DB menangani null. Apakah null sama dengan string kosong, apakah null == null, itu bisa berbeda antar-DB
  • Soal penanganan null dan indexing, DB yang pernah saya pakai tidak mengindeks nilai null, jadi query WHERE col IS NULL tetap berjalan tidak efisien walau ada indeks pada col. Kalau memang sangat perlu, saya sarankan membuat kolom char(1) atau bit yang menandai apakah col bernilai null, lalu mengindeks field itu
  • Tadi dibilang != atau NOT IN (...) hampir selalu tidak efisien, saya penasaran kenapanya. Jika nilai di sisi kanan berupa konstanta, bukankah itu bisa jadi lookup hash table dan biasanya efisien? Saya ingin tahu apakah ada alternatif yang lebih efisien
• Saya rasa tidak semua 'anti-pattern' yang disebut benar-benar anti-pattern. Masalah kondisi query yang tidak cocok dengan indeks pada akhirnya berakar pada kurangnya pemahaman tentang prinsip indeks. Dan banyak masalah yang disebut di sini lebih dekat ke desain skema database daripada SQL itu sendiri. Jika DISTINCT diperlukan, bisa jadi desain primary key-nya tidak tepat. Jika view menumpuk terlalu banyak, berarti desain tabel dasarnya keliru. Pemodelan DB yang baik mencegah semua masalah ini sejak awal
• 'Anti-pattern' seperti ini sebenarnya cuma solusi tambal sulam yang muncul karena keterbatasan desain SQL (atau karena memang tidak pernah dirancang). Saya sedang membuat bahasa baru yang berjalan di atas database SQL, dan untuk tiap masalah seperti ini saya ingin menyediakan alternatif yang lebih baik. Masih belum selesai dan dokumentasinya juga minim, tapi kalau tertarik saya ingin menerima masukan di https://lutra-lang.org
  • Ungkapan "database SQL" itu ambigu. SQL diimplementasikan bukan hanya pada DB relasional tetapi juga ada contohnya di DB non-relasional. Para ahli sebenarnya sudah lama menyadari masalah SQL, dan alternatif seperti Tutorial D dari Chris Date dan Hugh Darwen juga sudah ada. Namun karena kode dan tool SQL yang terakumulasi selama puluhan tahun, pengganti sulit mendapat tempat. Berkat SQL, saya menikmati stabilitas kerja dan penghasilan yang konsisten selama puluhan tahun, jadi meskipun bahasa yang lebih baik memang diperlukan, di sisi lain saya juga memandang situasi ini secara positif
  • Proyeknya terlihat menjanjikan. Kalau nanti lebih matang, saya pasti akan mengikutinya
• Anti-pattern terbesar adalah tidak menganggap SQL sebagai bahasa pemrograman sungguhan, melainkan sekadar bahasa query. Saya sarankan gaya kode yang konsisten dalam indentasi, dan mengelompokkan bagian-bagian yang secara logis saling terkait. Subquery sebaiknya diubah menjadi CTE. Memberi komentar secara efektif juga penting. Ini referensi gaya saya: https://bentilly.blogspot.com/2011/02/sql-formatting-style.html
  • Menurut saya, perdebatan soal gaya kode seperti ini hampir tidak ada artinya tanpa tool linter yang memadai
• Rahasia terbesar saya untuk meningkatkan kecepatan query dan mengurangi penggunaan resource server adalah membuat query lebih sargable
  https://en.wikipedia.org/wiki/Sargable
  https://www.brentozar.com/blitzcache/non-sargable-predicates/
  • Saya penasaran kata "sargable" itu sebenarnya dipakai di komunitas mana. Saya sudah lebih dari 20 tahun berkutat dengan SQL, tapi hampir tidak pernah melihatnya di manual, Stack Overflow, maupun HN. Saya ingin tahu apakah istilah ini banyak dipakai di DB, perusahaan, atau komunitas open source tertentu
  • Saat menelusuri etimologi sargable, jawaban StackOverflow ini membantu https://dba.stackexchange.com/a/217983
    Kata "sargable" adalah portmanteau dari "Search ARGument ABLE"
• Banyak masalah akibat penyalahgunaan CASE WHEN bisa diatasi dengan menyatukan logika di satu tempat memakai UDF (User Defined Function)
  Menggunakan fungsi pada kolom yang diindeks adalah sinyal bahwa query tersebut tidak sargable
  Sebagai pengganti penyalahgunaan DISTINCT, query untuk menghapus duplikasi sesuai grain tabel di tengah fan-out turunan dari join seperti ini

  ROW_NUMBER() OVER (PARTITION BY <grain> ORDER BY <deterministic sort>) = 1
  

  bisa sangat berguna. Beberapa DB mendukung fitur QUALIFY, sehingga query menjadi jauh lebih rapi
  penjelasan sargable
  QUALIFY in Redshift
  • Masalah tidak sargable bisa dengan mudah diatasi lewat expression index. Setidaknya di sqlite, menurut saya begitu
• Ada juga situasi di mana penggunaan view bertingkat justru memang diperlukan. Di software POS kami, kami banyak memakai nested view untuk membuat backbone view yang menampilkan transaksi secara bersih sekaligus. Kalau tidak begitu, kami harus menulis klausa where berbeda untuk tiap tabel, dan menangani kondisi seperti void/retur/pembatalan setiap kali, sehingga jika ada perubahan kami harus memperbaiki puluhan view/procedure. Dalam kasus kami, memanfaatkan nested view jauh lebih praktis
• Masalah penggunaan fungsi pada kolom yang diindeks perlu penjelasan yang lebih jelas. Jika fungsi diterapkan pada kolom yang memiliki indeks, efisiensi indeks hilang dan yang terjadi pada praktiknya adalah full scan, sehingga menjadi lambat. Saya belajar ini lewat pengalaman langsung
  • Ada dokumentasi yang cukup dikenal soal ini https://use-the-index-luke.com/sql/where-clause/obfuscation
  • Solusi yang diajukan (misalnya menambahkan indeks pada kolom UPPER(name)) setidaknya bukan yang terbaik di MS SQL Server. Saya kurang tahu dukungan di DB lain, tetapi solusi yang lebih baik adalah membuat computed column yang memang case-insensitive lewat COLLATE

    ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;
    

    (sesuaikan menurut preferensi)
  • Ada typo di blog terkait. Baris pertama seharusnya ditulis dengan huruf kapital. Jika indeks sudah dipasang pada data yang memang telah diberi fungsi, saat query dijalankan itu bukan full scan. Tentu saja dalam contoh ini sejak awal lebih baik memakai collation yang case-insensitive, tetapi secara umum idenya tetap masuk akal
  • "Saya belajar ini lewat pengalaman langsung" terasa seperti moto para pengembang SQL. Bagaimanapun, SQL sudah berubah secara stabil dalam waktu yang cukup lama, jadi mengetahui jebakan-jebakan seperti ini sejak awal akan sangat berguna untuk waktu yang lama

[ahwjdekf]

Yang paling penting malah terlewat.

• menggunakan ORM