Query 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.
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 pernyataan CREATE INDEX 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, kolom2, dan seterusnya adalah nama kolom yang akan diindeks.
Berikut cara membuat indeks non-cluster pada kolom LastName tabel Customers dengan pernyataan SQL.
CREATE NONCLUSTERED INDEX IX_Customers_LastName ON Customers (LastName);
Pernyataan tersebut membuat indeks non-cluster yang disebut IX_Customers_LastName pada kolom LastName pada tabel Customers.
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;
Query 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.
Query mungkin memberikan wawasan yang berharga, tetapi rumit dan pada akhirnya akan memakan waktu lebih lama untuk diproses, terutama jika tabel pesanan memiliki banyak entri.
Anda dapat menyederhanakan kueri dengan memecah kueri menjadi kueri yang lebih kecil yang dijalankan satu per satu.
-- Get a list of customer names and the total number of orders they have placed 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; -- Get the average order amount for each customer 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; -- Get the total sales for each customer 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 Query
Saat Anda menyederhanakan Query, penting untuk fokus pada satu tugas per waktu untuk membuat Query 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 Query Melalui Stored Procedures
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
Di stored procedure, Anda menentukan parameter bernama @DepartmentName dan menggunakan klausa WHERE untuk memfilter hasil berdasarkan departemen. Anda juga menggunakan fungsi AVG untuk menghitung gaji rata-rata karyawan di departemen tersebut.
Anda dapat menjalankan prosedur tersimpan di MSSQL dengan pernyataan EXEC.
Inilah cara Anda dapat menjalankan prosedur tersimpan di atas:
EXEC [dbo].[GetAverageSalary] @DepartmentName = 'Sales'
Dalam pernyataan eksekusi prosedur tersimpan, Anda meneruskan nilai Penjualan sebagai nama departemen. Pernyataan itu akan mengembalikan gaji rata-rata karyawan di departemen penjualan.
Bagaimana Stored Procedures Meningkatkan Kinerja Query?
Stored Procedures dapat meningkatkan kinerja Query secara signifikan. Pertama, Stored Procedures 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 stored caching procedures, 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 Query.
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.
