Tag: sql

Tentang dia , kamu dan modularisasi pada sqlserver

Posted on by radityopw

Kamu siapa kamu ?  dia siapa dia ?

langsung saja gaiss.. jangan melihat judul itu, langsung saja yang ingin saya bahas adalah :

  1. Stored Procedure
  2. Function

di environtment sqlserver.  tentunya setelah belajar mengenai TSQL , kita harus move on kan ? hehehe.

alasan utama kenapa harus membuat stored procedure dan function adalah modularity yang berujung pada reuse dari stored procedure dan function tersebut.

Stored Procedure

Dengan stored procedure, kita dapat menyimpan sekumpulan TSQL yang kita buat ke dalam sebuah procedure yang tersimpan rapi di hati sqlserver. Dengan tersimpan, kita dapat memanggil manggilnya sesuka hati kita, kapan saja dia siap membantu kita…

sungguh setia bukan !

mari kita mulai dari yang paling simple saja, seperti :

Pada baris 9 dapat dilihat sintaks untuk membuat simple procedure, dimana terdiri dari nama procedure lalu block procedure dimulai dari begin dan diakhiri sampai end, yang mana dalam kasus yang ada di atas kita akan menghitung jumlah baris dari table person yang ada pada database adventure works 2012. Untuk menjalankan stored procedure ini kita dapat menggunakan perintah exec seperti pada baris 21.

Stored procedure dengan parameter

nah yang simple memang sesuai namanya, simple, bagaimana jika jumlah baris yang kita hitung tidak semua person, namun person dengan kriteria tertentu, untuk itu kita dapat menambahkan parameter seperti pada cuplikan berikut :

seperti pada kode baris 9 , kita dapat menambahkan varibael parameter @min_len_middle_name yang bertipe int dengan nilai default 3, sehingga untuk memanggilnya kita dapat menggunakan dengan berbagai cara seperti pada

  • baris 21 tanpa parameter yang berarti memberi nilai default yaitu 3
  • baris 23 dan 25 dengan memberi nilai pada parameter

Stored Procedure dengan nilai kembalian

kedua contoh stored procedure di atas menggunakan tabel sebagai tempat untuk melihat jalannya stored procedure, jika kita tidak menginginkan cara itu, kita dapat membuat stored procedure dengan mempunyai nilai kembalian seperti :

dari kode, saya membuat modifikasi, yaitu sekarang parameter tidak hanya 1 variabel, namun menjadi 3 variabel, dimana 2 variabel pertama adalah varibel input dan  varibel terakhir sebagai variabel output. Perbedaaannya adalah terletak pada keyword OUT yang ada di akhir variabel output.

untuk menjalankan stored procedure ini juga cukup mudah yaitu dengan membuat dulu variabel yang akan kita jadikan sebagai variabel output dan menggunakannya pada EXEC seperti pada 18,23,28.

cukup mudah bukan…

Function

Function itu mirip dengan storedprocedure , bedanya dia pasti punya nilai kembalian dan dijalankannya pada level query. Bagaimana maksudnya, maksudnya seperti :

dimana kita mengguakan pre defined function yaitu AVG untuk menghitung rata – rata pada kolom totaldue pada tabel salesorderheader.

function yang sudah pre defined disebut juga sebagai system functions dan untuk function buatan kita (user) disebut sebagai userd defined function atau disingkat sebagai UDF. contohnya adalah function berikut :

di contoh tersebut fungsi yang kita buat adalah mencari nilai minimum dari 2 variabel,  dan pada baris 22 , fungsi itu digunakan untuk mencari nilai minimum antara kolom freight dan subtotal pada tiap barisnya.

kegunaan lain dari function adalah untuk menghasilkan table , seperti pada contoh berikut :

di mana terlihat bahwa fungsi akan mengembalikan table yang merupakan hasil query. Perbedaannya adalah di sini fungsi tidak membutuhkan begin end melainkan langsung melakukan return table. Untuk memanggil fungsi jenis ini adalah dengan menganggap bahwa fungsi ini sebagai table, seperti pada baris 19.

so mudah bukan melakukan pembuatan fungsi..

happy coding all..

Gentle Introduction to TSQL

Posted on by radityopw

