Penyesuaian Kinerja Basis Data SQL untuk Pengembang

Penyetelan kinerja SQL bisa menjadi tugas yang sangat sulit, terutama ketika bekerja dengan data berskala besar di mana bahkan perubahan paling kecil pun dapat berdampak dramatis (positif atau negatif) pada kinerja.

Di perusahaan menengah dan besar, sebagian besar penyetelan kinerja SQL akan ditangani oleh Database Administrator (DBA). Tapi percayalah, ada banyak pengembang di luar sana yang harus melakukan tugas seperti DBA. Lebih lanjut, di banyak perusahaan yang pernah saya lihat yang memiliki DBA, mereka sering kesulitan untuk bekerja dengan baik dengan pengembang—posisi tersebut hanya memerlukan mode pemecahan masalah yang berbeda, yang dapat menyebabkan ketidaksepakatan di antara rekan kerja.

Selain itu, struktur perusahaan juga dapat berperan. Katakanlah tim DBA ditempatkan di lantai 10 dengan semua database mereka, sementara para pengembang berada di lantai 15, atau bahkan di gedung yang berbeda di bawah struktur pelaporan yang benar-benar terpisah—tentu saja sulit untuk bekerja sama dengan lancar dalam kondisi ini.

Dalam artikel ini, saya ingin mencapai dua hal:

1. Berikan pengembang beberapa teknik penyetelan kinerja SQL sisi pengembang.
2. Jelaskan bagaimana pengembang dan DBA dapat bekerja sama secara efektif.

Penyetelan Kinerja SQL (dalam Basis Kode): Indeks

Jika Anda benar-benar pendatang baru di database dan bahkan bertanya pada diri sendiri "Apa itu penyetelan kinerja SQL?", Anda harus tahu bahwa pengindeksan adalah cara efektif untuk menyempurnakan database SQL Anda yang sering diabaikan selama pengembangan. Dalam istilah dasar, indeks adalah struktur data yang meningkatkan kecepatan operasi pengambilan data pada tabel database dengan menyediakan pencarian acak yang cepat dan akses yang efisien dari catatan yang dipesan. Ini berarti bahwa setelah Anda membuat indeks, Anda dapat memilih atau mengurutkan baris Anda lebih cepat dari sebelumnya.

Indeks juga digunakan untuk mendefinisikan kunci utama atau indeks unik yang akan menjamin bahwa tidak ada kolom lain yang memiliki nilai yang sama. Tentu saja, pengindeksan basis data adalah topik yang sangat menarik dan saya tidak dapat melakukan keadilan dengan deskripsi singkat ini (tapi inilah penulisan yang lebih rinci).

Jika Anda baru mengenal indeks, saya sarankan menggunakan diagram ini saat menyusun kueri Anda:

Pada dasarnya, tujuannya adalah untuk mengindeks kolom pencarian dan pemesanan utama.

Perhatikan bahwa jika tabel Anda terus-menerus dipalu oleh INSERT , UPDATE , dan DELETE , Anda harus berhati-hati saat mengindeks—Anda bisa berakhir dengan penurunan kinerja karena semua indeks perlu dimodifikasi setelah operasi ini.

Lebih lanjut, DBA sering menjatuhkan indeks SQL mereka sebelum melakukan penyisipan batch dari jutaan baris lebih untuk mempercepat proses penyisipan. Setelah batch dimasukkan, mereka kemudian membuat ulang indeks. Namun, ingat bahwa penurunan indeks akan memengaruhi setiap kueri yang berjalan di tabel itu; jadi pendekatan ini hanya disarankan saat bekerja dengan penyisipan tunggal yang besar.

Penyetelan SQL: Rencana Eksekusi di SQL Server

Omong-omong: alat Rencana Eksekusi di SQL Server dapat berguna untuk membuat indeks.

Fungsi utamanya adalah untuk menampilkan secara grafis metode pengambilan data yang dipilih oleh pengoptimal kueri SQL Server. Jika Anda belum pernah melihatnya sebelumnya, ada panduan terperinci.

Untuk mengambil rencana eksekusi (di SQL Server Management Studio), cukup klik "Sertakan Rencana Eksekusi Aktual" (CTRL + M) sebelum menjalankan kueri Anda.

Setelah itu, tab ketiga bernama "Rencana Eksekusi" akan muncul. Anda mungkin melihat indeks hilang yang terdeteksi. Untuk membuatnya, cukup klik kanan pada rencana eksekusi dan pilih "Rincian Indeks yang Hilang...". Ini sesederhana itu!

( Klik untuk memperbesar )

Penyetelan SQL: Hindari Pengkodean Loop

