Beberapa hari yang lalu teman saya menelepon dan bercerita tentang SSIS package yang mengalami penurunan performance ketika ditambahkan Sort transformation ke dalam Data Flow Task yang dibuatnya. Well, hal tersebut memang dapat terjadi, dan beberapa literatur merekomendasikan untuk melakukan proses sorting data di database untuk data yang sangat besar, dan bukan di SSIS.

Yang mungkin akan menjadi pertanyaan Anda adalah: Sebetulnya apa yang terjadi ketika menggunakan Sort transformation? Kenapa performance-nya mengalami penurunan? Untuk menjawabnya, ada baiknya kita mengetahui arsitektur dan proses di belakang layar ketika SSIS bekerja.

Data flow engine, yang merupakan engine untuk melakukan proses ETL, menggunakan arsitektur yang disebut Buffer-Oriented Architecture yang melakukan proses load secara efisien dengan cara memanipulasi dataset di memory. Keuntungan dari pemrosesan di memory ini adalah agar kita tidak melakukan proses peng-copy-an dan penyimpanan data secara fisik pada setiap step transformasi data. Proses peng-copy-an secara fisik sering kali saya temui pada proses ETL di SQL Server 2000 dimana data disimpan sementara pada temporary table untuk kemudian ditransformasi supaya menghasilkan data yang diinginkan. Tentunya proses penyimpanan data sementara, lalu pengambilan data tadi untuk digunakan pada proses selanjutnya adalah hal yang membutuhkan waktu dan mengurangi performance. Akan tetapi hal ini dapat saya maklumi mengingat pada DTS di SQL Server 2000 transformasi yang disediakan lebih sederhana jika dibandingkan SSIS.

Kembali ke SSIS, data flow engine memanipulasi data ketika sedang ditransfer dari source ke destination. Ketika data mengalir melalui pipeline-nya, SSIS akan mencoba menggunakan kembali data dari buffer sebelumnya sebanyak mungkin ketika ada operasi tambahan yang harus dikerjakan. Bagaimana buffer digunakan dan digunakan kembali tergantung pada tipe transformasi yang digunakan di dalam pipeline.

Tipe transformasi yang pertama adalah Row Transformations, yang melakukan transformasi untuk memanipulasi data atau membuat field baru dengan menggunakan data yang tersedia pada row yang sedang ditransformasi. Contoh komponen SSIS yang melakukan row transformations adalah Derived Column, Data Conversion, Multicast, dan Lookup. Walaupun komponen-komponen tersebut menambahkan field baru pada row yang ditransformasi, row transformations tidak akan membuat row tambahan. Row transformations memiliki keuntungan yakni menggunakan kembali buffer yang telah ada dan tidak membutuhkan peng-copy-an data ke dalam buffer yang baru. Karena setiap row output memiliki hubungan 1 ke 1 dengan row input, row transformations dikenal sebagai synchronous transformation.

Tipe transformasi selanjutnya adalah Partially Blocking Transformations. Transformasi ini biasanya menggabungkan beberapa dataset input. Sebagai hasilnya, outputnya bisa jadi memiliki jumlah row yang sama, lebih besar, atau lebih sedikit dari jumlah row input. Contoh component yang merupakan partially blocking transformations di SSIS adalah Merge, Merge Join, dan Union All. Dengan partially blocking transformations, output hasil transformasi disimpan kedalam sebuah buffer baru dan sebuah thread baru kemungkinan akan ditambahkan ke dalam data flow. Karena jumlah row input kemungkinan tidak sama dengan jumlah row output, transformasi ini disebut asynchronous transformation.

Tipe transformasi yang terakhir adalah Blocking Transformations. Blocking transformations akan membaca dan mem-proses semua row input sebelum membuat row output. Dari semua tipe transformasi, transformasi ini adalah yang paling banyak bekerja dan dapat mempengaruhi resource yang tersedia. Contoh komponen SSIS yang termasuk di dalamnya adalah Aggregate dan Sort. Seperti partially blocking transformasions, blocking transformation juga bersifat asynchronous, dan ketika transfomasi-nya terjadi di data flow, sebuah buffer baru akan diciptakan untuk output-nya dan thread baru akan ditambahkan ke dalam data flow.

Jadi sekarang dapat kita pahami mengapa proses Sort yang dibuat oleh teman saya tadi menurunkan performance secara signifikan. Hal ini terjadi karena data flow engine SSIS mengumpulkan semua row input, lalu kemudian mengurutkannya sesuai dengan kriteria yang kita buat, baru setelah itu membuat row output. Ada banyak hal yang dapat kita lakukan untuk menghindari penggunaan component dengan tipe Blocking Transformastions yang membuat performance SSIS package yang telah kita buat tetap stabil, dan biasanya berbeda-beda tergantung case yang dihadapi. Salah satu yang biasa saya lakukan adalah melakukan sorting pada saat meng-extract data dari database.