PERBANDINGAN OPTIMASI QUERY DENGAN VIEW DAN INDEXED VIEW

Jurnal Ilmu Komputer dan Sistem Informasi

PERBANDINGAN OPTIMASI QUERY DENGAN VIEW DAN INDEXED VIEW Kristina 1) Wasino 2) Tony 3) 1) 2)

Program Studi Sistem Informasi Universitas Tarumanagara Jl. Letjen S. Parman No. 1, Jakarta 11440 Indonesia

email : 1) [email protected] , 2) [email protected],

3)

[email protected]

View dapat menjadi alat yang efektif untuk mempercepat dan menyederhanakan query kompleks. Bahkan, menggunakan view untuk beberapa tujuan penggunaan dapat membantu mengoptimalkan query. Namun, menggunakan view yang mengembalikan hasil dalam jumlah besar dan melibatkan operasi kompleks dapat memberikan kinerja yang buruk. Oleh karena itu, penggunaan view untuk proses yang lebih mengutamakan pada kinerja tidaklah efisien, namun berbeda apabila menggunakan materialized view (MV) [11]. Penggunaan MV untuk mempercepat pemrosesan query merupakan ide yang telah ada sejak lama [8]. Konsep MV pertama kali diperkenalkan pada Oracle Database 8i. Microsoft telah mendukung fitur ini pada SQL Server 2000. Pada SQL Server, MV disebut dengan indexed view karena MV dapat di-index dengan berbagai cara [4]. View dimaterialisasi dengan menciptakan unique clustered index pada view. MV sangatlah bermanfaat dalam meningkatkan kinerja query [7]. MV dapat memberikan peningkatan waktu pemrosesan query yang signifikan, terutama untuk agregasi terhadap tabel dalam jumlah besar. Oleh karena itu, pada penelitian ini akan dibahas pengoptimasian query dengan menggunakan indexed view sebagai upaya untuk meningkatkan kinerja dan waktu respon query terhadap basis data yang berskala besar. Dalam penelitian ini dilakukan sejumlah pengujian dengan membandingkan waktu respon query yang mereferensikan langsung pada view dan indexed view, untuk mengetahui seberapa besar manfaat kinerja yang dihasilkan query dengan menggunakan indexed view.

ABSTRACT Processing of complex queries involving join operation on large database will require long response time, particularly if the query returns large amounts of data. Therefore, it is necessary to do query optimization. View can be an effective tool to simplify a complex query. However, using view which returns large result sets can lead to bad performance. SQL Server has the ability to make a special kind of view called indexed view. Indexed view or also known as materialized view is a view that has a unique clustered index. When unique clustered index created on the view, the view results will be stored in the database. Query performance can be improved significantly by creating an indexed view which involves a complex process with a large amounts of data, such as aggregation and join. The research aims to prove performance benefits resulting from query optimization using indexed view. The experiments were conducted by testing and measuring response time for three types of query then compare the results. Queries that will be compared are query with view and query with indexed view. Experimental results proved that indexed view perform very well by improving response time when processing the query as compared to views.

Key words Query optimization, indexed view, materialized view

1. Pendahuluan Dalam basis data relasional sering dilakukan pengaksesan data terhadap beberapa tabel sekaligus. Proses pengaksesan dan pengolahan data yang berskala besar serta melibatkan banyak operasi join akan membutuhkan waktu yang cukup lama sehingga menyebabkan kinerja menjadi tidak efektif dan efisien. Optimasi query memberikan pemecahan untuk menangani masalah tersebut dengan cara menggabungkan sejumlah besar teknik – teknik dan strategi, yang meliputi transformasi logika dari query – query untuk mengoptimisasi akses dan penyimpanan data terutama pada basis data yang berskala besar.

2. Kajian Pustaka 2.1 Optimasi Query Suatu aktivitas dalam memilih strategi eksekusi yang efisien untuk memproses query disebut sebagai Optimasi query [1]. Optimasi query merupakan aspek penting dari query processing. Optimasi query bertujuan untuk memilih query yang meminimalkan penggunaan sumber daya.

113

Jurnal Ilmu Komputer dan Sistem Informasi

