Lima Cara Meningkatkan Kinerja SQL Anda
Artikel ini dikutip dari artikel MSDN "Lima Cara Meningkatkan Kinerja SQL" dan mengusulkan cara meningkatkan efisiensi pengoperasian aplikasi berbasis SQL Server. Untuk beberapa sistem aplikasi dengan lalu lintas tinggi, cara meningkatkan dan menyempurnakan instruksi SQL sangatlah penting dan merupakan titik terobosan yang baik.
*Artikel ini terutama memuat konten berikut (jika Anda tertarik, silakan langsung kunjungi URL berikut untuk membaca dokumen lengkap berbahasa Mandarin dan Inggris):
1. Kembalikan IDENTITAS dari INSERT
SELECT @@IDENTITY
2, tampilan tertanam dan tabel sementara
Tabel sementara – Tabel sementara di tempdb dapat menyebabkan kueri melakukan operasi I/O berat dan akses disk, dan tabel sementara dapat menghabiskan banyak sumber daya.
Tampilan Sebaris - Gunakan tampilan sebaris alih-alih tabel sementara. Tampilan sebaris hanyalah sebuah kueri yang dapat digabungkan dalam klausa FROM. Jika Anda hanya perlu menggabungkan data ke kueri lain, Anda bisa mencoba menggunakan tampilan sebaris untuk menghemat sumber daya.
3. Hindari LEFT JOIN dan NULL
LEFT JOIN sangat membutuhkan sumber daya karena berisi data yang cocok dengan data NULL (tidak ada). Dalam beberapa kasus, hal ini tidak dapat dihindari, namun biayanya bisa sangat tinggi. LEFT JOIN mengkonsumsi lebih banyak sumber daya daripada INNER JOIN, jadi jika Anda dapat menulis ulang kueri sehingga tidak menggunakan LEFT JOIN apa pun, Anda akan mendapatkan hadiah yang sangat bagus.
Salah satu teknik untuk mempercepat query yang menggunakan LEFT JOIN adalah dengan membuat tipe data TABLE, menyisipkan semua baris pada tabel pertama (tabel di sebelah kiri LEFT JOIN), dan kemudian memperbarui tipe data TABLE dengan nilai dari meja kedua. Teknik ini merupakan proses dua langkah, namun dapat menghemat banyak waktu dibandingkan dengan LEFT JOIN standar. Aturan yang baik adalah mencoba berbagai teknik berbeda dan mencatat waktu yang diperlukan untuk masing-masing teknik hingga Anda mendapatkan kueri yang berkinerja terbaik untuk aplikasi Anda.
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))
4, penggunaan produk Cartesian yang fleksibel
Saya akan menjelaskan secara rinci tentang teknik ini dan menganjurkan penggunaan produk Cartesian dalam beberapa kasus. Untuk beberapa alasan, produk Cartesian (CROSS JOIN) mendapat banyak kritik, dan pengembang sering kali diperingatkan untuk tidak menggunakannya sama sekali. Dalam banyak kasus, mereka menghabiskan terlalu banyak sumber daya untuk digunakan secara efisien. Namun seperti alat apa pun di SQL, alat ini dapat bermanfaat jika digunakan dengan benar.
Salah satu contoh kode yang patut diikuti:
-- Produk Cartesian mengembalikan semua pelanggan selama semua bulan. Perkalian Cartesian pada dasarnya mengalikan tabel pertama dengan tabel kedua, menghasilkan sekumpulan baris yang berisi jumlah baris pada tabel pertama dikalikan dengan jumlah baris pada tabel kedua. Oleh karena itu, produk Cartesian mengembalikan 12 (semua bulan) * 81 (semua pelanggan) = 972 baris ke tabel @tblFinal. Langkah terakhir adalah memperbarui tabel @tblFinal dengan total penjualan bulanan setiap pelanggan untuk rentang tanggal ini dan memilih kumpulan baris terakhir.
DEKLARASIKAN @tblMonths TABEL (sMonth VARCHAR(7))
DEKLARASIKAN @tblCustomers TABEL (CUSTOMERID CHAR(10),
Nama Perusahaan VARCHAR(50),
Nama Kontak VARCHAR(50))
DEKLARASIKAN @tblFinal TABEL (sBulan VARCHAR(7),
ID Pelanggan CHAR(10),
Nama Perusahaan VARCHAR(50),
Nama Kontak VARCHAR(50),
mSalesMONEY)
DEKLARASIKAN @dtStartDate DATETIME,
@dtEndDate TANGGAL WAKTU,
@dtDate TANGGAL WAKTU,
@i INTEGER
SET @dtEndDate = '5/5/1997'
SET @dtEndDate = TANGGALADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS
VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) SEBAGAI VARCHAR(4)) + ' 23:59:59' SEBAGAI DATETIME))
SET @dtStartDate = TANGGALADD(MM, -1 * 12, @dtEndDate)
-- Masukkan semua bulan ke dalam tabel pertama
SETEL @i = 0
SAAT (@i < 12)
MULAI
SET @dtDate = TANGGALADD(mm, -1 * @i, @dtEndDate)
MASUKKAN KE @tblMonths PILIH CAST(YEAR(@dtDate) SEBAGAI VARCHAR(4)) + '-' +
KASUS
KAPAN BULAN(@dtDate) < 10
LALU '0' + CAST(MONTH(@dtDate) SEBAGAI VARCHAR(2))
LAINNYA CAST(MONTH(@dtDate) SEBAGAI VARCHAR(2))
BERAKHIR SEBAGAI sBulan
SETEL @i = @i + 1
AKHIR
-- Masukkan semua klien yang memiliki penjualan selama periode tersebut ke dalam tabel "y".
MASUKKAN KE @tblCustomers
PILIH BERBEDA
c.ID Pelanggan,
c.Nama Perusahaan,
c.NamaKontak
DARI Pelanggan c
Pesanan INNER JOIN o ON c.CustomerID = o.CustomerID
DIMANA o.OrderDate ANTARA @dtStartDate DAN @dtEndDate
MASUKKAN KE @tblFinal
PILIH m.sBulan,
c.ID Pelanggan,
c.Nama Perusahaan,
c.NamaKontak,
0
DARI @tblMonths m LINTAS GABUNG @tblCustomers c
UPDATE @tblFinal SET
mSales = data saya.mSales
DARI @tblFinal f GABUNG DALAM
(
PILIH c.ID Pelanggan,
CAST(TAHUN(o.Tanggal Pemesanan) SEBAGAI VARCHAR(4)) + '-' +
KASUS KETIKA BULAN(o.Tanggal Pemesanan) < 10
LALU '0' + CAST(MONTH(o.OrderDate) SEBAGAI VARCHAR(2))
LAINNYA CAST(BULAN(o.OrderDate) SEBAGAI VARCHAR(2))
BERAKHIR SEBAGAI sBulan,
SUM(od.Quantity * od.UnitPrice) SEBAGAI mPenjualan
DARI Pelanggan c
Pesanan INNER JOIN o ON c.CustomerID = o.CustomerID
INNER JOIN [Rincian Pesanan] od ON o.OrderID = od.OrderID
DIMANA o.OrderDate ANTARA @dtStartDate DAN @dtEndDate
KELOMPOK OLEH
c.ID Pelanggan,
CAST(TAHUN(o.Tanggal Pemesanan) SEBAGAI VARCHAR(4)) + '-' +
KASUS KETIKA BULAN(o.Tanggal Pemesanan) < 10
LALU '0' + CAST(MONTH(o.OrderDate) SEBAGAI VARCHAR(2))
LAINNYA CAST(BULAN(o.OrderDate) SEBAGAI VARCHAR(2))
AKHIR
) data saya di f.CustomerID = mydata.CustomerID DAN f.sMonth =
data saya.sBulan
PILIH f.sBulan,
f.ID Pelanggan,
f.Nama Perusahaan,
f.NamaKontak,
f.mSales
DARI @tblFinal f
PESAN OLEH
f.Nama Perusahaan,
f.sBulan
5. Ambil bagian yang hilang dan ganti bagian yang hilang.
Berikut beberapa teknik umum lainnya yang dapat membantu meningkatkan efisiensi kueri SQL. Misalkan Anda ingin mengelompokkan semua tenaga penjualan berdasarkan wilayah dan subtotal penjualannya, namun Anda hanya ingin tenaga penjualan tersebut ditandai sebagai aktif dalam database. Anda dapat mengelompokkan tenaga penjualan berdasarkan wilayah dan menghilangkan mereka yang tidak aktif menggunakan klausa HAVING, atau Anda dapat melakukannya dalam klausa WHERE. Melakukan hal ini pada klausa WHERE akan mengurangi jumlah baris yang perlu dikelompokkan, sehingga lebih efisien dibandingkan melakukannya pada klausa HAVING. Pemfilteran berdasarkan kondisi baris dalam klausa HAVING memaksa kueri untuk mengelompokkan data yang akan dihapus dalam klausa WHERE.
Tip efisiensi lainnya adalah dengan menggunakan kata kunci DISTINCT untuk menemukan laporan terpisah untuk baris data daripada menggunakan klausa GROUP BY. Dalam hal ini, SQL menggunakan kata kunci DISTINCT lebih efisien. Harap gunakan GROUP BY hanya ketika Anda perlu menghitung fungsi agregat (SUM, COUNT, MAX, dll.). Selain itu, jika kueri Anda selalu mengembalikan baris unik dengan sendirinya, jangan gunakan kata kunci DISTINCT. Dalam hal ini, kata kunci DISTINCT hanya menambah overhead sistem.
－－－－－－－－－－－－－－－－－－－
URL berbahasa Mandarin:
http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
URL bahasa Inggris:
http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/