Di dunia per basis dataan kita mengenal sebuah bahasa pemrograman deklaratif [1] yaitu SQL. Berbeda sekali yang mungkin suka bergulat di dunia pemrograman ria.. yang mana bahasa yang lebih populer yaitu bahasa dengan paradigma pemrograman imperatif [2] seperti Java, c , c++, c# dan lainnya, walaupun ada juga bahasa yang sifatnya deklaratif misalnya saja haskel.

Sqlserver merupakan salah satu produk dari microsoft yang gencar sekali mengadopsi sql ini,selain linQ [3]. Namun dalam perkembangannya microsoft mungkin melihat dengan sql saja (dan sql spesific sqlserver) belum dapat menyelesaikan semua problem yang bisa saja baru dan kompleks, maka mereka menambahkan fitur baru di implementasi sql mereka yang mereka namakan TSQL (Transact-SQL) [4].

Dengan menggunakan TSQL, kita dapat melakukan pemrograman secara imperatif di environtment sqlserver yang tentunya hal ini akan memperkaya kemampuan sqlserver itu sendiri.

Memulai dengan santai

mari kita lihat cuplikan kode berikut :

Anda dapat mengetikkan cuplikan kode itu langsung di sqlserver management studio atau apapun yang menjadi sql client anda, anggap saja itu bagian dari query , mudah saja.

jika anda lihat pada baris 1, terdapat sebuah deklarasi variabel yang kita beri nama @vars yang akan bertipe float. Untuk tipe tipe lainnya dapat anda lihat di website microsoft [5].

lalu pada baris 3 akan terlihat operasi untuk memberi nilai pada variabel @vars dengan nilai 1, dan akhirnya kita akan tampilkan dalam baris ke 5.

cukup mudah bukan.. apalagi jika anda adalah seseorang dengan basic pemrograman imperatif yang cukup mumpuni pasti akan mudah mengikuti alur nya.

Mengambil nilai langsung dari data

pada cuplikan sebelumnya pada baris ke 3 kita memberi nilai adhoc yang merupakan contoh operasi pemberikan nilai, nah sekarang bagaimana jika kita menginginkan nilai dari variabel @vars berupa nilai yang kita ambil berasal dari data ?, cukup mudah rupanya mari kita lihat cuplikan berikut :

anda dapat lihat caranya benar benar mudah yaitu dengan yang saya lakukan di baris ke 6. Maka kita dapat lihat hasilnya dengan melakukan print di baris ke 8. Untuk melihat nilai variabel anda dapat menggunakan perintah print atau perintah select tergantung pada apa yang ingin anda hasilkan, jika ingin menghasilkan nilai berupa text saja ( akan tampil di tab message) maka anda cukup menggunakan print, namun jika ingin dijadikan sebuah tabel maka anda bisa menggunakan keyword select.

Percabangan itu mudah

Untuk melakukan percabangan caranya juga sangat mudah , seperti pada cuplikan berikut :

dimulai dari baris ke 9 untuk mengisi kondisi dari percabangan dan dilanjutkan dengan membuat begin – end pada baris 10 dan 12, hal ini juga sama dengan kondisi ‘else’ nya.

