♌️ 🗺️ 🧓 Kinerja Kolom Terhitung di SQL Server 👨🏾‍🚀 🙊 🙇

Terjemahan artikel disiapkan khusus untuk siswa kursus "Pengembang MS SQL Server" .

Kolom kalkulasi dapat menyebabkan masalah kinerja yang sulit didiagnosis. Artikel ini membahas sejumlah masalah dan beberapa solusi.

Kolom terhitung adalah cara mudah untuk menyematkan penghitungan dalam definisi tabel. Tetapi mereka dapat menyebabkan masalah kinerja, terutama karena ekspresi menjadi lebih kompleks, aplikasi menjadi lebih menuntut, dan volume data terus bertambah.

Kolom terhitung adalah kolom virtual yang nilainya dihitung berdasarkan nilai di kolom lain dalam tabel. Secara default, nilai yang dihitung tidak disimpan secara fisik, tetapi SQL Server menghitungnya di setiap permintaan kolom. Ini meningkatkan beban pada prosesor, tetapi mengurangi jumlah data yang perlu disimpan saat tabel berubah.

Seringkali kolom terhitung non-persisten membutuhkan CPU yang intensif, memperlambat kueri dan membekukan aplikasi. Untungnya, SQL Server menyediakan beberapa cara untuk meningkatkan kinerja kolom terhitung. Anda dapat membuat kolom terhitung yang ada, mengindeksnya, atau melakukan keduanya.

Untuk demonstrasi, saya membuat empat tabel serupa dan mengisinya dengan data identik dari database demo WideWorldImporters. Setiap tabel memiliki kolom terhitung yang sama, tetapi dua tabel tetap ada dan dua memiliki indeks. Hasilnya adalah opsi berikut:

Tabel Orders1adalah kolom terhitung yang belum disimpan.
Tabel Orders2adalah kolom kalkulasi yang persisten.
Tabel Orders3adalah kolom komputasi non-persisten dengan indeks.
Tabel Orders4adalah kolom komputasi persisten dengan indeks.

Ekspresi yang dihitung cukup sederhana dan set data sangat kecil. Namun, itu harus cukup untuk mendemonstrasikan prinsip kolom hitung yang persisten dan terindeks dan bagaimana hal ini membantu dalam memecahkan masalah kinerja.

Kolom terhitung belum disimpan

Mungkin dalam situasi Anda, Anda mungkin ingin kolom terhitung non-persisten untuk menghindari penyimpanan data, membuat indeks, atau untuk digunakan dengan kolom non-deterministik. Misalnya, SQL Server akan memperlakukan UDF skalar sebagai non-deterministik jika WITH SCHEMABINDING hilang dari definisi fungsi. Jika Anda mencoba membuat kolom terhitung yang persisten menggunakan fungsi ini, Anda akan mendapatkan pesan kesalahan bahwa kolom yang bertahan tidak dapat dibuat.

Namun, perlu diperhatikan bahwa fungsi kustom dapat menimbulkan masalah performanya sendiri. Jika tabel berisi kolom terhitung dengan fungsi, Mesin Kueri tidak akan menggunakan konkurensi (kecuali Anda menggunakan SQL Server 2019). Bahkan dalam situasi di mana kolom terhitung tidak ditentukan dalam kueri. Untuk kumpulan data yang besar, ini dapat berdampak besar pada performa. Fungsi juga dapat memperlambat eksekusi UPDATE dan memengaruhi cara pengoptimal menghitung biaya kueri pada kolom yang dihitung. Ini tidak berarti bahwa Anda tidak boleh menggunakan fungsi pada kolom yang dihitung, tetapi harus diperlakukan dengan hati-hati.

Apakah Anda menggunakan fungsi atau tidak, membuat kolom komputasi non-persisten cukup mudah. Instruksi selanjutnyaCREATE TABLEmendefinisikan tabel Orders1yang menyertakan kolom terhitung Cost.

USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Orders1;
GO
CREATE TABLE Orders1(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit));

INSERT INTO Orders1 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

