SQL dilepaskan: 17 cara untuk mempercepat kueri SQL Anda

Pengembang SQL di setiap platform sedang berjuang, tampaknya terjebak dalam DO WHILElingkaran yang membuat mereka mengulangi kesalahan yang sama berulang kali. Itu karena bidang database masih relatif belum matang. Tentu, vendor membuat beberapa langkah, tetapi mereka terus bergulat dengan masalah yang lebih besar. Konkurensi, manajemen sumber daya, manajemen ruang, dan kecepatan masih mengganggu pengembang SQL apakah mereka melakukan pengkodean di SQL Server, Oracle, DB2, Sybase, MySQL, atau platform relasional lainnya.

Sebagian dari masalahnya adalah tidak ada peluru ajaib, dan untuk hampir setiap praktik terbaik, saya dapat menunjukkan setidaknya satu pengecualian. Biasanya, pengembang menemukan metode favoritnya sendiri — meskipun biasanya metode tersebut tidak menyertakan konstruksi apa pun untuk kinerja atau konkurensi — dan tidak repot-repot menjelajahi opsi lain. Mungkin itu adalah gejala kurangnya pendidikan, atau para pengembang terlalu dekat dengan proses untuk mengenali saat mereka melakukan sesuatu yang salah. Mungkin kueri berjalan dengan baik pada sekumpulan data pengujian lokal tetapi gagal total pada sistem produksi.

Saya tidak berharap pengembang SQL menjadi administrator, tetapi mereka harus mempertimbangkan masalah produksi saat menulis kode mereka. Jika mereka tidak melakukannya selama pengembangan awal, DBA hanya akan membuat mereka kembali dan melakukannya nanti — dan pengguna menderita untuk sementara waktu.

Ada alasan mengapa kami mengatakan menyetel database adalah seni dan sains. Itu karena sangat sedikit aturan keras dan cepat yang berlaku di seluruh papan. Masalah yang telah Anda selesaikan di satu sistem bukanlah masalah di sistem lain, dan sebaliknya. Tidak ada jawaban yang benar untuk pertanyaan tuning, tapi itu tidak berarti Anda harus menyerah.

Ada beberapa prinsip bagus yang dapat Anda ikuti yang akan memberikan hasil dalam satu kombinasi atau lainnya. Saya telah merangkumnya dalam daftar SQL dos dan tidak boleh dilakukan yang sering terlewatkan atau sulit dikenali. Teknik-teknik ini akan memberi Anda lebih banyak wawasan tentang pikiran DBA Anda, serta kemampuan untuk mulai memikirkan proses dengan cara yang berorientasi pada produksi.

1. Jangan gunakan UPDATEsebagai gantinyaCASE

Masalah ini sangat umum, dan meskipun tidak sulit dikenali, banyak pengembang sering mengabaikannya karena penggunaan UPDATEmemiliki alur alami yang tampaknya logis.

Ambil skenario ini, misalnya: Anda memasukkan data ke tabel temp dan membutuhkannya untuk menampilkan nilai tertentu jika ada nilai lain. Mungkin Anda menarik diri dari tabel Pelanggan dan ingin siapa pun yang memiliki pesanan lebih dari $ 100.000 diberi label sebagai "Lebih disukai". Jadi, Anda memasukkan data ke dalam tabel dan menjalankan UPDATEpernyataan untuk menyetel kolom CustomerRank ke "Preferred" untuk siapa saja yang memiliki pesanan lebih dari $ 100.000. Masalahnya adalah bahwa UPDATEpernyataan tersebut dicatat, yang berarti harus menulis dua kali untuk setiap penulisan ke tabel. Jalan keluarnya, tentu saja, adalah dengan menggunakan CASEpernyataan sebaris dalam kueri SQL itu sendiri. Ini menguji setiap baris untuk kondisi jumlah pesanan dan menetapkan label "Pilihan" sebelum ditulis ke tabel. Peningkatan kinerja bisa mengejutkan.

2. Jangan menggunakan kembali kode secara membabi buta

Masalah ini juga sangat umum. Sangat mudah untuk menyalin kode orang lain karena Anda tahu itu menarik data yang Anda butuhkan. Masalahnya adalah sering kali ia menarik lebih banyak data daripada yang Anda butuhkan, dan pengembang jarang repot-repot memangkasnya, sehingga mereka berakhir dengan superset data yang sangat besar. Ini biasanya datang dalam bentuk gabungan luar ekstra atau kondisi ekstra dalam WHEREklausa. Anda bisa mendapatkan peningkatan kinerja yang besar jika Anda memangkas kode yang digunakan kembali sesuai kebutuhan Anda.