Bayangkan sebuah skenario di mana 1000 kueri memalu database Anda secara berurutan. Sesuatu seperti:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Anda harus menghindari pengulangan seperti itu dalam kode Anda. Misalnya, kita dapat mengubah cuplikan di atas dengan menggunakan INSERT atau UPDATE unik dengan beberapa baris dan nilai:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Pastikan klausa WHERE Anda menghindari memperbarui nilai yang disimpan jika cocok dengan nilai yang ada. Pengoptimalan sepele seperti itu dapat secara dramatis meningkatkan kinerja kueri SQL dengan memperbarui hanya ratusan baris, bukan ribuan. Sebagai contoh:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

Penyetelan SQL: Hindari Subkueri SQL Berkorelasi

Subquery berkorelasi adalah subquery yang menggunakan nilai dari kueri induk. Jenis kueri SQL ini cenderung dijalankan baris demi baris, sekali untuk setiap baris yang dikembalikan oleh kueri luar, dan dengan demikian menurunkan kinerja kueri SQL. Pengembang SQL baru sering ketahuan menyusun kueri mereka dengan cara ini—karena biasanya ini adalah rute yang mudah.

Berikut ini contoh subquery yang berkorelasi:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

Secara khusus, masalahnya adalah kueri dalam ( SELECT CompanyName… ) dijalankan untuk setiap baris yang dikembalikan oleh kueri luar ( SELECT c.Name… ). Tapi mengapa pergi ke Company lagi dan lagi untuk setiap baris yang diproses oleh kueri luar?

Teknik penyetelan kinerja SQL yang lebih efisien adalah dengan memfaktorkan kembali subquery yang berkorelasi sebagai gabungan:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

Dalam hal ini, kita pergi ke tabel Company sekali saja, di awal, dan GABUNG dengan tabel Customer . Sejak saat itu, kita dapat memilih nilai yang kita butuhkan ( co.CompanyName ) dengan lebih efisien.

Penyetelan SQL: Pilih Hemat

Salah satu tip pengoptimalan SQL favorit saya adalah menghindari SELECT * ! Sebagai gantinya, Anda harus menyertakan kolom spesifik yang Anda butuhkan satu per satu. Sekali lagi, ini terdengar sederhana, tetapi saya melihat kesalahan ini di semua tempat. Pertimbangkan tabel dengan ratusan kolom dan jutaan baris—jika aplikasi Anda hanya benar-benar membutuhkan beberapa kolom, tidak ada gunanya meminta semua data. Ini adalah pemborosan besar-besaran sumber daya. ( Untuk masalah lebih lanjut, lihat di sini. )

Sebagai contoh:

 SELECT * FROM Employees

vs.

 SELECT FirstName, City, Country FROM Employees

Jika Anda benar-benar membutuhkan setiap kolom, cantumkan secara eksplisit setiap kolom. Ini bukan aturan, melainkan sarana untuk mencegah kesalahan sistem di masa mendatang dan penyetelan kinerja SQL tambahan. Misalnya, jika Anda menggunakan INSERT... SELECT... dan tabel sumber telah berubah melalui penambahan kolom baru, Anda mungkin mengalami masalah, meskipun kolom tersebut tidak diperlukan oleh tabel tujuan, misalnya:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Untuk menghindari kesalahan semacam ini dari SQL Server, Anda harus mendeklarasikan setiap kolom satu per satu:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Namun, perhatikan bahwa ada beberapa situasi di mana penggunaan SELECT * bisa jadi tepat. Misalnya, dengan tabel temp—yang membawa kita ke topik berikutnya.

SQL Tuning: Penggunaan Tabel Sementara yang Bijaksana (#Temp)

Tabel sementara biasanya meningkatkan kompleksitas kueri. Jika kode Anda dapat ditulis dengan cara yang sederhana dan lugas, saya sarankan untuk menghindari tabel temp.

Tetapi jika Anda memiliki prosedur tersimpan dengan beberapa manipulasi data yang tidak dapat ditangani dengan satu kueri, Anda dapat menggunakan tabel sementara sebagai perantara untuk membantu Anda menghasilkan hasil akhir.

Ketika Anda harus bergabung dengan tabel besar dan ada kondisi pada tabel tersebut, Anda dapat meningkatkan kinerja database dengan mentransfer data Anda dalam tabel sementara, dan kemudian membuat gabungan pada . Tabel temp Anda akan memiliki lebih sedikit baris daripada tabel asli (besar), sehingga penggabungan akan selesai lebih cepat!

Keputusannya tidak selalu langsung, tetapi contoh ini akan memberi Anda gambaran tentang situasi di mana Anda mungkin ingin menggunakan tabel temp:

Bayangkan sebuah tabel pelanggan dengan jutaan catatan. Anda harus bergabung di wilayah tertentu. Anda dapat mencapai ini dengan menggunakan pernyataan SELECT INTO dan kemudian bergabung dengan tabel temp:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Catatan: beberapa pengembang SQL juga menghindari penggunaan SELECT INTO untuk membuat tabel temp, dengan mengatakan bahwa perintah ini mengunci database tempdb, melarang pengguna lain membuat tabel temp. Untungnya, ini diperbaiki di 7.0 dan yang lebih baru .)

