7 SQL Tips and Tricks Untuk Performa Query Lebih Baik

---

---

SQL Tips and Tricks — Siapa yang udah bisa SQL ayo ngacung! Kerenn!

Nah artikel ini untuk sharing ke lo pada semua, gimana caranya mempercepat query lo. Baik dari segi kecepatan, ataupun cara kerja (workflow). Mungkin ada beberapa yang lo tahu, tapi gue rasa ada juga yang belum.

Maka dari itu, let’s start the discussion!

Disclaimer SQL Tips and Tricks

Sebagai tolok ukur atau benchmark, gue menggunakan SQLite. Database client-nya menggunakan DBeaver Community Edition. Datasetnya menggunakan dataset dari Kaggle untuk project ini.

Maka dari itu, hasil tips SQL yang akan kita bahas ini, bisa jadi berbeda hasilnya untukmu.

SQL Tips and Tricks

Menggunakan LIMIT Ketika Preview Data

Menurut gue ini penting, terutama untuk server database dengan cloud. Hal sesimpel ini akan mempercepat query lo serta menurunkan biaya.

Kalau lo sedang dalam proses development query SQL, ada baiknya menggunakan LIMIT. Ini untuk membatasi jumlah row yang terambil. Semisal pada tabel lo ada 100.000 data, dengan LIMIT (misalkan LIMIT 1000), row yang terambil hanya 1000 row teratas.

Berikut syntax dan posisi LIMIT pada SQL query. Posisinya biasanya harus berada di ujung query:

SELECT *
FROM your_table
WHERE 1=1
ORDER BY 1
LIMIT 10

Seperti contoh berikut ini, untuk melakukan fetch 200 row, memerlukan waktu 203ms.

Namun jika kita membatasi hanya 10 row teratas yang kita miliki, total waktu penarikan hanya 32ms. Peningkatan performa hampir 90% lebih.

Mungkin lo berpikir hitungan milisecond (ms) ini tidak signifikan. Pikir lagi, kalau lo berada di ecommerce atau data dengan transaksi jutaan, berapa penghematan resource yang bisa dilakukan. So, this definitely worth to do.

Menghindari Pemilihan Kolom Dengan *

Konsepnya sebenarnya sama dengan poin pertama. Semakin sedikit data yang tertarik/difetch, maka semakin cepat. Nah selain jumlah baris atau row, meminimalisir kolom juga lebih cepat. Jadi saran gue, mulai pilih hanya kolom yang dibutuhkan.

Jadi instead of melakukan ini:

SELECT *
FROM your_table
WHERE 1=1
ORDER BY 1

dengan waktu 193ms kalau dari contoh yang gue berikan:

Lebih baik define kolomnya dengan mendefinisikannya satu-persatu:

SELECT ID, Customer_ID, Month, SSN, Occupation
FROM your_table
WHERE 1=1
ORDER BY 1

Sehingga menjadi 63ms dengan 5 kolom. Peningkatan performa hampir 70%:

Menggunakan GROUP BY 1 Daripada DISTINCT

Nah, biasanya kita memerlukan DISTINCT untuk mencari nilai-nilai yang berbeda pada suatu kolom. Nah, gunakan trik GROUP BY 1 untuk mempercepat performa, untuk hasil yang sama. Namun, trik ini hanya berlaku jika kolom yang ingin di DISTINCT hanya satu yah.

Jadi contohnya, misalkan kita mau melakukan DISTINCT data ID:

SELECT DISTINCT ID
FROM train
WHERE 1=1
ORDER BY 1

waktu yang dibutuhkan adalah 46ms.

Dengan GROUP BY 1:

SELECT ID
FROM your_table
WHERE 1=1
GROUP BY 1
ORDER BY 1

Hasilnya menjadi 7% lebih cepat atau menjadi 43ms.

Memilih Kapan Menggunakan WITH AS Untuk Sub-query

Kan kalau membuat sub-query bisa dengan dua metode. Bisa menggunakan subquery biasa atau WITH AS. Tipsnya: hindari penggunaan WITH AS sebisa mungkin untuk mempercepat performa.

Berikut dengan subquery, 200 baris ter-fetch dalam waktu 193ms:

SELECT *
FROM (
	SELECT *
	FROM your_table t
	WHERE 1=1
	ORDER BY 1
) a

Jika menggunakan WITH AS, kecepatannya memburuk menjadi 212ms. Alias, menggunakan subquery menjadi 10% lebih cepat.

WITH dataset AS (
	SELECT *
	FROM train t
	WHERE 1=1
	ORDER BY 1
)
SELECT *
FROM dataset

Membuat Tabel Khusus Untuk Penanggalan dan INNER JOIN

Nah, tips ini untuk mempercepat workflow atau cara kerja. Biasanya, kalau kita ingin mengambil data bersifat snapshot, kita menggunakan DATE_PART atau FORMAT. Namun, cara ini kurang fleksibel dan cukup memakan performa.

Tips gue adalah: buat satu tabel berisi tanggal dengan penanggalan pentingnya (misalnya akhir bulan, akhir minggu, dsb). Kemudian, lakukan INNER JOIN untuk mengambil data snapshot yang lo inginkan.

Berikut query yang bisa lo gunakan untuk membuat tabel penanggalan tersebut.

create or replace table your_scheme.your_table as
select
  d `full_date`
  , extract(year from d) `year`
  , extract(month from d) `month`
  , extract(week from d) `week`
  , extract(day from d) `day`
  , format_date('%Y-Q%Q', d) `quarter`
  , format_date('%Y-%m', d) `year_month`
  , format_date('%b', d) `month_name`
  , format_date('%a', d) `day_name`
  , case when format_date('%a', d) not in ('Fri','Sat','Sun') then 1 else 0 end `is_weekday`
  , date_trunc(d, week) `first_of_the_week`
  , last_day(d, week) `end_of_the_week`
  , date_trunc(d, month) `start_of_the_month`
  , last_day(d, month) `end_of_the_month`
  , date_trunc(d, quarter) `start_of_the_quarter`
  , last_day(d, quarter) `end_of_the_quarter`
from (
  select *
  from unnest(generate_date_array('2018-01-01', '2100-12-31', interval 1 day)) as d )
order by d asc

Nah, jadi misalkan lo ingin mengambil data snapshot setiap akhir bulan, dari tabel my_table. Maka, akan lebih mudah dan cepat dengan cara:

SELECT *
FROM my_table mt
INNER JOIN (SELECT DISTINCT end_of_the_month FROM your_table) yt ON yt.end_of_the_month = mt.observation_date

Mengapa ini lebih cepat? Karena menggunakan INNER JOIN ketimbang memfilter satu-persatu. Tapi, sebenarnya ini lebih cepat dalam pekerjaan. Karena, jadi lebih mudah menambahkan/mengganti periode snapshot. Semisal mau jadi akhir minggu, tinggal ubah:

SELECT *
FROM my_table mt
INNER JOIN (SELECT DISTINCT end_of_the_week FROM your_table) yt ON yt.end_of_the_month = mt.observation_date

Menerapkan Dummy Column dan Koma “,” di Sebelum Nama Kolom

Tips ini juga untuk mempercepat workflow. Biasanya, beberapa dari kita akan menulis kolom yang ingin kita tarik seperti ini:

SELECT 
ID, 
Customer_ID,
Name, 
Age
FROM train t
WHERE 1=1
ORDER BY 1

Problemnya adalah: kalau kita ingin menghapus kolom Age, pasti akan error. Karena, ada koma di kolom Name. Jadi, cara terbaik yang bisa lo lakukan adalah membuat dummy column:

SELECT 1
, ID 
, Customer_ID
, Name
, Age
FROM train t
WHERE 1=1
ORDER BY 1

Sehingga dengan begini, kolom apapun yang akan kita hapus (kecuali 1, namun tidak ada urgensi dalam analytics untuk kita hapus nantinya), tidak akan menimbulkan error.

Sebenarnya sama saja sih kalau mencantumkan si 1 ini di belakang. Namun, gue kurang suka karena kalau ngoding jadinya kurang rapih. Kalau diawal, bisa ditaruh setelah SELECT dan kolom-kolom sisanya di baris baru.

Menggunakan WHERE 1=1 Untuk Mempercepat Cara Kerja

Konsep ini dapat berlaku juga untuk WHERE. Misalkan kita punya banyak kondisi WHERE, kita bisa tambahkan 1=1 (sebagai TRUE condition). Sehingga, penulisannya bisa menjadi:

SELECT 1
, ID 
, Customer_ID
, Name
, Age
FROM train t
WHERE 1=1 
AND Age > 30
ORDER BY 1

Jadi semisal kita tidak ingin menggunakan kondisi Age > 30, tinggal dihapus dengan menjadikan comment tanpa harus menganggu struktur WHERE yang ada. Kalau ingin diaktifkan, tinggal hapus “–” comment-nya.

---

Berikut sekilas SQL tips and tricks dari gue untuk membuat performa query SQL mu menjadi lebih baik. Semoga bermanfaat, coba praktekkin yah!

Baca Juga: Presentasi Efektif Model Machine Learning Untuk Tim Bisnis