Mengoptimalkan Kueri SQL: Contoh Menggunakan MSSQL

Kueri SQL Microsoft SQL Server (MSSQL) yang tidak dioptimalkan dapat menghasilkan kinerja di bawah standar, penggunaan sumber daya yang berlebihan, data yang tidak konsisten, kelemahan keamanan, dan kesulitan pemeliharaan. Masalah ini dapat memengaruhi fungsionalitas, keandalan, dan keamanan program Anda, yang dapat membuat pengguna frustrasi dan menghabiskan lebih banyak uang.

Mengoptimalkan kueri SQL di MSSQL sangat penting untuk mencapai kinerja yang efisien dan cepat. Dengan menerapkan teknik seperti pengindeksan, penyederhanaan kueri, dan prosedur penyimpanan, pengguna dapat menyeimbangkan kinerja kueri dan kinerja modifikasi data, yang pada akhirnya meningkatkan kinerja database secara keseluruhan.

Baca Juga:

Mau Berkarir di Dunia blockchain? Ini Dia 10 Skill yang Harus Kamu Dalami!

Optimasi Kueri Melalui Pengindeksan

Pengindeksan database mengatur dan mengurutkan data dalam tabel database untuk membuat pencarian lebih cepat dan lebih efisien. Pengindeksan membuat salinan data dalam tabel dan mengurutkannya sehingga mesin database dapat menavigasinya dengan mudah.

Pada eksekusi kueri, mesin basis data menggunakan indeks untuk menemukan data yang diperlukan dan mengembalikan hasil dengan cepat, sehingga mempercepat waktu eksekusi kueri. Tanpa pengindeksan, mesin basis data harus memindai semua baris dalam tabel untuk menemukan data yang diperlukan, dan ini bisa memakan banyak waktu dan sumber daya, terutama untuk tabel besar.

Membuat Indeks di MSSQL

Membuat indeks dalam database relasional itu mudah, dan MSSQL tidak diabaikan. Anda dapat menggunakan BUAT INDEKS pernyataan untuk membuat indeks di MSSQL.

 CREATE INDEX index_name
ON table_name (column1, column2, ...);
 

Pada kode SQL di atas, index_name adalah nama indeks, table_name adalah nama tabel, dan kolom1, kolom2dan seterusnya adalah nama kolom yang akan diindeks.

Berikut cara membuat indeks non-cluster pada a Pelanggan meja Nama keluarga kolom dengan pernyataan SQL.

 CREATE NONCLUSTERED INDEX IX_Customers_LastName
ON Customers (LastName);
 

Pernyataan itu membuat indeks non-cluster yang disebut IX_Pelanggan_NamaBelakang pada Nama keluarga kolom dari Pelanggan meja.

Biaya Pengindeksan

Meskipun pengindeksan dapat meningkatkan kinerja kueri secara signifikan, namun ada biayanya. Pengindeksan memerlukan ruang disk tambahan untuk menyimpan indeks, dan operasi pengindeksan dapat memperlambat operasi modifikasi data seperti menyisipkan, memperbarui, dan menghapus. Anda harus memperbarui indeks pada modifikasi data, dan operasi pembaruan dapat memakan waktu lama untuk tabel besar.

Oleh karena itu, penting untuk menyeimbangkan kinerja kueri dan kinerja modifikasi data. Anda harus membuat indeks hanya pada kolom yang sering Anda cari. Penting juga untuk secara teratur memantau penggunaan sumber daya indeks dan menghapus indeks yang tidak perlu.

Optimasi Query Melalui Penyederhanaan Query

Untuk analisis data, kueri kompleks berguna untuk ekstraksi data. Namun, kueri kompleks berdampak pada kinerja, dan dapat mengakibatkan ekstraksi data yang tidak efisien.

Penyederhanaan kueri melibatkan penguraian kueri kompleks menjadi kueri yang lebih kecil dan sederhana untuk pemrosesan yang lebih cepat dan tidak memakan banyak sumber daya.

Penyederhanaan kueri meningkatkan kinerja dan mempermudah ekstraksi data dengan memecah kueri kompleks menjadi kueri yang lebih sederhana karena kueri kompleks dapat menyebabkan hambatan dalam sistem. Mereka sulit dipahami, mempersulit pengembang dan analis untuk memecahkan masalah atau mengidentifikasi area pengoptimalan.

Berikut adalah contoh kueri kompleks yang berjalan di MSSQL untuk tabel pesanan pelanggan yang tujuannya adalah untuk mengidentifikasi tren dan pola dalam data:

 SELECT
  customer_name,
  COUNT(order_id) AS total_orders,
  AVG(order_amount) AS average_order_amount,
  SUM(order_amount) AS total_sales
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status="completed"
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5
ORDER BY
  total_sales DESC;
 