memberikan manfaat kinerja tambahan yang tidak dapat diperoleh dengan menggunakan index standar. Indexed view dapat meningkatkan kinerja query dalam cara – cara berikut [6]: 1. Agregasi dapat dipre-komputasi dan disimpan dalam index untuk meminimalkan komputasi tinggi selama eksekusi query. 2. Tabel dapat dipre-join dan data yang dihasilkan disimpan. 3. Kombinasi dari join atau agregasi dapat disimpan

2.2 Index Index adalah struktur data fisik yang terpisah yang memungkinkan query untuk mengakses satu atau lebih baris data dengan cepat [9]. Index dapat memberikan manfaat dalam meningkatkan kinerja query secara signifikan. Clustered index menentukan urutan fisik dari data yang berada dalam tabel [2]. Clustered index menyimpan baris-baris data dalam tabel berdasarkan pada nilai key. Sedangkan, nonclustered index tidak menyimpan data tabel. Nonclustered index menyimpan pointer terhadap data tabel sebagai bagian dari key index.

Agregasi dan join yang sering dilakukan merupakan kandidat yang tepat untuk indexed view. Sebuah query dapat menjadi kandidat dari indexed view jika memerlukan waktu yang signifikan dan jumlah data besar untuk mendapatkan hasil query dengan cepat. Indexed view akan bekerja sangat baik ketika data relatif statik atau jarang diperbaharui. Sedangkan lingkungan transaksional tidak cocok untuk indexed view. Beberapa aplikasi yang tepat untuk pengimplementasian indexed view yaitu : decision support workloads, data marts, data warehouses, basis data Online analytical processing (OLAP), data mining workloads [6].

2.3 Index View merupakan tabel virtual yang tidak harus ada dalam basis data, tetapi dapat dihasilkan berdasarkan permintaan dari pengguna tertentu pada saat yang diinginkan [1]. View adalah hasil dinamik dari satu atau lebih operasi relasional yang dilakukan pada tabel dasarnya untuk menghasilkan tabel yang lain. View bersifat dinamis, artinya jika tabel dasarnya mengalami perubahan, maka view akan langsung menunjukkan perubahan tersebut. Tujuan dari penggunaan view antara lain : 1. Menyediakan mekanisme keamanan yang fleksibel dan baik dengan menyembunyikan bagian basis data dari pengguna tertentu. 2. Memungkinkan pengguna untuk mengakses data dengan cara yang disesuaikan dengan kebutuhan, sehingga data yang sama dapat dilihat oleh pengguna yang berbeda dengan cara yang berbeda pada saat yang sama. 3. Menyederhanakan operasi yang rumit pada tabel dasar.

3. Metode Penelitian Pada penelitian ini, dilakukan pengujian dan pengukuran waktu respon terhadap tiga jenis query untuk kemudian dibandingkan waktu responnya. Jenis query yang akan dibandingkan adalah query dengan view dan query dengan indexed view.

3.1 Data Penelitian Pengujian terhadap sejumlah query dilakukan pada basis data AdventureWorksLT, menggunakan DBMS SQL Server 2008 Enterprise Edition. Serta menggunakan software Redgate SQL Data Generator, yang digunakan untuk melaksanakan proses generate data atau penambahan data, sebesar 1 juta baris data pada setiap tabel dalam basis data AdventureWorksLT. Proses generate ini berhasil menambahkan 1 juta baris data pada 5 buah tabel, ratusan baris data terhadap 4 buah tabel, dan 1 buah tabel yang gagal mengalami penambahan data. Basis data AdventureWorks yang semula berukuran 7,188MB setelah dilakukan proses generate data, mengalami penambahan ukuran menjadi 2,25GB.

View memberikan beberapa manfaat, seperti meningkatkan keamanan dengan membatasi akses terhadap data dan mengurangi kompleksitas query. Namun, di sisi lain view juga memiliki kelemahan, salah satunya ialah dapat menurunkan kinerja apabila pendefinisian dilakukan dengan query yang kompleks dan melibatkan banyak tabel. Tetapi, view memberikan manfaat dengan mengurangi kompleksitas dan meningkatkan kinerja ketika diciptakan sebagai Materialized View. Materialized view (MV) adalah view yang isinya dikomputasikan dan disimpan [10]. MV seperti cache, yaitu salinan data yang dapat diakses dengan cepat [5]. Microsoft telah mendukung fitur MV pada SQL Server 2000, dan disebut sebagai indexed view. View ini dimaterialiasi dengan menciptakan unique clustered index pada view yang ada. Setelah clustered index dibuat, nonclustered index juga dapat dibuat sebagai index tambahan. Menggunakan index untuk meningkatkan kinerja query bukanlah sebuah konsep baru, namun indexed view