Sebagai alternatif untuk tabel temp, Anda dapat mempertimbangkan untuk menggunakan subquery sebagai tabel:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Tapi tunggu! Ada masalah dengan kueri kedua ini. Seperti dijelaskan di atas, kita seharusnya hanya memasukkan kolom yang kita butuhkan di subquery kita (yaitu, tidak menggunakan SELECT * ). Mempertimbangkan hal itu:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Semua cuplikan SQL ini akan mengembalikan data yang sama. Tetapi dengan tabel temp, kita dapat, misalnya, membuat indeks di tabel temp untuk meningkatkan kinerja. Ada beberapa diskusi bagus di sini tentang perbedaan antara tabel sementara dan subkueri.

Terakhir, ketika Anda selesai dengan tabel temp Anda, hapus untuk menghapus sumber daya tempdb, daripada hanya menunggu untuk dihapus secara otomatis (seperti ketika koneksi Anda ke database dihentikan):

 DROP TABLE #temp

Penyetelan SQL: "Apakah Catatan Saya Ada?"

Teknik optimasi SQL ini menyangkut penggunaan EXISTS() . Jika Anda ingin memeriksa apakah ada catatan, gunakan EXISTS() alih-alih COUNT() . Sementara COUNT() memindai seluruh tabel, menghitung semua entri yang cocok dengan kondisi Anda, EXISTS() akan keluar segera setelah melihat hasil yang dibutuhkan. Ini akan memberi Anda kinerja yang lebih baik dan kode yang lebih jelas.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

vs.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

Penyesuaian Kinerja SQL Dengan SQL Server 2016

Seperti yang diketahui oleh DBA yang bekerja dengan SQL Server 2016, versi tersebut menandai perubahan penting dalam default dan manajemen kompatibilitas. Sebagai versi utama, tentu saja, dilengkapi dengan pengoptimalan kueri baru, tetapi kontrol atas apakah mereka digunakan sekarang disederhanakan melalui sys.databases.compatibility_level .

Penyesuaian Kinerja SQL (di Office)

Administrator database SQL (DBA) dan pengembang sering bentrok karena masalah terkait data dan non-data. Berdasarkan pengalaman saya, berikut adalah beberapa tips (untuk kedua belah pihak) tentang cara bergaul dan bekerja sama secara efektif.

Menciak

Pengoptimalan Basis Data untuk Pengembang:

1. Jika aplikasi Anda berhenti bekerja tiba-tiba, itu mungkin bukan masalah database. Misalnya, mungkin Anda memiliki masalah jaringan. Selidiki sedikit sebelum Anda menuduh DBA!

2. Bahkan jika Anda seorang ninja pemodel data SQL, mintalah DBA untuk membantu Anda dengan diagram relasional Anda. Mereka memiliki banyak hal untuk dibagikan dan ditawarkan.

3. DBA tidak menyukai perubahan yang cepat. Ini wajar: mereka perlu menganalisis database secara keseluruhan dan memeriksa dampak dari setiap perubahan dari semua sudut. Perubahan sederhana dalam sebuah kolom bisa memakan waktu seminggu untuk diterapkan—tetapi itu karena kesalahan bisa menjadi kerugian besar bagi perusahaan. Sabar!

4. Jangan meminta SQL DBA untuk membuat perubahan data di lingkungan produksi. Jika Anda ingin mengakses database produksi, Anda harus bertanggung jawab atas semua perubahan Anda sendiri.

Pengoptimalan Basis Data untuk DBA SQL Server:

1. Jika Anda tidak suka orang bertanya tentang database, beri mereka panel status waktu nyata. Pengembang selalu curiga dengan status basis data, dan panel semacam itu dapat menghemat waktu dan energi semua orang.

2. Bantu pengembang dalam lingkungan pengujian/jaminan kualitas. Permudah simulasi server produksi dengan pengujian sederhana pada data dunia nyata. Ini akan menjadi penghemat waktu yang signifikan bagi orang lain dan juga diri Anda sendiri.

3. Pengembang menghabiskan sepanjang hari pada sistem dengan logika bisnis yang sering berubah. Cobalah untuk memahami dunia ini menjadi lebih fleksibel, dan dapat melanggar beberapa aturan di saat yang kritis.

4. Database SQL berkembang. Harinya akan tiba ketika Anda harus memigrasikan data ke versi baru. Pengembang mengandalkan fungsionalitas baru yang signifikan dengan setiap versi baru. Alih-alih menolak untuk menerima perubahan mereka, rencanakan ke depan dan bersiaplah untuk migrasi.