Kueri mencari nama pelanggan dan informasi penjualan dari pesanan yang diselesaikan pada tahun 2022 sambil memfilter pelanggan dengan kurang dari lima pesanan menurut total penjualan dalam urutan menurun.

Kueri mungkin memberikan wawasan yang berharga, tetapi rumit dan pada akhirnya akan memakan waktu lebih lama untuk diproses, terutama jika pesanan tabel memiliki banyak entri.

Anda dapat menyederhanakan kueri dengan memecah kueri menjadi kueri yang lebih kecil yang dijalankan satu per satu.

 
SELECT
  customer_name,
  COUNT(order_id) AS total_orders
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status="completed"
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5;

SELECT
  customer_name,
  AVG(order_amount) AS average_order_amount
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status="completed"
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5;

SELECT
  customer_name,
  SUM(order_amount) AS total_sales
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status="completed"
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5
ORDER BY
  total_sales DESC;
 

Pendekatan yang disederhanakan ini memisahkan tugas untuk mendapatkan nama pelanggan dan pesanan total, jumlah pesanan rata-rata, dan total penjualan untuk setiap pelanggan ke dalam kueri individual. Setiap kueri memiliki tujuan tertentu dan dioptimalkan untuk tugas tertentu, sehingga memudahkan basis data untuk memproses permintaan.

Tips untuk Penyederhanaan Kueri

Saat Anda menyederhanakan kueri, penting untuk fokus pada satu tugas per waktu untuk membuat kueri yang dioptimalkan untuk tugas tertentu. Berfokus pada satu tugas dapat membantu meningkatkan kinerja secara signifikan.

Selain itu, penting untuk menggunakan konvensi penamaan yang baik agar kode lebih mudah dipahami dan dipelihara. Anda dapat dengan mudah mengidentifikasi masalah potensial dan area perbaikan dalam sistem.

Optimasi Kueri Melalui Prosedur Tersimpan

Prosedur tersimpan adalah kumpulan pernyataan SQL yang telah ditulis sebelumnya yang disimpan dalam database. Anda dapat menggunakan prosedur tersimpan untuk melakukan berbagai operasi, mulai dari memperbarui hingga memasukkan atau mengambil data dari database. Prosedur tersimpan dapat menerima parameter. Anda dapat memanggil mereka dari berbagai bahasa pemrograman, menjadikannya alat yang ampuh untuk pengembangan aplikasi.

Berikut adalah contoh membuat prosedur tersimpan untuk MSSQL yang mengembalikan gaji rata-rata karyawan di departemen:

 CREATE PROCEDURE [dbo].[GetAverageSalary]
@DepartmentName VARCHAR(50)
AS
BEGIN
SELECT AVG(Salary) as AverageSalary
FROM Employees
WHERE Department = @DepartmentName
END
 

Dalam prosedur tersimpan, Anda menentukan parameter yang disebut @Nama departemen dan menggunakan DI MANA klausul untuk menyaring hasil oleh departemen. Anda juga menggunakan AVG berfungsi untuk menghitung gaji rata-rata karyawan di departemen.

Anda dapat menjalankan prosedur tersimpan di MSSQL dengan EXEC penyataan.

Inilah cara Anda dapat menjalankan prosedur tersimpan di atas:

 EXEC [dbo].[GetAverageSalary] @DepartmentName="Sales"
 

Dalam pernyataan eksekusi prosedur tersimpan, Anda meneruskan Penjualan nilai sebagai nama departemen. Pernyataan itu akan mengembalikan gaji rata-rata karyawan di departemen penjualan.

Bagaimana Prosedur Tersimpan Meningkatkan Kinerja Kueri?

Prosedur tersimpan dapat meningkatkan kinerja kueri secara signifikan. Pertama, prosedur tersimpan mengurangi lalu lintas jaringan dengan mengeksekusi pernyataan SQL di sisi server daripada mentransfer data bolak-balik antara klien dan server, sehingga mengurangi jumlah data yang dikirim melalui jaringan dan mengurangi waktu respons kueri.

Kedua, Anda dapat mengkompilasi prosedur caching tersimpan, yang berarti menyimpan rencana eksekusi di memori. Saat Anda memanggil prosedur tersimpan, server mengambil rencana eksekusi dari memori daripada mengkompilasi ulang pernyataan SQL, sehingga mengurangi waktu eksekusi untuk prosedur tersimpan dan meningkatkan kinerja kueri.

Anda Dapat Mengatur MSSQL di Ubuntu

MSSQL telah membuat langkah signifikan dalam mendukung Ubuntu dan distribusi Linux lainnya. Microsoft menyadari semakin populernya Linux di perusahaan dan memutuskan untuk memperluas ketersediaan server database andalan mereka ke platform Linux.