3.2 Metode dan Teknik Indexed View Sebelum menciptakan indexed view, terdapat beberapa hal yang perlu diperhatikan. Berikut adalah pengaturan SET option yang harus dilakukan :

114

Jurnal Ilmu Komputer dan Sistem Informasi CREATE UNIQUE CLUSTERED vProduct (ProductID); CREATE NONCLUSTERED INDEX (ProductID);

Tabel 1 Pengaturan SET Option

SESSION SET Option ANSI_NULLS ANSI_PADDING ANSI_WARNING ARITHABORT CONCAT_NULL_YEILDS_NULL NUMERIC_ROUNDABORT QUOTED_IDENTIFIER

INDEX NCI_vP

UCI_vP ON

ON

vProduct

Pernyataan pertama merupakan perintah untuk membuat view bernama vProduct. Fungsi WITH SCHEMABINDING diperlukan untuk menjadikan view tersebut sebagai indexed view. Fungsi ini akan mengunci tabel dasar untuk mencegah terjadinya perubahan skema yang akan mempengaruhi view. Kolom – kolom output yang didefinisikan dengan ekspresi aritmatika atau ekspresi lainnya harus menggunakan klausa AS agar dapat direferensikan. Indexed view akan mematerialisasi fungsi agregasi ke dalam disk. Hal ini akan mengeliminasi kebutuhan untuk perhitungan fungsi agregasi selama eksekusi query. Pernyataan kedua merupakan sintaks untuk membuat clustered index. Setelah view dibuat, unique clustered index akan ditambahkan ke dalam view tersebut. Dengan ditambahkannya unique clustered index pada view, sistem basis data mengalokasikan penyimpanan untuk view tersebut. Pernyataan ketiga merupakan sintaks untuk membuat nonclustered index, yaitu index tambahan bernama NCI_vP pada kolom ProductID.

ON ON ON ON ON OFF ON

View harus memenuhi persyaratan berikut sebelum dapat dibuat clustered index : 1. Semua fungsi referensi (function yang didefinisikan oleh sistem dan user) yang digunakan oleh view harus deterministic. 2. View harus mengacu pada tabel dasar. 3. View dan tabel dasar harus berasal dari basis data yang sama. 4. View harus dibuat dengan option SCHEMABINDING. 5. User-defined function (UDF) yang direferensikan dalam view harus dibuat dengan option SCHEMABINDING. 6. Pernyataan SELECT dalam view tidak dapat berisi klausa dan option berikut: DISTINCT, UNION, COMPUTE, TOP, ORDER BY, MIN, MAX, COUNT, SUM, subquery, derived table, atau OUTER.

3.3 Prosedur Pengujian Jumlah query yang akan diujikan pada penelitian ini ialah 15 query yang masing-masing mereferensikan pada view dan indexed view. Lima belas query ini dibagi menjadi 3 jenis query, yaitu query join (QJ), query agregasi (QA), dan query join & agregasi (QAJ). Kemudian, 5 query dari setiap jenis query akan mengembalikan jumlah baris data yang berbeda, yang dibagi menjadi ratusan, ribuan, puluhan ribu, ratusan ribu, dan jutaan baris data. Pengujian ini dilakukan pada 2 komputer dengan spesifikasi yang berbeda. Berikut ini adalah spesifikasi dari kedua komputer. 1. Komputer 1

Jika terdapat pernyataan GROUP BY, pendefinisian view harus : 1. Mengandung COUNT_BIG(*). 2. Tidak mengandung HAVING, CUBE, ROLLUP, atau GROUPING(). Indexed view dibuat dengan melakukan 2 langkah proses berikut : 1. Membuat view menggunakan pernyataan CREATE VIEW dengan klausa WITH SCHEMABINDING. 2. Membuat unique clustered index yang sesuai.

Processor Memory Hardisk VGA OS