3. Lakukan tarik hanya sejumlah kolom yang Anda butuhkan

Masalah ini mirip dengan masalah No. 2, tetapi khusus untuk kolom. Terlalu mudah untuk mengkodekan semua kueri Anda SELECT *daripada mencantumkan kolom satu per satu. Masalahnya lagi adalah itu menarik lebih banyak data daripada yang Anda butuhkan. Saya telah melihat kesalahan ini lusinan kali. Pengembang melakukan SELECT *kueri terhadap tabel dengan 120 kolom dan jutaan baris, tetapi akhirnya hanya menggunakan tiga hingga lima baris. Pada titik itu, Anda memproses lebih banyak data daripada yang Anda butuhkan, sungguh menakjubkan jika kueri kembali sama sekali. Anda tidak hanya memproses lebih banyak data daripada yang Anda butuhkan, tetapi Anda juga mengambil sumber daya dari proses lain.

4. Jangan mencelupkan dua kali

Ini satu lagi yang pernah saya lihat lebih dari yang seharusnya: Prosedur tersimpan ditulis untuk menarik data dari tabel dengan ratusan juta baris. Pengembang membutuhkan pelanggan yang tinggal di California dan memiliki pendapatan lebih dari $ 40.000. Jadi dia bertanya kepada pelanggan yang tinggal di California dan memasukkan hasilnya ke tabel temporer; lalu dia menanyakan pelanggan dengan pendapatan di atas $ 40.000 dan memasukkan hasil tersebut ke tabel temp lain. Akhirnya, ia menggabungkan kedua tabel tersebut untuk mendapatkan produk akhir.

Apakah kamu bercanda? Ini harus dilakukan dalam satu kueri; sebaliknya, Anda mencelupkan dua kali meja yang sangat besar. Jangan menjadi orang bodoh: Buat kueri tabel besar hanya sekali jika memungkinkan — Anda akan menemukan betapa jauh lebih baik kinerja prosedur Anda.

Skenario yang sedikit berbeda adalah ketika subset dari tabel besar dibutuhkan oleh beberapa langkah dalam proses, yang menyebabkan tabel besar akan di-query setiap kali. Hindari ini dengan membuat kueri untuk subset dan menyimpannya di tempat lain, lalu arahkan langkah-langkah selanjutnya ke kumpulan data yang lebih kecil.

6. Lakukan data pra-tahap

Ini adalah salah satu topik favorit saya karena ini adalah teknik lama yang sering diabaikan. Jika Anda memiliki laporan atau prosedur (atau lebih baik lagi, sekumpulannya) yang akan melakukan penggabungan serupa ke tabel besar, akan bermanfaat bagi Anda untuk menyiapkan data terlebih dahulu dengan menggabungkan tabel sebelumnya dan mempertahankannya ke dalam tabel. Sekarang laporan dapat dijalankan terhadap tabel pra-tahapan itu dan menghindari gabungan besar.

Anda tidak selalu dapat menggunakan teknik ini, tetapi jika Anda bisa, Anda akan menyadari bahwa ini adalah cara terbaik untuk menghemat sumber daya server.

Perhatikan bahwa banyak pengembang mengatasi masalah penggabungan ini dengan berkonsentrasi pada kueri itu sendiri dan membuat hanya-lihat di sekitar penggabungan sehingga mereka tidak perlu mengetikkan kondisi penggabungan berulang kali. Namun masalah dengan pendekatan ini adalah kueri masih berjalan untuk setiap laporan yang membutuhkannya. Dengan menyiapkan data sebelumnya, Anda menjalankan gabungan hanya sekali (misalnya, 10 menit sebelum laporan) dan semua orang menghindari gabungan besar. Saya tidak dapat memberi tahu Anda betapa saya menyukai teknik ini; di sebagian besar lingkungan, ada tabel populer yang digabungkan sepanjang waktu, jadi tidak ada alasan mengapa tabel tersebut tidak dapat dipra-stage.

7. Hapus dan perbarui secara berkelompok

Inilah teknik mudah lain yang sering diabaikan. Menghapus atau memperbarui data dalam jumlah besar dari tabel besar bisa menjadi mimpi buruk jika Anda tidak melakukannya dengan benar. Masalahnya adalah bahwa kedua pernyataan ini dijalankan sebagai satu transaksi, dan jika Anda perlu menghentikannya atau jika sesuatu terjadi pada sistem saat mereka bekerja, sistem harus memutar kembali seluruh transaksi. Ini bisa memakan waktu lama. Operasi ini juga dapat memblokir transaksi lain selama durasinya, yang pada dasarnya menghambat sistem.

