Analisis Kecepatan Proses Insert Query Pada Tabel Terpartisi Di Database Engine Oracle Dan SQL Server
Widhya Wijaksono Fakultas Teknologi Industri, Jurusan Teknik Informatika Universitas Gunadarma
[email protected]
ABSTRAK Proses insert query adalah proses dimana memasukkan data kedalam tabel. Jika tabel tersebut memiliki partisi, maka kecepatannya menurun karena data tersebut sebelum dimasukkan kedalam tabel, data tersebut dipecah-pecah terlebih dahulu sesuai dengan partisinya. Kecepatan proses insert query pada tabel terpartisi dapat menarik perhatian DBA (Database Administrator) dalam memilih Database Engine. Salah satu implementasi tabel terpartisi yaitu pada database OLTP (Online Transaction Processing) dengan data dalam jumlah jutaaan atau lebih. OLTP adalah proses transaksi secara Online, bahkan real time seperti ATM Perbankan. Penulisan ini menggambarkan bagaimana cara mendapatkan kecepatan proses insert query pada tabel terpartisi dan menganalisis proses partisi pada Database Engine Oracle dan SQL Server.
Kata Kunci : Analisis, Oracle, SQL Server, Kecepatan, Partisi, Insert, Database.
1.
PENDAHULUAN Perkembangan teknologi database dari tahun ke tahun semakin stabil, cepat, minimum
downtime, dan banyak fitur-fitur baru di dalamnya. Perkembangan ini dituntut oleh banyak klien kelas Corporate dimana kebutuhan database aktif selama 24 jam, terjamin keamanan dan kecepatan datanya. Pada database yang memiliki data dalam jumlah jutaan bahkan milyaran, sebuah query yang dieksekusi, memakan waktu yang lama. Database Engine besar, seperti Oracle dan SQL Server, saling berlomba untuk memenuhi kebutuhan ini. Banyak perubahan dan 1
perkembangan yang diraih, namun ada juga kegagalan dan ketidakstabilan pada masingmasing Database Engine. Database yang bekerja 24 jam dan yang memiliki jumlah transaksi yang besar pada saat yang bersamaan, menjadi sangat kritis. Karena jumlah data dalam suatu perusahaan setiap harinya pasti mengalami pertumbuhan yang cukup besar. Jika suatu table yang mempunyai satu juta rows, yang mana ketika kita ingin menarik salah satu data, dan jika table tersebut tidak terindex maka performa akan menurun, tetapi jika table tersebut diindex, maka performa akan membaik. Jika table tersebut bertambah besar menjadi sepuluh juta rows atau bahkan satu milyar rows, maka table yang terindex pun juga akan menurun performanya, oleh karena itu dibuatlah table yang terpartisi untuk meningkatkan kembali performa yang menurun tadi. Dari permasalahan ini, penulis ingin mengetahui seberapa cepat proses insert query pada tabel yang terpartisi. Maksud dari proses insert query disini adalah proses pemasukan data pada tabel, baik yang kosong maupun berisi data.
2.
TINJAUAN PUSTAKA
2.1 Partisi di Oracle Partisi dapat mempercepat pencarian data. Oracle memiliki 4 macam partisi, yaitu range partitioning, list partitioning, hash partitioning dan composite partitioning. Range partitioning memetakan data ke dalam partisi berdasarkan rentang nilai kunci partisi yang membangun untuk setiap partisi. Ini adalah jenis yang paling umum partisi dan sering digunakan dengan tanggal. List Partitioning memungkinkan secara eksplisit mengontrol bagaimana baris peta ke partisi. Caranya dengan menentukan daftar nilai diskrit untuk kunci partisi dalam deskripsi untuk setiap partisi. Ini berbeda dengan partisi range, dimana rentang nilai dikaitkan dengan partisi dan dari partisi hash, di mana fungsi hash mengontrol pemetaan baris-ke-partisi. Keuntungan dari list partition adalah dapat mengelompokkan dan mengatur unordered dan set data yang tidak terkait dengan cara alami. Hash partitioning memungkinkan data dipartisi dengan mudah dan tidak meminjamkan dirinya untuk range partition atau list partition. Hal ini dilakukan dengan sintaks yang sederhana dan mudah untuk diimplementasikan. Sedangkan composite partitioning mempartisi data menggunakan metode jangkauan, dan dalam setiap partisi, subpartitions dengan menggunakan hash atau metode daftar. Composite range-hash partitioning menyediakan pengelolaan peningkatan 2
partisi jangkauan dan penempatan data, striping, dan keuntungan paralelisme partisi hash. Composite range-list partitioning menyediakan pengelolaan partisi jangkauan dan kontrol eksplisit daftar partisi untuk subpartitions.
Gambar 2.1 List Partitioning, Range Partitioning, Hash Partitioning dan Composite Partitioning
2.2 Partisi di SQL Server Tabel Partisi menyimpan data dalam cluster index tersegmentasi dan menggunakan tabel untuk mengakses data. Tabel partisi mengurangi ukuran dari yang tercluster dan yg tidak tercluster b-tree index, yang memberikan manfaat sebagai berikut :
Operasi insert dan update juga harus dilakukan pada operasi insert dan update halaman indeks. Ketika sebuah tabel dipartisi, hanya indeks partisi yang dapat diperbarui.
Pemeliharaan indeks dapat menjadi operasi yang mahal. Indeks sebuah partisi menjadi signifikan lebih kecil dan mengurangi biaya kinerja dari mengindeks
3
ulang atau defragmenting indeks. Namun, partisi tidak dapat diindeks secara offline, yang mana masih menjadi kelemahan utama.
Melakukan backup dari bagian tabel menggunakan filegroups backup dapat memudahkan proses backup.
Indeks b-tree sedikit lebih kecil - mungkin tingkat menengah atau dua tingkat lebih kecil - tapi keuntungan kinerja mungkin tidak akan terlihat.
Sebuah tabel partisi bisa mensegmen data dengan satu klausa WHERE filter dan mungkin meningkatkan seleksi filtering predikat yang lain sehingga indeks memungkinkan digunakan ketika tanpa partisi.
Dengan data dipartisi, proses scan mungkin hanya perlu mengambil sebuah partisi, bukan seluruh tabel, yang dapat menghasilkan perbedaan performa yang besar dan dapat menghindari menggunakan memori berlebih.
3.
PEMBAHASAN
Pada penelitian kali ini penulis menggunakan perangkat computer yang menggunakan virtual machine dengan informasi sebagai berikut :
Informasi VMware
Processor (CPU) : Intel Core 2 Duo CPU T6400 2.0 GHz
Memori (RAM)
: 1 GB
Hardisk
: 40 GB
Informasi Sistem Operasi
Sistem Operasi
: Windows server 2008
Arsitektur
: x86 (32-bit)
Program Aplikasi yang berhubungan dengan implementasi:
Oracle 11g Enterprise Edition for Windows 32-bit
SQL Server 2008 Enterprise Edition for Windows 32-bit
Informasi lainnya:
.NET Framework 4.0 untuk kebutuhan utama SQL Server 2008 Enterprise
SQL Server Management Studio Express untuk kebutuhan manajemen SQL Server 2008 Enterprise
4
3.1 Metode Penyelesaian Permasalahan Partisi dalam database meningkatkan kinerja, pengelolaan, dan ketersediaan berbagai macam aplikasi dan membantu mengurangi total biaya kepemilikan untuk menyimpan sejumlah besar data. Partisi memungkinkan tabel, indeks, atau table indeks-terorganisir yang akan dibagi ke beberapa bagian, di mana setiap bagian dari suatu objek database disebut partisi. Setiap partisi memiliki nama sendiri, dan secara opsional mungkin memiliki karakteristik penyimpanan sendiri. Berikut ini alur analisis proses insert query yang digambarkan sebagai berikut:
Buat Partisi
Gambar 3.1 Analisis Proses Insert Query pada Tabel Terpartisi Metode analisis yang dimulai dari pembuatan tabel insertdata untuk menampung 1000 data, pembuatan tabel simpanwaktu untuk menampung waktu sebelum proses insert dan sesudah proses insert, pembuatan partisi untuk menguji kecepatannya, dan memasukkan 1000 data pada tabel insertdata sebagai medianya. Sebelum insert 1000 data, waktu disimpan terlebih dahulu kedalam tabel simpanwaktu sebagai tanda dimulainya insert 1000 data tersebut. Kemudian sesudah insert 1000 data, waktu disimpan kembali kedalam tabel simpanwaktu, sehingga didapat waktu yang dibutuhkan dalam penyimpanan 1000 data. Waktu yang dibutuhkan dalam insert 1000 data didapat dengan mengurangi waktu sesudah insert dan waktu sebelum insert data. Alur algoritma partition juga dijabarkan agar dapat mengetahui penyebab cepat lambatnya pemasukan data. Dari insert query, kita dapat menjabarkan alur-alur partition dari sebelum masuk ke partisi dan tabel sampai ditulis di 5
partisi dan di tabel. Metode ini merupakan metode sederhana dan dapat dimengerti oleh orang umum sehingga jika ada orang yang ingin mengembangkan kembali tulisan ini, dapat melakukannya dengan mudah.
3.2 Membuat Table Pada tahap ini penulis akan membuat dua buah table, yang mana table pertama akan digunakan untuk memasukkan data, sedangkan table kedua akan digunakan untuk mencatat waktu sebelum dan sesudah memasukkan data, yang mana nantinya akan diolah untuk mendapatkan waktu yang diperlukan dalam proses insert 1000 data. Selanjutnya pada langkah ini penulis juga akan membuat partisi pada table pertama.
3.3 Memasukkan Data dan Mencatat Waktu Pada tahap ini data-data akan dimasukkan ke dalam table yang sudah terpartisi. Dan pada waktu yang bersamaan pula akan dicatat waktu yang dibutuhkan database engine untuk memasukkan keseluruhan data yang ada. Langkah ini akan di ulang sebanyak sepuluh kali agar penelitian ini dapat menghasilkan hasil yang valid.
3.4 Melakukan Perhitungan TPS(Transaction Per Second) Setelah seluruh data selesai di masukkan dan dicatat waktunya, langkah selanjutnya adalah melakukan perhitungan untuk memperoleh TPS. Perhitungan TPS ini dilakukan di setiap pengulangan proses pemasukan data ke dalam table. Setelah seluruh TPS didapatkan, langkah selanjutnya adalah mencari rata-rata dari TPS tersebut dengan cara menjumlahkan seluruh TPS lalu dibagi dengan jumlah TPS yang ada.
3.5 Perbandingan Oracle dan SQL Server Setelah TPS rata-rata di dapat, langkah selanjutnya adalah membandingkan kedua database engine yang ada, mana yang lebih cepat dalam proses insert query pada table yang terpartisi. Berikut ini hasil perbandingan kedua Database Engine dalam hasil analisis proses insert query pada tabel terpartisi.
6
Tabel 3.1 Perbandingan Kecepatan Proses Insert Query pada Tabel Terpartisi No Keterangan
Oracle
SQL Server
1
263 tps
1091 tps
Kecepatan rata-rata insert query pada tabel terpartisi
Dari table diatas dapat terlihat bahwa kecepatan proses insert query pada tabel terpartisi di database engine sql server untuk 1000 data lebih cepat dibandingkan dengan oracle. Berikut ini perbandingan Algoritma pada Oracle dan SQL Server. Tabel 3.2 Perbandingan Algoritma No Oracle
SQL Server
1
Menggunakan partition key untuk menentukan bagaimana data di partisi
Menggunakan partition function untuk menentukan bagaimana data di partisi
2
Menggunakan tablespace yang memberikan partisi pada filegroups
Menggunakan partition scheme untuk memberikan parisi pada filegroups
3
Menampung data pada disk
Menampung data pada batch processing
4
Langkah pertama memparsing data
Langkah pertama menampung data pada batch processing
5
Me-load table yang dituju terlebih Langsung menentukan lokasi dimana dahulu, setelah itu baru menentukan data akan dimasukkan berdasarkan lokasi dimana data akan dimasukkan partisi yang telah dibuat berdasarkan partition key yang ada
6
Data dimasukkan ke dalam table yang telah di partisi
Data dimasukkan ke dalam table yang telah di partisi
3.6 Analasis Partitioning Setelah didapatkan database engine mana yang lebih cepat, penulis akan mencari penyebab yang membuat database engine yang satu dapat melakukan proses insert query lebih cepat dan penyebab database engine yang satunya lagi lebih lambat dalam melakukan proses insert query.
7
3.6.1 Analisis Partitioning di Oracle Berikut ini potongan hasil trace (pencatatan proses internal) dari eksekusi yang menunjukkan proses dalam melakukan insert query pada tabel terpartisi. Berikut ini hasilnya:
Gambar 3.2 Potongan Hasil Trace di Oracle Untuk mempermudah dalam membaca hasil tracing diatas, penulis menggunakan fungsi TKPROF, berikut ini hasilnya :
Gambar 3.3 Potongan Hasil Interpreter TKPROF Pada potongan hasil diatas dapat terlihat bahwa hal pertama yang dilakukan oracle adalah memparsing data. Kemudian saat perintah insert di eksekusi, maka data disimpan terlebih dahulu ke dalam disk. Setelah itu barulah oracle membuka table yang dituju untuk memasukkan datanya.
3.6.2 Analisis Partitioning pada SQL Server Berikut ini potongan hasil trace (pencatatan proses internal) dari eksekusi yang menunjukkan proses dalam melakukan insert query pada tabel terpartisi. Berikut ini hasilnya: 8
Gambar 3.4 Hasil Trace di SQL Server Berdasarkan hasil tracing di sql server dengan menggunakan sql profiler, dapat dilihat bahwa setiap data yang dimasukkan ditampung terlebih dahulu di dalam batch processing, yang mana batch tersebut akan di update setiap saat. Kemudian setelah data di tampung pada batch, data di baca terlebih dahulu untuk menentukan lokasi data tersebut akan di tuliskan, dan setelah di ketahui lokasi penulisannya, data tersebut langsung dimasukkan ke dalam tabel sesuai dengan partisi yang telah dibuat.
4.
PENUTUP
4.1 Kesimpulan Dari hasil analisa dan implementasi, penulis menyimpulkan:
Kecepatan proses insert query pada tabel terpartisi di Database Engine Oracle lebih lambat daripada kecepatan proses insert query pada tabel terpartisi di Database SQL Server, dimana Oracle berkecepatan sekitar 260 tps, sedangkan SQL Server berkecepatan sekitar 1000 tps.
Pada analisis algoritmanya, penyebab lambatnya Oracle adalah proses parsing data dan Oracle menampung data terlebih dahulu pada disk, sedangkan SQL Server tidak melakukan parsing data dan SQL Server menggunakan partition function untuk menentukan bagaimana data dipartisi.
9
4.2 Saran Dari penelitian ini, penulis menyarankan untuk:
Menghitung tablespace dan index karena penelitian ini mengabaikan ukuran tablespace dan index di masing-masing Database Engine.
Menggunakan jumlah data yang lebih besar dari yang penulis gunakan pada penelitian ini.
Melakukan pengembangan terhadap penulisan ini.
DAFTAR PUSTAKA 1. Nielsen, Paul., Mike White, and Uttam Parui. Microsoft SQL Server 2008 Bible, Wiley Publishing, Inc., 2009
2. Niemiec, Richard, Oracle Database 10g Performance Tuning Tips & Techniques, McGraw-Hill Professional, 2007
3. Database Model URL : http://en.wikipedia.org/wiki/Database_model 3 Juni 2012, 11.30 AM
4. dbforum URL : http://www.dbforums.com 3 Juni 2012, 12.30 PM
5. Partitioned Tables and Indexes URL : http://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm 3 Juni 2012, 01.30 PM
6. Partitioned Table and Index Strategies Using SQL Server 2008 URL : http://technet.microsoft.com/en-us/library/dd578580%28v=sql.100%29.aspx 3 Juni 2012, 05.30 PM
10
7. Partitioning Enhancements in Oracle Database 11g Release 1 URL : http://www.oracle-base.com/articles/11g/partitioning-enhancements-11gr1.php 5 Juni 2012, 01.20 PM
8. Partitioning in SQL Server 2008 URL : http://www.sqlservercentral.com/articles/partition/64740/ 5 Juni 2012, 02.20 PM
9. Spatial_wp10_bestprac URL http://www.google.co.id/url?sa=t&rct=j&q=oracle%20partitioning%20range%20struc ture&source=web&cd=1&ved=0CEYQFjAA&url=http%3A%2F%2Fdownload.oracl e.com%2Fotndocs%2Fproducts%2Fspatial%2Fpdf%2Fspatial_wp10_bestprac.pdf&ei =wR4uUKiHLYu0rAf14oHQCg&usg=AFQjCNHwNAckmZCZh2YXBbG_NWklQJ 1P8Q&cad=rja 5 Juni 2012, 03.20 PM
10. SQL Server Central URL : http://www.sqlservercentral.com/ 5 Juni 2012, 04.20 PM
11