Unique clustered index dibuat pada kolom dari view untuk menciptakan indexed view. Setelah unique clustered index dibuat, sistem basis data mengalokasikan penyimpanan untuk view, kemudian dapat dibuat tambahan nonclustered index. Nonclustered index pada view dapat memberikan tambahan kinerja query.

2.

Komputer 2 Processor

Contoh query pembuatan indexed view :

Memory Hardisk VGA OS

CREATE VIEW vProduct WITH SCHEMABINDING AS SELECT p.ProductID, p.Name AS ProductName, SUM(p.ListPrice) AS Price, SUM(p.StandardCost) AS Cost, COUNT_BIG(*) AS TotalRows FROM SalesLT.Product p JOIN SalesLT.ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID JOIN SalesLT.ProductModel pm ON p.ProductModelID = pm.ProductModelID GROUP BY ProductID, p.Name

Intel® Core™ i3 CPU M380 @2.53GHz 2048MB RAM DDR3 500GB Intel® HD Graphics Windows 7 Ultimate 32-bit

Pentium® Dual-Core CPU @3.00GHz 2048MB RAM DDR3 250GB Intel® G41 Express Chipset Windows 7 Ultimate 32-bit

E5700

Setiap query akan diuji sebanyak 5 kali dan dan pada tiap pengujiannya, waktu respon query dicatat hingga eksekusi ke-5. Pembersihan cache dan buffer dilakukan di setiap awal pengujian. Dari hasil setiap pengujian, hanya waktu respon pertama dari setiap pengujian yang diambil dan dirata-ratakan. Sedangkan waktu eksekusi

115

Jurnal Ilmu Komputer dan Sistem Informasi

yang kedua dan seterusnya tidak digunakan. Hal ini dikarenakan waktu eksekusi pertama tidak terpengaruh pada query plan dan cache yang mungkin tersimpan, karena setiap sebelum dilakukan pengeksekusian dilakukan pembersihan cache dan buffer.

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15

4. Hasil Pengujian Berdasarkan hasil pengujian yang telah dilakukan terhadap 15 query yang masing – masing mereferensikan pada view dan indexed view, diperoleh perbedaan waktu respon terhadap setiap query. Berikut ini adalah tabel dan grafik yang menunjukkan perbedaan waktu respon tersebut. Tabel 2 Waktu Respon Query pada Komputer 1

Waktu Respon Rata-Rata Pengujian Query 20000 15000

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15

5000 0

Query

View

Indexed View

Gambar 1 Grafik Waktu Respon Rata-Rata Query

Berdasarkan hasil waktu respon rata-rata pengujian, dapat diketahui besarnya kinerja peningkatan waktu respon yang dihasilkan oleh indexed view. Pengukuran kinerja dilakukan menggunakan rasio, yaitu query dengan indexed view n kali lebih cepat dari query dengan view. Berikut adalah rumus rasio kinerja tersebut [3] :

Tabel 3 Waktu Respon Query pada Komputer 2

Query

10000

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15

Waktu Respon Rata – rata (ms) View Indexed View 117.6 56.2 19775.0 152.6 32186.2 53.0 9298.0 1949.6 27114.6 11642.8 11.6 7.4 8255.8 109.6 7906.2 42.6 8585.8 1164.0 11840.8 7008.4 18286.0 17.0 17979.8 19.8 27774.4 375.8 14561.6 1429.0 12432.0 7119.8

Waktu (ms)

Query

Waktu Respon Rata – rata (ms) View Indexed View 118.0 65.4 18559.1 139.4 24575.4 138.8 7519.1 1764.9 23016.8 11391.3 12.9 6.4 6602.0 114.2 6456.3 145.7 6705.4 1161.7 11023.8 7978.4 16615.2 57.0 16401.3 68.5 22142.3 356.4 13577.7 1362.4 11228.0 8063.3

Waktu Respon Rata – rata (ms) View Indexed View 118.4 74.5 17343.2 126.2 16964.6 224.6 5740.2 1580.2 18919 11139.8 14.2 5.4 4948.2 118.8 5006.4 248.8 4825 1159.4 10206.8 8948.4 14944.4 97 14822.8 117.2 16510.2 337 12593.8 1295.8 10024 9006.8

n = WEV

WEIV ...........(1)