dalam banyak kasus saya memudahkan pengertian saya mengenai begin  – end dengan menganalogikannya dengan ‘{‘ ‘}’ pada pemrograman berbasis ‘{‘ seperti java , php .

contoh yang lain dapat kita lihat pada cuplikan berikut :

kali ini kita membuat 2 variabel, yang mana keduanya diambil dari data. pada baris ke 12 dan 16 dapat dilihat statement conditional yang menarik, kenapa menarik, karena mungkin tidak terbiasa bagi yang terbiasa menggunakan java atau php.

  1. dimana jika kita ingin melakukan conditional checking pada 2 kondisi dapat menggunakan keyword and
  2. untuk melakukan checking ‘!=’ kita dapat menggunakan ‘<>’
  3. untuk melakukan checking ‘==’ kita dapat menggunakan ‘=’

Perulangan yang juga mudah dilupakan

Pemrograman tanpa perulangan seperti sayur tanpa garam.. kurang mantap goyangannya..

mari kita lihat cuplikan berikut :

cara nya cukup mudah bukan ? , saya menggunakan metode paling simple dalam perulangan yaitu metode while do.

untuk baris 12 anda dapat melihat sebuah operasi baru yaitu cast. Dengan cast saya dapat mengubah sebuah tipe data variabel ke tipe data tertentu, dan yang saya lakukan adalah merubah tipe data variable tsb (int) ke dalam varchar agar dapat dilakukan proses concatenate dengan operator +. Sebenarnya kalo dilihat lagi mirip dan tidak mirip dengan java, di java kita dapat melakukan System.out.print(“Halo umur saya “+umur); , bedanya adalah di java terjadi automatic conversion antara tipe data tsb, dimana tipe data primiritf akan di ubah menjadi String, sedangkan jika tipe data referensional akan digunakan method toString().

Sepertinya contoh di atas terlalu simple, bagaiamana jika ingin melihat atau melakukan operasi yang langsung melibatkan data dari table ? mari kita lihat contoh yang lebih kompleks :

tujuan dari kode tsb adalah untuk menampilkan totaldue dari 10 transaksi terakhir.tahap pertama dapat anda lihat di baris 8 , saya menggunakan tipe data table dengan kolom yaitu nomor(int) dan id(int), yang bertujuan menyimpan nomor urut dan id dari salesorder. Untuk mengisi variabel ini, operasi insert into select digunakan, seperti pada baris 15, dan untuk mengisi nomor urut digunakan function row_number() yang sudah saya bahas pada postingan membuat query paging di sqlserver sebelumnya.  Dan proses utama terdapat di baris 25 – 29.

Perulangan menggunakan cursor

di Sqlserver kita dapat menggunakan cursor untuk melakukan perulangan seperti kode terakhir yang kita lihat. Contohnya sebagai berikut :

kita dapat mendefinisikan cursor seperti pada baris 7 dan 9, lalu dilanjutkan pada proses penggunaan cursor pada baris 16 yang sebelumnya kita buka pada baris 15. Proses tersebut akan memindahkan nilai query pada cursor dan disimpan pada sebuah variabel yang nanti akan kita tampilkan pada baris 20.

Beberapa catatan untuk penggunaan cursor ini adalah seringkali programmer lupa untuk menambahkan perintah yang sama pada baris 16 ke baris 22 (sebelum end) , hal itu dibutuhkan untuk cursor tetap berjalan.

catatan lainnya adalah jangan lupa untuk menutup dan melakukan deallocate pada variabel cursor tersebut.

mudah bukan TSQL.. , jadi gak perlu dikit dikit php , dikit dikit java …….

happy coding semua…

referensi

[1] https://en.wikipedia.org/wiki/Declarative_programming

[2] https://en.wikipedia.org/wiki/Imperative_programming

[3] https://msdn.microsoft.com/en-us/library/bb397926.aspx

[4] https://en.wikipedia.org/wiki/Transact-SQL

[5] https://msdn.microsoft.com/en-us/library/ms187752.aspx

Membuat query lebih simple dengan Common Table Expression

Posted on by radityopw

setelah melihat bagaimana menggunakan paging di sqlserver, sekarang saya akan mencoba mengulas sedikit mengenai kemampuan sqlserver yang lain yaitu Common Table Expression (CTE) [1].  Dengan CTE ini kita dapat membuat query kita lebih simple.

Contoh Sederhana CTE

mari ambil contoh salah satu query dari postingan saya sebelumnya, yaitu query

pada query itu kita akan menambahkan kolom nomor baris, dan selanjutnya akan dilanjutkan dengan query

yang akan melakukan seleksi baris yang akan kita ambil dan tampilkan.

nah sekarang kita coba dengan menggunakan cara yang sedikit berebeda , yaitu menggunakan CTE. Untuk melakukan itu, kita membutuhkan keyword with.

berikut langkah – langkah sederahananya :

  1. Buat dahulu CTE tersebut yang akan menggunakan nama alias tertentu
  2. Buat query yang memanggil alias tersebut sebagai sebuah table.

tahap 1 dimulai dari kita membuat CTE nya, seperti pada query :

setelah itu kita dapat menambahkan query dibawahnya dengan memanggil CTE tersebut, seperti pada query penuh seperti :

maka kita dapat menggunakan CTE sebagai alternatif dalam menuliskan paging tanpa nested query.

Menggunakan CTE untuk mengaggregasi

contoh pada web microsoft [1]  memberikan contoh yang baik untuk hal ini , misalnya saja kita mempunyai query untuk menghasilkan jumlah penjualan (transaksi) per Sales, maka dapat kita selesaikan dengan query

dan untuk menghitung rata – rata penjualan per sales maka akan kita dapatkan dengan query

 

yang mana sama dengan query

jika kita tidak menggunakan CTE.

Multiple CTE

misalkan saja berdasarkan contoh di atas, kita ingin melihat siapa saja yang sales yang diatas rata – rata maka dengan mudah kita mendefinisikan query sebagai berikut :

 

jauh lebih simple daripada kita menggunakan nested query berikut :

Hirarki dengan CTE

masalah ini sangat terkait dengan masalah yang terkenal di dunia design yaitu masalah rekursif, contohnya adalah design pada masalah employee-manager [2]. Dalam gambar kira2 bisa digambarkan seperti :

recursive-uml

untuk menggenerate masalah ini sebelumnya kita perlu membuat tabel untuk itu, karena di adventure works 2012 tidak ada permasalahan rekursif ini secara mudah untuk dilihat. Untuk itu kembali kita lihat contoh dari website microsoft [1]. Yaitu menggunakan query :

dengan ini kita akan membuat table myEmployees yang berisikan beberapa data dummy.

maka untuk menampilkan list employee beserta managernya kita bisa memanfaatkan query

di dalam query itu kita menggunakan (memanggil ) CTE di dalam deklarasi CTE itu sendiri

so asik bukan.. silakan untuk melihat – lihat contoh lain pada [1].

Referensi

[1]. https://msdn.microsoft.com/en-us/library/ms175972.aspx

[2] http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php

Paging di SQLSERVER

Posted on by radityopw

seandainya kita akan membuat halaman web untuk menampilkan data penjualan dari database adventurework 2012 [1] seperti pada query :

maka kit memerlukan solusi paging untuk itu. Karena jika query itu dijalankan maka akan menampilkan data sebanyak 121.317 baris.. tentu hal yang mustahil menampilkan data sebanyak itu dalam 1 halaman web saja.

pada sistem database yang lain misalnya mysql , postgresql atau sqlite maka akan dikenal keyword limit dan offset untuk melakukan paging [2], namun hal itu tidak berlaku di sqlserver.

untuk melakukan paging di sqlserver kita memerlukan beberapa langkah secara umum :

  1. Jadikan query menjadi nested query
  2. Berikan nomor pada masing – masing baris hasil nested query pada query baru kita
  3. filter hasil query berdasarkan nomor tersebut

untuk proses nomor 2 , kita dapat memanfaatkan function dari sqlserver yaitu row_number()  [3], seperti pada query :

setelah melakukan number maka kita dapat jadikan hal tersebut sebagai nested query dan memfilter datanya berdasarkan kolom number seperti pada query :

 

Paging pada SQLSERVER 2012

khusus pada sqlserver 2012 ke atas, paging dapat dilakukan dengan lebih simple dengan memanfaatkan keyword offset fetch [4], dengan langkah – langkah secara umum sebagai berikut :

  1. buat query dasar lengkap dengan pengurutan
  2. tambahkan keyword offset
  3. tambahkan keyword fetch

seperti pada query :

 

mudah bukan….

happy coding semua…

Referensi

[1] http://blog.sqlauthority.com/2012/03/15/sql-server-install-samples-database-adventure-works-for-sql-server-2012/

[2] http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server

[3] https://msdn.microsoft.com/en-us/library/ms186734.aspx

[4] https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx

Serial Indexing Cantik : Posisi Menentukan Prestasi….

Posted on by radityopw

Posisi Menentukan Prestasi

kalimat yang sering saya dengar saat mahasiswa dulu, dimana sering disebutkan saat mau ujian… banyak yang bilang posisi duduk saat ujian menentukan hasil ujian. Hahaha tentu saja hal itu tidak benar dan sangat tidak berdasar… ya kata gaulnya itu cuman “mitos”. Namun berbeda dengan indexing, hal ini bisa jadi benar lho… “mitos” yang menjadi nyata.

Setelah membaca post saya sebelumnya tentang clustered index vs non clustered index [1],  secara umum anda akan mengetahui bagaimana struktur index dan bagaimana index itu bekerja.

Langsung saja ke contoh nya ya, misalnya saja kita punya tabel dengan definisi sebagai berikut :

 

Dengan query di atas kita akan mendapatkan table bernama “[pt1] [pt2] [pt3] ([ptx])” yang memilki kolom :

  • nim : int : adalah nomor induk mahasiswa yang menjadi primary key (PK)
  • nama : varchar(100) : adalah nama mahasiswa.

 

selain itu juga ada tabel yang bernama “[all_pt]” yang merupakan gabungan dari ke tiga tabel [ptx] tersebut.

setelah itu kita lakukan pengisisan data dengan data dari [2] ke dalam tabel [pt1][pt2][pt3]. dan kita running query penggabungan pada table [all_pt] sebagai berikut :

 

setelah hal itu dilakukan, maka kita akan memiliki seluruh data mahasiswa dari pt1, pt2, pt3 di dalam sebuah tabel yang bernama all_pt. hal itu mengakibatkan kolom nim tidak menjadi hal yang unik lagi melainkan kolom nim dan kolom pt.

mari kita lakukan percobaan,

sebelum menjalankan query tersebut kita terlebih dahulu harus mengaktifkan actual execution plan pada sqlserver management studio [3] [4], seperti pada gambar di bawah ini :

2252_image2

(sumber : https://www.mssqltips.com/sqlservertutorial/2252/estimated-vs-actual-query-plan/ )

 

setelah kita mengaktifkan actual execution plan, maka kita akan dapatkan hasil sebagai berikut :

Screenshot_1.png

dari grafik di atas, dapat kita lihat database melakukan operasi table scan yang dilanjutkan dengan operasi parallelism. Yang menjadi sorot utama kita kali ini adanya operasi table scan yang berarti database melakukan pembacaan pada keseluruhan tabel tanpa menggunakan index sama sekali. Hal tersebut tentu sangat tidak kita inginkan.

Namun hal tersebut juga tidak aneh, karena saat mendefinisikan tabel all_pt, kita sama sekali tidak mendifinisikan index yang ada. Untuk itu mari kita definisikan index berdasarkan query di atas.

Pembuatan index di atas sudah sesuai dengan query yang melakukan query dengan melihat kolom pt terlebih dahulu lalu dilanjutkan dengan kolom nim. Setelah index dibuat mari kita coba query select

Maka akan didapatkan hasil sebagai berikut :

Screenshot_2.png

dari gambar dapat dilihat database telah menggunakan index_1 sebagai objek operasi index seek dan mengkombinasikan dengan RID Lookup. Hal ini tentunya sesuai ekspektasi kita dalam penggunaan index yang harapannya dapat memperbaiki kinerja dari query kita.

Untuk melakukan query berdasarkan Pt dan Nim tentu sudah dapat kita selesaikan, namun bagaimana dengan query berdasarkan Nim saja. Misalnya saja kita ingin melakukan list mahasiswa yang memiliki Nim = 40 seperti query :

maka akan kita dapatkan hasil sebagai berikut :

Screenshot_3.png

database tidak menggunakan index_1 secara maksimal, walaupun tetap menggunakan index tersebut. hal ini Dapat dilihat dari text hijau yang menandakan adanya missing index.

untuk hal ini kita dapat saja membuat index baru khusus berisi kolom Nim saja (seperti yang disarankan pada missing index), namun perlu untuk diingat , bahwa membuat index berarti menambah kompleksitas dalam hal proses insert, delete , update dari tabel. untuk itu jika bisa kita dapat menggunakan index yang telah ada sebaik mungkin.

database tidak menggunakan index_1 dengan maksimal karena dalam index_1 kolom pertama yang terindex adalah kolom Pt lalu diikuti kolom Nim. Yang dapat kita lakukan adalah dengan memperbaiki index_1 dengan mengatur ulang posisi dari kolom kolomnya, kolom Nim akan kita dahulukan lalu diikuti dengan kolom Pt, seperti pada query :

maka akan kita dapatkan hasil untuk query :

Screenshot_4

dapat dilihat bahwa penggunaan index_1 lebih maksimal sekarang, selain indikator missing index yang tidak ada, juga jumlah proses dalam database yang lebih sedikit.

jika kembali kita coba dengan query  :

 

maka akan kita dapatkan hasil :

Screenshot_5.png

sehingga hasilnya tidak berbeda dengan menggunakan index_1 di awal. ini berarti memang membuktikan bahwa

Posisi Menentukan Prestasi

sebelum saya akhiri catatan saya, saya ada sedikit renungan bagi pembaca yang budiman semua…

kita tahu kita punya 2 kondisi index_1, yaitu :

  1. dengan urutan pt,nim
  2. dengan urutan nim, pt

dan dari catatan ini dapat dilihat bahwa untuk query

dan

yang terbaik adalah opsi 2.

nah seandainya kita tetap pada opsi 1 dan melakukan query

mengapa kita mendapatkan hasil seperti di bawah ini ?

Screenshot_6

Referensi

[1]. https://radityo.pw/2016/01/24/clustered-index-vs-non-clustered-index-pada-sqlserver/

[2]. https://github.com/radityopw/blog_files/blob/master/pmp/datagen.txt

[3]. https://msdn.microsoft.com/en-us/library/ms189562.aspx

[4]. https://www.mssqltips.com/sqlservertutorial/2252/estimated-vs-actual-query-plan/

mySimple PHP SQL Generator Function

Posted on by radityopw

Terkadang (sering sih) males banget nulis kode SQL jika sedang memrogram..harus insert into blablabla belum lagi ngebinding ke variabel varibel yang dibutuhkan…Tentu ini sangat merepotkan kalo bekerja dengan banyak form dan dikejar dedline …. :Diseng – iseng karena melihat kemampuan PHP yang banyak memanfaatkan Array , tidak ada salahnya menulis fungsi yang mungkin dapat membantu semua :<?php /*** generate sql for easy to SQL** @param String $mode add,del,edit* @param String $table table name* @param Array $data contain the data that will include in operation* @param String $where where condition* @return String*/public static function generate_sql($mode,$table,$data=array(),$where=”1=1″){$sql = “”;switch($mode){case “add” :$sql = “INSERT INTO $table”;$akey = array();$avalue = array();foreach ($data as $key => $value) {$akey[] = $key;$avalue[] = “‘”.$value.”‘”;}$skey = implode(“,”,$akey);$svalue = implode(“,”,$avalue);$sql .= “(“.$skey.”) values(“.$svalue.”)”;break;case “del” :$sql = “DELETE FROM $table WHERE $where”;break;case “edit” :$sql = “UPDATE $table SET”;$aupdate = array();foreach ($data as $key => $value) {$aupdate[] = “$key=’$value'”;}$supdate = implode(“,”,$aupdate);$sql .= ” $supdate WHERE $where”;break;}return $sql;} ?> intinya function ini dapat bekerja dalam 3 mode => add, edit, dan delete…ah males ah jelasinnya tak beri contoh aja deh…untuk add :<?php ……. $data = array(‘user_info_id’=>$userid,’user_id’=>$this->ms,’address_type’=>’ST’,’address_type_name’=>’mobilestockiestclient’,’first_name’=>$name,’address_1’=>$address,’country’=>$country,’state’=>$state,’city’=>$city);$sql = ps_multiorder::generate_sql(“add”,”#__vm_user_info”,$data);  …….?> untuk edit :<?php ………….$userid = $user_info_id;        $data = array(‘address_1’=>$address,’country’=>$country,’state’=>$state,’city’=>$city);$where = “user_info_id=’$userid'”;$sql = ps_multiorder::generate_sql(“edit”,”#__vm_user_info”,$data,$where);………….?> dan untuk delete :<?php………$userid = $user_info_id;        $data = array();$where = “user_info_id=’$userid'”;$sql = ps_multiorder::generate_sql(“del”,”#__vm_user_info”,$data,$where);………….?>yah sekian deh postingannya daripada kosong…kalo ada ide saran jangan sungkan – sungkan menghubungi saya di radityo_pw[at]is[dot]its[dot]ac[dot]id