Solusinya adalah melakukan penghapusan atau pembaruan dalam batch yang lebih kecil. Ini menyelesaikan masalah Anda dengan beberapa cara. Pertama, jika transaksi terbunuh karena alasan apa pun, hanya memiliki sejumlah kecil baris untuk diputar kembali, sehingga database kembali online lebih cepat. Kedua, sementara batch yang lebih kecil melakukan ke disk, yang lain dapat menyelinap dan melakukan beberapa pekerjaan, sehingga konkurensi sangat ditingkatkan.

Sejalan dengan itu, banyak pengembang yang terjebak di kepala mereka bahwa operasi penghapusan dan pembaruan ini harus diselesaikan pada hari yang sama. Itu tidak selalu benar, terutama jika Anda mengarsipkan. Anda dapat memperluas operasi itu selama Anda membutuhkannya, dan kelompok yang lebih kecil membantu mencapainya. Jika Anda dapat memerlukan waktu lebih lama untuk melakukan operasi intensif ini, gunakan waktu ekstra dan jangan membuat sistem Anda down.

8. Gunakan tabel temp untuk meningkatkan kinerja kursor

Saya harap kita semua tahu sekarang bahwa yang terbaik adalah menjauh dari kursor jika memungkinkan. Kursor tidak hanya mengalami masalah kecepatan, yang dengan sendirinya dapat menjadi masalah pada banyak operasi, tetapi juga dapat menyebabkan operasi Anda memblokir operasi lain lebih lama dari yang diperlukan. Ini sangat mengurangi konkurensi di sistem Anda.

Namun, Anda tidak selalu dapat menghindari penggunaan kursor, dan ketika waktu tersebut muncul, Anda mungkin dapat menghindari masalah kinerja yang disebabkan kursor dengan melakukan operasi kursor pada tabel temp. Ambil, misalnya, kursor yang melewati tabel dan memperbarui beberapa kolom berdasarkan beberapa hasil perbandingan. Alih-alih melakukan perbandingan terhadap tabel langsung, Anda mungkin dapat memasukkan data tersebut ke tabel temp dan melakukan perbandingan terhadapnya. Maka Anda memiliki satu UPDATEpernyataan melawan tabel langsung yang jauh lebih kecil dan memegang kunci hanya untuk waktu yang singkat.

Menghentikan modifikasi data Anda seperti ini dapat sangat meningkatkan konkurensi. Saya akan menyelesaikannya dengan mengatakan bahwa Anda hampir tidak perlu menggunakan kursor. Hampir selalu ada solusi berbasis set; Anda perlu belajar untuk melihatnya.

9. Jangan membuat tampilan bersarang

Tampilan bisa jadi nyaman, tetapi Anda harus berhati-hati saat menggunakannya. Meskipun tampilan dapat membantu mengaburkan kueri besar dari pengguna dan untuk menstandarkan akses data, Anda dapat dengan mudah menemukan diri Anda dalam situasi di mana Anda memiliki tampilan yang memanggil tampilan yang memanggil tampilan yang memanggil tampilan. Ini disebut tampilan bertingkat , dan dapat menyebabkan masalah performa yang parah, terutama dalam dua cara:

  • Pertama, kemungkinan besar Anda akan mendapatkan lebih banyak data yang kembali daripada yang Anda butuhkan.
  • Kedua, pengoptimal kueri akan menyerah dan mengembalikan rencana kueri yang buruk.

Saya pernah memiliki klien yang menyukai tampilan bersarang. Klien memiliki satu tampilan yang digunakan untuk hampir semua hal karena memiliki dua sambungan penting. Masalahnya adalah view mengembalikan kolom dengan dokumen 2MB di dalamnya. Beberapa dokumen bahkan lebih besar. Klien mendorong setidaknya 2MB ekstra di seluruh jaringan untuk setiap baris di hampir setiap kueri yang dijalankan. Secara alami, kinerja kueri sangat buruk.

Dan tidak ada kueri yang benar-benar menggunakan kolom itu! Tentu saja, kolom itu terkubur dalam tujuh tampilan, jadi bahkan sulit untuk menemukannya. Saat saya menghapus kolom dokumen dari tampilan, waktu untuk kueri terbesar berubah dari 2,5 jam menjadi 10 menit. Ketika saya akhirnya mengungkap tampilan bersarang, yang memiliki beberapa gabungan dan kolom yang tidak perlu, dan menulis kueri biasa, waktu untuk kueri yang sama turun menjadi sub-detik.