Topic: [MYSQL SQL Perfomance]

duh saat in iperushaan yang aku tempati mulai merubah sitem dari windows base(walaupun masih tetap ada) mau ke linux. jadi model desktop based dirubah ke model web based.
nah kebetulan aku menghandle webbase untuk saat ini.

ketika kau harus membuat suatu sql untuk menilai semua orang aku cari cara, aku sudah ada yang versi MsSQL itu dan saya rubah ke MYSQL menjadi:


select nip,ucode_kry,nama,
    (select count(jenis_sa) as sp3_2 from hr.tb_sanksi a
        where jenis_sa='Surat Peringatan' and
        (bobot='II' or bobot='III')
        and a.ucode_kry=x.ucode_kry  and tgl_sa between '2007-01-01' and '2007-06-30') as sp3_2,
       
    (select count(jenis_sa) as sp1_st3 from hr.tb_sanksi a
        where ((jenis_sa='Surat Peringatan' and
        bobot='I') or (jenis_sa='Surat Teguran' and bobot='III') )
        and a.ucode_kry=x.ucode_kry  and tgl_sa between '2007-01-01' and '2007-06-30') as sp1_st3,
       
    (select count(jenis_sa) as st2_1 from hr.tb_sanksi a
        where jenis_sa='Surat Teguran' and
        (bobot='II' or bobot='I') and a.ucode_kry=x.ucode_kry  and tgl_sa between '2007-01-01' and '2007-06-30') as st2_1,   
       
    (select count(jenis_sa) as stl from hr.tb_sanksi a
        where jenis_sa='Surat Teguran Lisan' and
        a.ucode_kry=x.ucode_kry  and tgl_sa between '2007-01-01' and '2007-06-30') as stl,
       
    (select count(status_absen) as alpha from hr.tb_d_absb a
        where (status_absen='CTA' or status_absen='A') and a.ucode_kry=x.ucode_kry  and tgl_absen between '2007-01-01' and '2007-06-30') as alpha,
       
    (select count(status_absen) as ijin from hr.tb_d_absb a
        where (status_absen='CTI'
        or status_absen='I' 
        or  status_absen='SO'
        or  status_absen='CSO') and a.ucode_kry=x.ucode_kry  and tgl_absen between '2007-01-01' and '2007-06-30') as ijin,
       
    (select count(status_absen) as dg_srt from hr.tb_d_absb a
        where status_absen='SI' and a.ucode_kry=x.ucode_kry  and tgl_absen between '2007-01-01' and '2007-06-30') as dg_srt,
       
    (select sum(a.jam_terlambat) as jam from hr.tb_d_absb a
         where a.ucode_kry=x.ucode_kry  and tgl_absen between '2007-01-01' and '2007-06-30') as jam_absen,
       
    (select (select count(status_absen) as o from hr.tb_d_absb a
        where status_absen='CT' and a.ucode_kry=y.ucode_kry  and tgl_absen between '2007-01-01' and '2007-06-30')
        +
        (select count(status_absen)/2) as hl
        from hr.tb_d_absb y
        where  (Upper(y.status_absen) = 'CT1/2-A'
        OR Upper(y.status_absen) = 'CT1/2-B')
        and y.ucode_kry=x.ucode_kry  and tgl_absen between '2007-01-01' and '2007-06-30' group by y.ucode_kry) as cuti,
   
    (select ifnull(rokok,'Y') as merokok from hr.tb_rokok a
        where a.ucode_kry=x.ucode_kry order by tgl_update desc limit 0,1) as merokok
       
    ,
(select    
sum(
    substring(TIMEDIFF(ifnull(a.jam_pulang,c.jam_pulang),jam_datang),1,2)*60
    +
    substring(TIMEDIFF(ifnull(a.jam_pulang,c.jam_pulang),jam_datang),4,2)
)
as total
from  hr.tb_d_absb a left join maindb.tb_karyawan b on a.ucode_kry=b.ucode_kry
left join maindb.tb_harikerja c on b.ucode_shift=c.ucode_shift
where a.ucode_kry=x.ucode_kry and a.status_absen='M'
and c.no_sort=dayofweek(a.tgl_absen)
and tgl_absen between '2007-01-01' and '2007-06-30'
) as jam,

datediff('2007-06-30',x.tgl_masuk) as masuk_kerja,
datediff('2007-06-30','2007-01-01') as waktu_kerja

from maindb.tb_karyawan x
left join maindb.tb_divisi d on x.ucode_divisi=d.ucode_divisi
left join maindb.tb_dept e on x.ucode_departemen=e.ucode_dept
where x.status_karyawan = 'Aktif' and x.status_gaji='Bulanan' 
order by nama

kebetulan yang diatas itu cuman dengan periode tertentu.
dari percobaan diatas aku coba runing hasilnya variable di mysql, paling cepat (cuman 2x) itu 40detik paling lama 8menit (selalu)untuk mengekseskusi perintah SQL diatas. aku bingung masak untuk query lamanya seperti itu. untuk data yang absensi memang jumlah total recordnya sekitar 600 ribuan ya saya maklum kalo lambat, tapi kalo 8menit (masa Allah lama banget ntar orang-orang pada ngopi dulu).

akhirnya aku pilah-pilah dan aku test untuk permasing-masing query dan aku kumpulkan lagi menjadi seperti ini:

select c.ucode_kry,
(select count(jenis_sa) as sp3_2 from hr.tb_sanksi a
    where jenis_sa='Surat Peringatan' and
    (bobot='II' or bobot='III')
    and a.ucode_kry=c.ucode_kry  and tgl_sa between '2007-01-01' and '2007-06-30') as sp3_2,
   
(select count(jenis_sa) as sp1_st3 from hr.tb_sanksi a
    where ((jenis_sa='Surat Peringatan' and
    bobot='I') or (jenis_sa='Surat Teguran' and bobot='III') )
    and a.ucode_kry=c.ucode_kry  and tgl_sa between '2007-01-01' and '2007-06-30') as sp1_st3,
   
(select count(jenis_sa) as st2_1 from hr.tb_sanksi a
    where jenis_sa='Surat Teguran' and
    (bobot='II' or bobot='I') and a.ucode_kry=c.ucode_kry  and tgl_sa between '2007-01-01' and '2007-06-30') as st2_1,   
   
(select count(jenis_sa) as stl from hr.tb_sanksi a
    where jenis_sa='Surat Teguran Lisan' and
    a.ucode_kry=c.ucode_kry  and tgl_sa between '2007-01-01' and '2007-06-30') as stl,
ifnull(a.ct + b.ct,0) as cuti,
d.total,
(select ifnull(rokok,'Y') as merokok from hr.tb_rokok a
where a.ucode_kry=c.ucode_kry order by tgl_update desc limit 0,1) as merokok,
datediff('2007-06-30',c.tgl_masuk) as masuk_kerja,
datediff('2007-06-30','2007-01-01') as waktu_kerja

from
maindb.tb_karyawan c

left join
(select  b.ucode_kry,count(status_absen) as ct from hr.tb_d_absb a
inner join maindb.tb_karyawan b on a.ucode_kry=b.ucode_kry
where status_absen='CT'
and tgl_absen between '2007-01-01' and '2007-06-30'
group by b.ucode_kry) as a on a.ucode_kry=c.ucode_kry

left join
(select  b.ucode_kry,count(status_absen)/2 as ct from hr.tb_d_absb a
inner join maindb.tb_karyawan b on a.ucode_kry=b.ucode_kry
where (status_absen='CT1/2-A' or status_absen='CT1/2-B')
and tgl_absen between '2007-01-01' and '2007-06-30'
group by b.ucode_kry) as b
on b.ucode_kry=c.ucode_kry

left join (select  b.ucode_kry,status_karyawan,   
sum(
    substring(TIMEDIFF(ifnull(a.jam_pulang,(select  c.jam_pulang
        from maindb.tb_harikerja c
        where c.no_sort=dayofweek(a.tgl_absen)
        and b.ucode_shift=c.ucode_shift
        )
    ),a.jam_datang),1,2)*60
    +
    substring(TIMEDIFF(ifnull(a.jam_pulang,(select  c.jam_pulang
        from maindb.tb_harikerja c
        where c.no_sort=dayofweek(a.tgl_absen)
        and b.ucode_shift=c.ucode_shift
        )
    ),a.jam_datang),4,2)
)
as total
from  hr.tb_d_absb a left join maindb.tb_karyawan b on a.ucode_kry=b.ucode_kry
where a.status_absen='M'
and a.tgl_absen between '2007-01-01' and '2007-06-30'
group by a.ucode_kry
) as d on d.ucode_kry=c.ucode_kry

where c.status_karyawan='Aktif'
and c.status_gaji='Bulanan'
and c.tgl_masuk<='2007-06-30'

akhirnya lumayan bisa menjadi 5 - 8 detik untuk proses querynya.
kira-kira pa penyebabnya yah?
kalo menurut aku sih index nya yang tidak berjalan.

mungkin ada teman-teman yang mengalaminya?

Terima kasih

http://icare.jagoanhosting.com/banners/footer-jagoan-hosting-indonesia.gif << web hosting surabaya, mo bikin web murah n kalo ada apa-apa bisa langsung disamperin big_smile
Guling-guling ... http://www.mysmiley.net/imgs/smile/happy/happy0071.gif hihihihi jadi pusing