Untuk menentukan kolom terhitung, tentukan namanya diikuti dengan kata kunci dan ekspresi AS. Dalam contoh kami, kami mengalikan Quantitydengan Pricedan mengurangi Profit. Setelah membuat tabel, kami mengisinya dengan INSERT menggunakan data dari Sales.InvoiceLinestabel database WideWorldImporters. Selanjutnya, kami menjalankan SELECT.

SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;

Kueri ini harus mengembalikan 22.973 baris, atau semua baris yang Anda miliki di database WideWorldImporters. Rencana eksekusi query ini ditunjukkan pada Gambar 1.

Gambar 1. Rencana eksekusi query terhadap tabel Orders1

Hal pertama yang perlu diperhatikan adalah Clustered Index Scan, yang bukan merupakan cara yang efisien untuk mendapatkan data. Tapi ini bukan satu-satunya masalah. Mari kita lihat jumlah pembacaan logis (Pembacaan Logis Aktual) di properti Clustered Index Scan (lihat Gambar 2).

Gambar 2. Logical membaca untuk menanyakan tabel Orders1

Jumlah pembacaan logis (dalam hal ini 1108) adalah jumlah halaman yang telah dibaca dari cache data. Tujuannya adalah mencoba mengurangi angka ini sebanyak mungkin. Karena itu, berguna untuk mengingatnya dan membandingkannya dengan opsi lain.

Jumlah pembacaan logika juga dapat diperoleh dengan menjalankan pernyataan SET STATISTICS IO ONsebelum menjalankan SELECT. Untuk melihat CPU dan total waktu - SET STATISTICS TIME ONatau melihat properti dari pernyataan SELECT di rencana eksekusi kueri.

Hal lain yang perlu diperhatikan adalah bahwa ada dua pernyataan Compute Scalar dalam rencana eksekusi. Yang pertama (yang di sebelah kanan) adalah perhitungan nilai kolom yang dihitung untuk setiap baris yang dikembalikan. Karena nilai kolom dihitung dengan cepat, Anda tidak dapat menghindari langkah ini dengan kolom terhitung non-persisten kecuali Anda membuat indeks pada kolom tersebut.

Dalam beberapa kasus, kolom terhitung yang tidak persisten menyediakan kinerja yang diperlukan tanpa menyimpannya atau menggunakan indeks. Ini tidak hanya menghemat ruang penyimpanan, tetapi juga menghindari overhead yang terkait dengan pembaruan nilai yang dihitung dalam tabel atau indeks. Namun, lebih sering daripada tidak, kolom terhitung yang tidak persisten menyebabkan masalah kinerja, dan kemudian Anda harus mulai mencari alternatif.

Kolom kalkulasi persisten

Salah satu teknik yang sering digunakan untuk memecahkan masalah kinerja adalah dengan mendefinisikan kolom yang dihitung sebagai persisten. Dengan pendekatan ini, ekspresi dihitung sebelumnya dan hasilnya disimpan bersama dengan data tabel lainnya.

Agar kolom menjadi persisten, ia harus deterministik, yaitu ekspresi harus selalu mengembalikan hasil yang sama untuk masukan yang sama. Misalnya, Anda tidak dapat menggunakan fungsi GETDATE dalam ekspresi kolom karena nilai yang dikembalikan selalu berubah.

Untuk membuat kolom terhitung tetap, Anda harus menambahkan kata kunci ke definisi kolom PERSISTED, seperti yang diperlihatkan dalam contoh berikut.

DROP TABLE IF EXISTS Orders2;
GO
CREATE TABLE Orders2(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED);

INSERT INTO Orders2 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

Tabel Orders2hampir identik dengan tabel Orders1, hanya saja kolomnya Costberisi kata kunci PERSISTED. SQL Server secara otomatis mengisi kolom ini ketika baris ditambahkan atau diubah. Tentu saja, ini berarti meja Orders2akan memakan lebih banyak tempat daripada meja Orders1. Ini dapat diverifikasi menggunakan prosedur tersimpan sp_spaceused.

sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO

Gambar 3 menunjukkan keluaran dari prosedur tersimpan ini. Ukuran data di tabel Orders1adalah 8.824 KB, dan di tabel Orders2- 12.936 KB. 4 112 KB lagi untuk menyimpan nilai yang dihitung.

Gambar 3. Perbandingan ukuran tabel Pesanan1 dan Pesanan2

Meskipun contoh ini didasarkan pada kumpulan data yang cukup kecil, Anda dapat melihat bagaimana jumlah data yang disimpan dapat berkembang pesat. Namun, ini bisa menjadi trade-off jika kinerja meningkat.

Untuk melihat perbedaan kinerja, lakukan langkah PILIH berikut.

SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;

Ini adalah SELECT yang sama yang saya gunakan untuk tabel Orders1 (kecuali untuk perubahan nama). Gambar 4 menunjukkan rencana eksekusi.

Gambar 4. Rencana eksekusi untuk kueri ke tabel Orders2.

Ini juga dimulai dengan Pemindaian Indeks Cluster. Tapi kali ini, hanya ada satu pernyataan Compute Scalar karena kolom yang dihitung tidak lagi perlu dihitung saat runtime. Secara umum, semakin sedikit langkah semakin baik. Meskipun ini tidak selalu terjadi.

Kueri kedua menghasilkan 1593 pembacaan logis, yang merupakan 485 lebih dari 1108 pembacaan untuk tabel pertama. Meskipun demikian, ini berjalan lebih cepat dari yang pertama. Meski hanya sekitar 100ms, dan terkadang jauh lebih sedikit. Waktu prosesor juga menurun, tetapi juga tidak banyak. Kemungkinan besar, perbedaannya akan jauh lebih besar pada volume yang lebih besar dan perhitungan yang lebih kompleks.

Indeks pada kolom terhitung yang tidak persisten

Teknik lain yang biasa digunakan untuk meningkatkan kinerja kolom yang dihitung adalah pengindeksan. Untuk dapat membuat indeks, kolom harus deterministik dan presisi, yang berarti ekspresi tidak dapat menggunakan tipe float dan real (jika kolom tidak persistent). Ada juga batasan pada tipe data lain serta parameter SET. Untuk daftar lengkap batasan, lihat dokumentasi SQL Server, Indexes on Computed Columns .

Anda dapat memeriksa apakah kolom terhitung yang tidak persisten cocok untuk diindeks melalui propertinya. Mari gunakan fungsi untuk melihat properti COLUMNPROPERTY. Properti IsDeterministic, IsIndexable, dan IsPrecise penting bagi kami.

DECLARE @id int = OBJECT_ID('dbo.Orders1')
SELECT
  COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic',
  COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable',
  COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';

Pernyataan SELECT harus menghasilkan 1 untuk setiap properti sehingga kolom terhitung dapat diindeks (lihat Gambar 5).

Gambar 5. Memverifikasi bahwa indeks

dapat dibuat Setelah verifikasi, Anda dapat membuat indeks nonclustered. Alih-alih mengubah tabel, Orders1saya membuat tabel ketiga ( Orders3) dan menyertakan indeks dalam definisi tabel.

DROP TABLE IF EXISTS Orders3;
GO
CREATE TABLE Orders3(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit),
  INDEX ix_cost3 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders3 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

Saya membuat indeks penutup non-cluster yang mencakup kolom dari ItemIDdan Costdari kueri SELECT. Setelah membuat dan mengisi tabel dan indeks, Anda dapat menjalankan pernyataan SELECT berikut yang mirip dengan contoh sebelumnya.

SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;

Gambar 6 menunjukkan rencana eksekusi untuk kueri ini, yang sekarang menggunakan indeks nonclustered ix_cost3 (Pencarian Indeks) alih-alih melakukan pemindaian indeks berkerumun.

Gambar 6. Rencana eksekusi untuk query pada tabel Orders3