Keterangan : n : Peningkatan Waktu Eksekusi WEV : Waktu Eksekusi Query dengan View WEIV : Waktu Eksekusi Query dengan Indexed View Berikut ini adalah tabel kinerja peningkatan waktu eksekusi oleh indexed view terhadap view.

Tabel 5 Kinerja Peningkatan Waktu Eksekusi oleh Indexed View Tabel 4 Waktu Respon Rata-Rata Query antara Komputer 1 dan Komputer 2

116

Jurnal Ilmu Komputer dan Sistem Informasi

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15

Jumlah baris data Ratusan Ribuan Puluhan Ribu Ratusan Ribu Jutaan Ratusan Ribuan Puluhan Ribu Ratusan Ribu Jutaan Ratusan Ribuan Puluhan Ribu Ratusan Ribu Jutaan

Jenis

Query Join

Query Agregasi

Query Agregasi & Join

Views”, diakses pada 27 Agustus http://technet.microsoft.com/enus/library/dd171921(v=SQL.100).aspx.

Peningkatan Waktu (n) 1.8 x 133.1 x 177.1 x 4.3 x 2x 2x 57.8 x 44.3 x 5.8 x 1.4 x 291.5 x 239.4 x 62.1 x 10 x 1.4 x

2012,

[7] Karde, P., Thakare, V.M., November 2010, “Selection of Materialized View Using Query Optimization in Database Management : An Efficient Methodology”, Internasional Journal of Database Management Systems,Vol.2, No.4, 116-130. [8] Larson, P.A., Yang, H.Z., 1985 “Computing Queries from Derived Relations”, Proceesing VLDB, Vol.11, 259-269. [9] Petkovic, Dusan., 2008, “Microsoft SQL Server 2008 : A Beginner’s Guide”, McGraw-Hill, USA. [10] Silberschatz, A., Henry, F. K., Sudarshan, S., 2011,” Database System Concepts”, McGraw-Hill, NY. [11] Zeis, C., Ruel, C., & Wessler, M., 2009, “Oracle 11g For Dummies”, Wiley Publishing Inc, Indianapolis, Ind.

Kristina, mahasiswi Program Studi Sistem Informasi Fakultas Teknologi Informasi Universitas Tarumanagara, Jakarta.

Dari hasil pengujian, diperoleh bahwa query dengan indexed view memiliki waktu eksekusi yang lebih cepat dibandingkan dengan view pada semua jenis query. Tabel 4 menunjukkan bahwa penggunaan indexed view dapat meningkatkan kinerja query dalam mengakses data hingga 200 kali lebih cepat dibandingkan dengan mengakses view. Peningkatan waktu terbesar terjadi pada query agregasi dan join.

5. Kesimpulan Berdasarkan hasil pengujian, dapat disimpulkan bahwa : 1. Indexed view terbukti meningkatkan kinerja query dalam mengakses data secara signifikan. 2. Indexed view bekerja meningkatkan kinerja secara optimal pada jenis query agregasi dan join (QAJ). 3. Indexed view terbukti lebih cepat dibandingkan dengan view dalam pemrosesan query dengan persentase peningkatan waktu yang mampu mencapai hingga 200 kali lebih cepat. 4. Indexed view tetap berkinerja lebih cepat pada perangkat dengan spesifikasi yang berbeda.

REFERENSI [1] Connolly, T.M., Carolyn, E.B., 2010, “Database Systems : A Practical Approach to Design, Implementation and Management”, Addison-Wesley, Longman Inc, USA. [2] Dewson, R., 2008, “Beginning SQL Server 2008 for Developers : From Novice to Professional “, Apress, USA. [3] Godse, A.P., Godse, D.A., 2006, “Computer Organization Architecture”, Technical Publications, India. [4] Goldstein, J., Larson, P., Mei 2001, “Optimizing Queries Using Materialized Views : A Practical, Scalable Solution”, Proceeding ACM SIGMOD, 331-342. [5] Gupta, A., Mumick, I.S., 1995, “Maintenance of Materialized Views: Problems, Techniques and Applications”, IEEE Quarterly Bulletin on Data Engineering.

[6] Hanson, E., Susan, P., 2008, “Improving Performance with SQL Server 2008 Indexed

117