Tunning Query Execution Ms SQL

Saya mau sharing pengalaman saya nih, mengenai Database MS SQL, data kami ketika transaki lemoooot banget, karena data yang terlalu besar...ada yang bilang aplikasi yang salah, bla ..bla ... bla...
gak taunya nemu tread ini http://www.kaskus.co.id/showthread.php?t=9509493
ternyata kesalahan kami di database yang tidak memakai index. dan sekarang problem solve... ni kutipan dari thread tersebut semoga banyak membantu, dan trimakasih banyak untuk Achilles123 [$].

Bagi agan2 yg pernah ngalamin "nih aplikasi kok jalannya lemot amat ya?"
tunggu 5menit lom tampil datanya, sabar deh tunggu lagi 30menit, masih ga tampil
ampun deh gw tinggal makan siang aja lah, abis maksi ...
eh udah keluar, et et et tapi kok yg tampil error "Time Out". hammer

Nah kali salah satu trik mempercepat query PALING AMPUH (salah satu):
INDEXING

BISA dipastikan query yg mengakses puluhan/ribuan juta/Miliyaran data akan sangat lambat jika tdk ada INDEX. 

Pertanyaan:

1. Apa itu index, Bagaimana cara kerjanya dan apa Kegunaanya?

2. Kapan index diperlukan?

3. Field Apa saja yg perlu diinddex?

4. susunan index yg optimal spt apa?

Studi Kasus:
Nama Tabel :T_Kegiatan
Field :

1. Id Int (Bukan Primary Key)
2. Kegiatan varchar(100)
3. TglJam datetime

Jumlah data 35.000.000

Query yg dijalankan:
Select * from T_Kegiatan where TglJam>='2011/01/01 00:00:01" and TglJam <='2011/01/01 00:30:01"

Hasil:
- Durasi execution 30menit
- Hasil yg keluar 8 Record

Knp Lambat? Cekidot for Solusi:
-Table Scan alias mencari data satu-satu mulai record ke-1 sampai ke-35jt.
akibatnya lambat, ini sama saja dengan buku super tebal tanpa daftar isi/index .
Bayangkan agan mau cari sesuatu pd buku 1jt halaman tanpa daftar isi/index? (Ini Jawaban pertanyaan 1)

-Mencari data pada data yg besar - 35jt record (Ini jawaban pertanyaan 2).

-Tdk ada Index.

-Diperlukan Index pada field TglJam. (Ini jawaban pertanyaan 3)

Jawaban no.4 baca di bawah ya gan



2. Kapan index diperlukan ?

1. Saat Query terasa lambat krn jumlah data semakin banyak.
2. Table dengan data yang sedang sampai besar (kira2 di atas 5rb record).
3. Jika data yg ingin di catch kira2 1-2% dari total data.

3. Field Apa saja yg perlu diinddex?

1. Field yg mayoritas digunakan dalam where dan on.
2. Field yg nilai/isi datanya hampir unik.

Tambahan dari agan Wiwik

Quote:

Originally Posted by widik lanjut gan Biasanya pedoman ane dalam tuning index - Index unik pada kolom parent key - Index unik pada kolom kunci utama (primary key) - Index pada kolom kunci asing (foreign key) - Index pada kolom yang digunakan sebagai kriteria pemilihan - Index pada kolom yang digunakan untuk pengurutan (order by) - Index pada kolom yang digunakan untuk pengelompokan (group by) apalagi ya?

4. susunan index yg optimal spt apa?

1. Harusnya urutan where/on pada query mirip dengan urutan di Index.
2. Jangan buat terlalu banyak index krn malah akan memperlambat query. jd ikuti jawaban no.3 untuk memilih field2 tepat, dan jgn buat index untuk semua kondisi where/on bisa lemot abis malah
3. Letakkan Field yg paling unik didepan, lalu berikutnya yg agak unik, begitu seterusnya (saran sy max 4 field index cukup memadai, jgn lebih!!!).

 Trik tunning :
Field mana yg perlu diindex (Advance)
- Urutan Index
- Execution Plan
- Maintenance Index
- Anti Normalisasi (Denormalisasi).
- CheckSum Index.
- Partition Table dan Partition Index.
- Batching Process.
- Put Data on RAM and make it Permanent.

Study Kasus Memahami cara kerja SQL Engine Memilih Index
(Gunakan Estimate Execution Plan)..

Table: t_transaksi (yg menampung semua transaksi pembayaran)
Field2: id (Cluster Index primary key), id_cabang (Non Cluster Index = IdxCbg), tgltransaksi, pembayaran.
Jumlah Row: 100rb

setelah digroup by id_cabang diperoleh hsl sbb:
id_cabang count
001 - 80000
002 - 19900
003 - 100

SOAL 1
Select * from t_transaksi whre id_cabang='001'

Pertanyaan:
1. jika query tersebut dijalankan apa index IdxCbg digunakan?
2. index apa yg digunakan pada query diatas index seek atau scan?
3. Cluster index atau non cluster index yg digunakan?

SOAL 2
Select * from t_transaksi whre id_cabang='003'

Pertanyaan:

1. jika query tersebut dijalankan apa index IdxCbg digunakan?
2. index apa yg digunakan pada query diatas index seek atau scan?
3. Cluster index atau non cluster index yg digunakan?

Pertanyaan Akhir:
1. menurut agan apa perlu buat index baru dgn nama IdxCbg field id_Cabang ?
2. Apa yg membedakan soal 1 dan 2? 

JAWABAN :

Originally Posted by Coolxs

G Coba jawab ya:

Sebelum g jawab g kasi penjelasan yang menurut gue dulu :

Non Clustered index adalah sebuah index yang diurutan berdasarkan indexnya bukan dari row nya dimana clustered index akan sangat berguna jika ingin mencari data yang sedikit di dalam row yang jumlahnya banyak

Clustered index adalah sebuah index yang akan membuat baris baris di dalam row menjadi berurutan (jadi disini row di dalam table yang diurutkan bukan dari indexnya) Hal ini sangat berguna karena pembacaan row menjadi sequential / tidak loncat2

Jadi yang soal 1 harusnya idxCbg ngak digunakan karena row yang dicari terlalu banyak (80% dari total row) sehingga penggunaan index akan memperlambat. Jika Perlu dioptimasi maka perlu dibuat clustered index idxCbg tersebut

namun yang soal 2 IdxCbg seharusnya digunakan karena cocok dengan prinsip Non Cluster index.

Quote:

Originally Posted by Coolxs

Waduh Pertanyaan yang mana ni yang index seek sama index scan ya.
Kalau index seek adalah optimizer akan menggunakan index sebagai bahan pencarian yang mana sangatlah cocok untuk query yang selective sebagai contoh yang id_cabang = '103'

Sedangkan index scan adalah optimizer akan membaca semua row di dalam table dan mengembalikan yang sesuai dengan criteria. Disini agan bertanya kenapa ga pake index saja bacanya. Hal ini karena pembacaan semua row tanpa menggunakan index pembacaan dengan menggunakan index. sehingga untuk query yang mengembalikan banyak row yaitu id_cabang = 101 dimana 80% dari total row maka kemungkinan besar akan menggunakan index scan .

Sedangkan untuk pembuatan cluster dan nonclustered index pada soal keempat sebenarnya tergantung seberapa sering query tersbeut digunakan dan juga seberapa menggangunya. Jelas pembuatan index tersebut akan mempercepat namun perlu diingat juga bahwa pembuatan index akan memperlama proses DML dan akan membuang jumlah hard disk. nah yang ini tergantung kebutuhan agan

 

sementara ini dahulu, sebenarnya masih banyak yang laen...