Jika Anda melihat pada properti dari pernyataan Index Seek, Anda akan menemukan bahwa query tersebut sekarang hanya melakukan 92 pembacaan logis, dan pada properti pernyataan SELECT, Anda akan melihat bahwa CPU dan total waktu telah berkurang. Perbedaannya tidak signifikan, tetapi sekali lagi, ini adalah kumpulan data kecil.

Perlu juga dicatat bahwa hanya ada satu pernyataan Compute Scalar dalam rencana eksekusi, bukan dua seperti pada kueri pertama. Karena kolom terhitung diindeks, nilainya telah dihitung. Ini menghilangkan kebutuhan untuk menghitung nilai pada waktu proses, bahkan jika kolom belum ditentukan untuk tetap.

Indeks pada kolom yang disimpan

Anda juga dapat membuat indeks pada kolom terhitung yang Anda simpan. Meskipun ini akan menghasilkan data tambahan dan data indeks yang disimpan, ini dapat berguna dalam beberapa kasus. Misalnya, Anda bisa membuat indeks pada kolom terhitung persisten, meskipun menggunakan tipe data float atau real. Pendekatan ini juga dapat berguna saat bekerja dengan fungsi CLR, dan saat Anda tidak dapat memeriksa apakah fungsi tersebut deterministik.

Pernyataan berikut CREATE TABLEmembuat tabel Orders4. Definisi tabel mencakup kolom persisten Costdan indeks penutup tidak terkluster ix_cost4.

DROP TABLE IF EXISTS Orders4;
GO
CREATE TABLE Orders4(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED,
  INDEX ix_cost4 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders4 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

Setelah tabel dan indeks dibuat dan diisi, jalankan SELECT.

SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;

Gambar 7 menunjukkan rencana eksekusi. Seperti dalam contoh sebelumnya, kueri dimulai dengan pencarian indeks non-cluster (Pencarian Indeks).

Gambar 7. Rencana eksekusi untuk kueri pada tabel Orders4

Kueri ini juga melakukan hanya 92 pembacaan logis seperti yang sebelumnya, yang menghasilkan kinerja yang kira-kira sama. Perbedaan utama antara dua kolom yang dihitung, dan antara kolom yang diindeks dan yang tidak diindeks, adalah jumlah ruang yang digunakan. Mari kita periksa ini dengan menjalankan prosedur tersimpan sp_spaceused.

sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO
sp_spaceused 'Orders3';
GO
sp_spaceused 'Orders4';
GO

Hasilnya ditunjukkan pada Gambar 8. Seperti yang diharapkan, kolom komputasi yang disimpan memiliki lebih banyak data dan kolom yang diindeks memiliki lebih banyak indeks.

Gambar 8. Perbandingan penggunaan ruang untuk keempat tabel

Kemungkinan besar, Anda tidak perlu mengindeks kolom komputasi yang disimpan tanpa alasan yang jelas. Seperti pertanyaan terkait database lainnya, pilihan Anda harus didasarkan pada situasi spesifik Anda: kueri Anda dan sifat data Anda.

Bekerja dengan Kolom Terhitung di SQL Server

Kolom yang dihitung bukanlah kolom tabel biasa dan harus ditangani dengan hati-hati untuk menghindari penurunan kinerja. Sebagian besar masalah kinerja dapat diselesaikan melalui penyimpanan atau pengindeksan kolom, tetapi kedua pendekatan tersebut perlu mempertimbangkan ruang disk tambahan dan bagaimana data berubah. Saat data berubah, nilai kolom terhitung harus diperbarui dalam tabel atau indeks, atau keduanya, jika Anda mengindeks kolom terhitung yang bertahan. Anda hanya dapat memutuskan opsi mana yang terbaik untuk kasus spesifik Anda. Dan, kemungkinan besar, Anda harus menggunakan semua opsi.

Baca lebih banyak

Pengenalan ke Database Grafik SQL Server 2017

Kinerja Kolom Terhitung di SQL Server

Kolom terhitung belum disimpan

Kolom kalkulasi persisten

Indeks pada kolom terhitung yang tidak persisten

Indeks pada kolom yang disimpan

Bekerja dengan Kolom Terhitung di SQL Server

Baca lebih banyak

More articles: