Optimasi Query SQL: 15 Teknik untuk Kinerja yang Lebih Baik

6. Optimalkan Klausa WHERE

Klausa WHERE sangat penting dalam query SQL karena memungkinkan kita memfilter data berdasarkan kondisi tertentu, memastikan hanya record yang relevan yang dikembalikan. Ini meningkatkan efisiensi query dengan mengurangi jumlah data yang diproses, yang sangat penting saat bekerja dengan dataset besar. 

Jadi, klausa WHERE yang tepat bisa menjadi andalan saat kita mengoptimalkan performa query SQL. Berikut beberapa cara untuk memanfaatkannya:

• Tambahkan kondisi penyaringan yang tepat sejak awal. Terkadang, memiliki klausa WHERE saja belum cukup. Kita harus berhati-hati di mana kita menempatkannya. Menyaring sebanyak mungkin baris sedini mungkin dalam klausa WHERE dapat membantu mengoptimalkan query.
• Hindari menggunakan fungsi pada kolom dalam klausa WHERE. Saat kita menerapkan fungsi pada sebuah kolom, database harus menerapkan fungsi tersebut pada setiap baris di tabel sebelum dapat memfilter hasil. Hal ini mencegah database menggunakan indeks secara efektif.

Sebagai contoh, alih-alih: 

SELECT * 
FROM employees WHERE 
YEAR(hire_date) = 2020;

Kita sebaiknya menggunakan: 

SELECT * 
FROM employees 
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

• Gunakan operator yang sesuai. Kita harus memilih operator yang paling efisien sesuai kebutuhan. Misalnya, = umumnya lebih cepat daripada LIKE, dan menggunakan rentang tanggal spesifik lebih cepat daripada menggunakan fungsi seperti MONTH(order_date).

Jadi, misalnya, alih-alih menjalankan query berikut:

SELECT * 
FROM orders 
WHERE MONTH(order_date) = 12 AND YEAR(order_date) = 2023;

Kita dapat menjalankan yang berikut: 

SELECT * 
FROM orders 
WHERE order_date >= '2023-12-01' AND order_date < '2024-01-01';

7. Optimalkan Subquery

Dalam beberapa kasus, saat menulis query kita merasa perlu melakukan pemfilteran, agregasi, atau join data secara dinamis. Kita tidak ingin membuat beberapa query; kita ingin tetap satu query saja. 

Untuk kasus tersebut, kita dapat menggunakan subquery. Subquery dalam SQL adalah query yang disarang di dalam query lain, biasanya pada pernyataan SELECT, INSERT, UPDATE, atau DELETE. 

Subquery bisa kuat dan cepat, tetapi juga dapat menyebabkan masalah performa jika tidak digunakan dengan hati-hati. Sebagai aturan, kita perlu meminimalkan penggunaan subquery dan mengikuti serangkaian praktik terbaik:

• Ganti subquery dengan join bila memungkinkan. Join umumnya lebih cepat dan lebih efisien daripada subquery.
• Gunakan common table expression (CTE) sebagai gantinya.  CTE memecah kode kita menjadi beberapa query kecil alih-alih satu query besar, sehingga jauh lebih mudah dibaca.

WITH SalesCTE AS ( 
             SELECT salesperson_id, SUM(sales_amount) AS total_sales 
             FROM sales GROUP BY salesperson_id ) 

SELECT salesperson_id, total_sales 
FROM SalesCTE WHERE total_sales > 5000;

• Gunakan subquery tidak berkorelasi. Subquery yang tidak berkorelasi bersifat independen dari query luar dan dapat dieksekusi sekali, sedangkan subquery berkorelasi dieksekusi untuk setiap baris dalam query luar.

8. Gunakan EXISTS Alih-alih IN untuk Subquery

Saat bekerja dengan subquery, kita sering perlu memeriksa apakah suatu nilai ada dalam sekumpulan hasil. Kita dapat melakukannya dengan IN atau EXISTS, tetapi EXISTS umumnya lebih efisien, terutama untuk dataset yang lebih besar.

Klausa IN membaca seluruh hasil subquery ke dalam memori sebelum membandingkan. Di sisi lain, klausa EXISTS menghentikan pemrosesan subquery segera setelah menemukan kecocokan. 

Berikut contoh cara menggunakan klausa ini:

SELECT * 
FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.country = 'USA');

9. Batasi Penggunaan DISTINCT

Bayangkan kita mengerjakan analisis untuk mengirim penawaran promosi kepada pelanggan dari kota yang unik. Database memiliki banyak pesanan dari pelanggan yang sama. Hal pertama yang terlintas di benak kita adalah menggunakan klausa DISTINCT.

Fungsi ini berguna untuk kasus tertentu tetapi bisa menguras sumber daya, terutama pada dataset besar. Ada beberapa alternatif untuk DISTINCT:

• Identifikasi dan hapus data duplikat saat proses pembersihan data. Ini mencegah duplikasi masuk ke database sejak awal.
• Gunakan GROUP BY alih-alih DISTINCT bila memungkinkan. GROUP BY bisa lebih efisien, terutama jika digabungkan dengan fungsi agregat. 

Jadi, alih-alih menjalankan:

SELECT DISTINCT city FROM customers;

Kita dapat menggunakan:

SELECT city FROM customers GROUP BY city;

• Gunakan fungsi jendela. Fungsi jendela seperti ROW_NUMBER dapat membantu kita mengidentifikasi duplikasi dan menyaringnya tanpa menggunakan DISTINCT.

10. Manfaatkan Fitur Spesifik Database

Saat bekerja dengan data, kita berinteraksi menggunakan SQL melalui Database Management System (DBMS). DBMS memproses perintah SQL, mengelola database, dan memastikan integritas serta keamanan data. Sistem database yang berbeda menawarkan fitur unik yang dapat membantu mengoptimalkan query. 

Database hint adalah instruksi khusus yang dapat kita tambahkan ke query agar dieksekusi lebih efisien. Hint berguna, tetapi harus digunakan dengan hati-hati. 

Contohnya, di MySQL, hint USE INDEX dapat memaksa penggunaan indeks tertentu:

 SELECT * FROM employees USE INDEX (idx_salary) WHERE salary > 50000;

Di SQL Server, hint OPTION (LOOP JOIN) menentukan metode join: 

SELECT * 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id OPTION (LOOP JOIN); 

Hint ini menimpa optimasi query bawaan, meningkatkan performa dalam skenario tertentu.

Di sisi lain, partitioning dan sharding adalah dua teknik untuk mendistribusikan data di cloud. 

• Dengan partitioning, kita membagi satu tabel besar menjadi beberapa tabel lebih kecil, masing-masing dengan kunci partisi. Kunci partisi biasanya didasarkan pada timestamp saat baris dibuat atau nilai integer yang dikandungnya. Saat kita menjalankan query pada tabel ini, server akan secara otomatis mengarahkan kita ke tabel partisi yang sesuai untuk query tersebut. 
• Sharding cukup mirip, kecuali alih-alih membagi satu tabel besar menjadi beberapa tabel yang lebih kecil, sharding membagi satu database besar menjadi beberapa database yang lebih kecil. Masing-masing database ini berada di server yang berbeda. Alih-alih kunci partisi, kunci sharding mengarahkan query untuk dijalankan pada database yang sesuai. Sharding meningkatkan kecepatan pemrosesan karena beban dibagi ke berbagai server. 

11. Pantau dan Optimalkan Statistik Database

Menjaga statistik database tetap mutakhir penting agar query optimizer dapat membuat keputusan yang tepat dan akurat terkait cara paling efisien mengeksekusi query. 

Statistik menjelaskan distribusi data dalam sebuah tabel (misalnya, jumlah baris, frekuensi nilai, dan sebaran nilai di berbagai kolom), dan optimizer bergantung pada informasi ini untuk memperkirakan biaya eksekusi query. Jika statistik usang, optimizer dapat memilih rencana eksekusi yang tidak efisien, seperti menggunakan indeks yang salah atau memilih full table scan alih-alih index scan yang lebih efisien, yang mengarah pada performa query yang buruk.

Database sering mendukung pembaruan otomatis untuk mempertahankan statistik yang akurat. Misalnya, di SQL Server, konfigurasi default secara otomatis memperbarui statistik saat sejumlah besar data berubah. Demikian pula, PostgreSQL memiliki fitur auto-analyze, yang memperbarui statistik setelah ambang batas modifikasi data tertentu tercapai. 

Namun, kita dapat memperbarui statistik secara manual jika pembaruan otomatis tidak memadai atau jika diperlukan intervensi manual. Di SQL Server, kita dapat menggunakan perintah UPDATE STATISTICS untuk menyegarkan statistik untuk tabel atau indeks tertentu, sementara di PostgreSQL, perintah ANALYZE dapat dijalankan untuk memperbarui statistik untuk satu atau lebih tabel. 

-- Update statistics for all tables in the current database
ANALYZE;

-- Update statistics for a specific table
ANALYZE my_table;

12. Manfaatkan Stored Procedure

Stored procedure adalah sekumpulan perintah SQL yang kita simpan di database sehingga kita tidak perlu menulis SQL yang sama berulang kali. Anggap saja sebagai skrip yang dapat digunakan kembali. 

Saat kita perlu melakukan tugas tertentu, seperti memperbarui record atau menghitung nilai, kita cukup memanggil stored procedure. Stored procedure dapat menerima input, melakukan pekerjaan seperti melakukan query atau memodifikasi data, dan bahkan mengembalikan hasil. Stored procedure membantu mempercepat proses karena SQL sudah dikompilasi sebelumnya, sehingga kode Anda lebih bersih dan lebih mudah dikelola. 

Kita dapat membuat stored procedure di PostgreSQL sebagai berikut:

CREATE OR REPLACE PROCEDURE insert_employee(
    emp_id INT,
    emp_first_name VARCHAR,
    emp_last_name VARCHAR
)
LANGUAGE plpgsql
AS $
BEGIN
    -- Insert a new employee into the employees table
    INSERT INTO employees (employee_id, first_name, last_name)
    VALUES (emp_id, emp_first_name, emp_last_name);
END;
$;

-- call the procedure
CALL insert_employee(101, 'John', 'Doe');

13. Hindari Pengurutan dan Pengelompokan yang Tidak Perlu

Sebagai praktisi data, kita senang data kita diurutkan dan dikelompokkan agar lebih mudah mendapatkan insight. Kita biasanya menggunakan ORDER BY dan GROUP BY dalam query SQL.

Namun, kedua klausa ini dapat mahal secara komputasi, terutama saat menangani dataset besar. Saat mengurutkan atau mengagregasikan data, mesin database sering kali harus melakukan pemindaian penuh terhadap data lalu mengaturnya, mengidentifikasi grup, dan/atau menerapkan fungsi agregat, biasanya menggunakan algoritme yang intensif sumber daya. 

Untuk mengoptimalkan query, kita dapat mengikuti beberapa tips berikut:

• Minimalkan pengurutan. Kita sebaiknya hanya menggunakan ORDER BY saat diperlukan. Jika pengurutan tidak esensial, menghilangkan klausa ini dapat sangat mengurangi waktu pemrosesan. 
• Gunakan indeks. Jika memungkinkan, pastikan kolom yang terlibat dalam ORDER BY dan GROUP BY terindeks. 
• Pindahkan pengurutan ke lapisan aplikasi. Jika memungkinkan, lakukan operasi pengurutan di lapisan aplikasi alih-alih di database. 
• Pra-agregasi data. Untuk query kompleks yang melibatkan GROUP BY, kita dapat melakukan pra-agregasi data pada tahap lebih awal atau dalam sebuah materialized view, sehingga database tidak perlu menghitung agregat yang sama berulang kali.

14. Gunakan UNION ALL Alih-alih UNION

Saat kita ingin menggabungkan hasil dari beberapa query menjadi satu daftar, kita dapat menggunakan klausa UNION dan UNION ALL. Keduanya menggabungkan hasil dari dua atau lebih pernyataan SELECT ketika memiliki nama kolom yang sama. Namun, keduanya tidak sama, dan perbedaannya membuatnya cocok untuk kasus penggunaan yang berbeda.

Klausa UNION menghapus baris duplikat, yang memerlukan waktu pemrosesan lebih lama. 

Gambar: Union dalam SQL. Sumber gambar: Lembar contekan SQL-Join DataCamp.

Di sisi lain, UNION ALL menggabungkan hasil tetapi mempertahankan semua baris, termasuk duplikasi. Jadi, jika kita tidak perlu menghapus duplikasi, sebaiknya gunakan UNION ALL untuk performa yang lebih baik.

Gambar: UNION ALL dalam SQL. Sumber: Lembar contekan SQL-Join DataCamp.

-- Potentially slower
SELECT product_id FROM products WHERE category = 'Electronics'
UNION
SELECT product_id FROM products WHERE category = 'Books';

-- Potentially faster
SELECT product_id FROM products WHERE category = 'Electronics'
UNION ALL
SELECT product_id FROM products WHERE category = 'Books';

15. Pecah Query yang Kompleks

Bekerja dengan dataset besar berarti kita sering menemui query kompleks yang sulit dipahami dan dioptimalkan. Kita dapat mencoba mengatasinya dengan memecahnya menjadi query yang lebih kecil dan sederhana. Dengan cara ini, kita lebih mudah mengidentifikasi bottleneck performa dan menerapkan teknik optimasi.

Salah satu strategi yang paling sering digunakan untuk memecah query adalah materialized view. Ini adalah hasil query yang dihitung dan disimpan sebelumnya sehingga dapat diakses dengan cepat alih-alih menghitung ulang setiap kali direferensikan. Saat data dasar berubah, materialized view harus disegarkan secara manual atau otomatis.

Berikut contoh cara membuat dan melakukan query pada materialized view:

-- Create a materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id;

-- Query the materialized view
SELECT * FROM daily_sales;

Kesimpulan

Dalam artikel ini, kita telah mengeksplorasi berbagai strategi dan praktik terbaik untuk mengoptimalkan query SQL, mulai dari pengindeksan dan join hingga subquery dan fitur spesifik database. Dengan menerapkan teknik-teknik ini, Anda dapat secara signifikan meningkatkan performa query dan membuat database kita berjalan lebih efisien.

Ingat, mengoptimalkan query SQL adalah proses yang berkelanjutan. Seiring pertumbuhan data Anda dan evolusi aplikasi Anda, Anda perlu terus memantau dan mengoptimalkan query agar berjalan pada performa terbaik.

Untuk semakin meningkatkan pemahaman Anda tentang SQL, kami mendorong Anda menjelajahi sumber daya berikut di DataCamp:

• Lembar Contekan Dasar SQL
• Meningkatkan Performa Query di SQL Server
• Meningkatkan Performa Query di PostgreSQL