Author Topic: [ask] Query MySQL Lambat  (Read 4083 times)

0 Members and 1 Guest are viewing this topic.

Offline johnsalim

  • Newbie
  • *
  • Posts: 37
Re: [ask] Query MySQL Lambat
« Reply #15 on: May 22, 2013, 09:09:30 AM »
select x.*,y.* from tb1 x left join tb2 y on(x.link=y.link) left join tb3 z on(x.link=z.link) left join tb4 w on(x.link=w.link)
where <kondisi>
thanks alot gan

Coba jawab, semoga bisa...
Quote
SELECT Negara.KdNgr, Negara.Negara, Customer.KdCust, Customer.NmCust, Customer.Plant,
Item2.jp, Inquiry.NPR, Inquiry.NoInq, Quotation.KdInq, Quotation.NoQuo, Quotation.TglQuo, Quotation.JmlItm,
Quotation.JmlNego, Quotation.St, ItemQuo.Nego, ItemQuo.TglNego, ItemQuo.KdItemQuo,
ItemQuo.KdItem, Item2.NmItem, Item2.Spec, ItemQuo.Qty, Quotation.Crcy, ItemQuo.UPrc, ItemQuo.Amount,
ItemQuo.TotAmt, Quotation.notes, Quotation.dscrp, ItemQuo.JmlItmA, Quotation.NotPO, Quotation.mexp, Quotation.PO
FROM Negara, Customer, Inquiry, Quotation, ItemQuo, Item2
WHERE Negara.KdNgr = Customer.KdNgr AND Customer.KdCust = Quotation.KdCust AND Quotation.mexp=0
AND Inquiry.KdInq = Quotation.KdInq AND ItemQuo.NoQuo = Quotation.NoQuo AND Item2.KdItem = ItemQuo.KdItem
AND ItemQuo.Nego = Quotation.Nego AND ItemQuo.KdItemQuo = '01' ORDER BY Quotation.TglQuo ;

field-field yang tertulis tebal kamu index, semoga bisa mempercepat....
Kalimat yang tertulis tebal dan berwarna biru = solusi  :toothy4: makasih pak

Offline aris_ah

  • Junior Member
  • *
  • Posts: 279
    • http://www.indosmartsys.com
Re: [ask] Query MySQL Lambat
« Reply #16 on: May 22, 2013, 09:29:43 AM »
neh coba gw perbaiki syntax mu
Code: [Select]
SELECT Negara.KdNgr, Negara.Negara, Customer.KdCust, Customer.NmCust, Customer.Plant,
Item2.jp, Inquiry.NPR, Inquiry.NoInq, Quotation.KdInq, Quotation.NoQuo, Quotation.TglQuo, Quotation.JmlItm,
Quotation.JmlNego, Quotation.St, ItemQuo.Nego, ItemQuo.TglNego, ItemQuo.KdItemQuo,
ItemQuo.KdItem, Item2.NmItem, Item2.Spec, ItemQuo.Qty, Quotation.Crcy, ItemQuo.UPrc, ItemQuo.Amount,
ItemQuo.TotAmt, Quotation.notes, Quotation.dscrp, ItemQuo.JmlItmA, Quotation.NotPO, Quotation.mexp, Quotation.PO
FROM Negara
      inner join Customer on Negara.KdNgr = Customer.KdNgr
      inner join Quotation on Customer.KdCust = Quotation.KdCust
      inner join Inquiry on Inquiry.KdInq = Quotation.KdInq
      inner join ItemQuo on ItemQuo.NoQuo = Quotation.NoQuo  and ItemQuo.Nego = Quotation.Nego
      inner join Item2 on Item2.KdItem = ItemQuo.KdItem
WHERE  Quotation.mexp=0
     AND  ItemQuo.KdItemQuo = '01'
ORDER BY Quotation.TglQuo 

trus tablenya  di index jangan lupa...
Customer  index  KdNgr , KdCust
Negara      index  KdNgr
Quotation  index  KdCust, NoQuo  , Nego
ItemQuo    index  NoQuo, Nego , KdItem
Item2        index   KdItem

Cara indexnya baca help nya yah  :icon_king:

regards

Offline davidmustakim

  • Fox-id M.V.P
  • Hero Member
  • *
  • Posts: 14.053
  • Awas ya...
Re: [ask] Query MySQL Lambat
« Reply #17 on: May 22, 2013, 09:46:49 AM »
ternyata malah butuh waktu 49 detik

Offline johnsalim

  • Newbie
  • *
  • Posts: 37
Re: [ask] Query MySQL Lambat
« Reply #18 on: May 22, 2013, 10:14:23 AM »
neh coba gw perbaiki syntax mu
Code: [Select]
SELECT Negara.KdNgr, Negara.Negara, Customer.KdCust, Customer.NmCust, Customer.Plant,
Item2.jp, Inquiry.NPR, Inquiry.NoInq, Quotation.KdInq, Quotation.NoQuo, Quotation.TglQuo, Quotation.JmlItm,
Quotation.JmlNego, Quotation.St, ItemQuo.Nego, ItemQuo.TglNego, ItemQuo.KdItemQuo,
ItemQuo.KdItem, Item2.NmItem, Item2.Spec, ItemQuo.Qty, Quotation.Crcy, ItemQuo.UPrc, ItemQuo.Amount,
ItemQuo.TotAmt, Quotation.notes, Quotation.dscrp, ItemQuo.JmlItmA, Quotation.NotPO, Quotation.mexp, Quotation.PO
FROM Negara
      inner join Customer on Negara.KdNgr = Customer.KdNgr
      inner join Quotation on Customer.KdCust = Quotation.KdCust
      inner join Inquiry on Inquiry.KdInq = Quotation.KdInq
      inner join ItemQuo on ItemQuo.NoQuo = Quotation.NoQuo  and ItemQuo.Nego = Quotation.Nego
      inner join Item2 on Item2.KdItem = ItemQuo.KdItem
WHERE  Quotation.mexp=0
     AND  ItemQuo.KdItemQuo = '01'
ORDER BY Quotation.TglQuo 

trus tablenya  di index jangan lupa...
Customer  index  KdNgr , KdCust
Negara      index  KdNgr
Quotation  index  KdCust, NoQuo  , Nego
ItemQuo    index  NoQuo, Nego , KdItem
Item2        index   KdItem

Cara indexnya baca help nya yah  :icon_king:

regards
thanks alot gan

Offline davidmustakim

  • Fox-id M.V.P
  • Hero Member
  • *
  • Posts: 14.053
  • Awas ya...
Re: [ask] Query MySQL Lambat
« Reply #19 on: May 22, 2013, 11:51:50 AM »
neh coba gw perbaiki syntax mu
Code: [Select]
SELECT Negara.KdNgr, Negara.Negara, Customer.KdCust, Customer.NmCust, Customer.Plant,
Item2.jp, Inquiry.NPR, Inquiry.NoInq, Quotation.KdInq, Quotation.NoQuo, Quotation.TglQuo, Quotation.JmlItm,
Quotation.JmlNego, Quotation.St, ItemQuo.Nego, ItemQuo.TglNego, ItemQuo.KdItemQuo,
ItemQuo.KdItem, Item2.NmItem, Item2.Spec, ItemQuo.Qty, Quotation.Crcy, ItemQuo.UPrc, ItemQuo.Amount,
ItemQuo.TotAmt, Quotation.notes, Quotation.dscrp, ItemQuo.JmlItmA, Quotation.NotPO, Quotation.mexp, Quotation.PO
FROM Negara
      inner join Customer on Negara.KdNgr = Customer.KdNgr
      inner join Quotation on Customer.KdCust = Quotation.KdCust
      inner join Inquiry on Inquiry.KdInq = Quotation.KdInq
      inner join ItemQuo on ItemQuo.NoQuo = Quotation.NoQuo  and ItemQuo.Nego = Quotation.Nego
      inner join Item2 on Item2.KdItem = ItemQuo.KdItem
WHERE  Quotation.mexp=0
     AND  ItemQuo.KdItemQuo = '01'
ORDER BY Quotation.TglQuo 

trus tablenya  di index jangan lupa...
Customer  index  KdNgr , KdCust
Negara      index  KdNgr
Quotation  index  KdCust, NoQuo  , Nego
ItemQuo    index  NoQuo, Nego , KdItem
Item2        index   KdItem

Cara indexnya baca help nya yah  :icon_king:

regards
thanks alot gan
penasaran pengen tau jadinya lebih cepat berapa kali ?

Offline johnsalim

  • Newbie
  • *
  • Posts: 37
Re: [ask] Query MySQL Lambat
« Reply #20 on: May 22, 2013, 03:31:48 PM »
neh coba gw perbaiki syntax mu
Code: [Select]
SELECT Negara.KdNgr, Negara.Negara, Customer.KdCust, Customer.NmCust, Customer.Plant,
Item2.jp, Inquiry.NPR, Inquiry.NoInq, Quotation.KdInq, Quotation.NoQuo, Quotation.TglQuo, Quotation.JmlItm,
Quotation.JmlNego, Quotation.St, ItemQuo.Nego, ItemQuo.TglNego, ItemQuo.KdItemQuo,
ItemQuo.KdItem, Item2.NmItem, Item2.Spec, ItemQuo.Qty, Quotation.Crcy, ItemQuo.UPrc, ItemQuo.Amount,
ItemQuo.TotAmt, Quotation.notes, Quotation.dscrp, ItemQuo.JmlItmA, Quotation.NotPO, Quotation.mexp, Quotation.PO
FROM Negara
      inner join Customer on Negara.KdNgr = Customer.KdNgr
      inner join Quotation on Customer.KdCust = Quotation.KdCust
      inner join Inquiry on Inquiry.KdInq = Quotation.KdInq
      inner join ItemQuo on ItemQuo.NoQuo = Quotation.NoQuo  and ItemQuo.Nego = Quotation.Nego
      inner join Item2 on Item2.KdItem = ItemQuo.KdItem
WHERE  Quotation.mexp=0
     AND  ItemQuo.KdItemQuo = '01'
ORDER BY Quotation.TglQuo 

trus tablenya  di index jangan lupa...
Customer  index  KdNgr , KdCust
Negara      index  KdNgr
Quotation  index  KdCust, NoQuo  , Nego
ItemQuo    index  NoQuo, Nego , KdItem
Item2        index   KdItem

Cara indexnya baca help nya yah  :icon_king:

regards
thanks alot gan
penasaran pengen tau jadinya lebih cepat berapa kali ?
pak david, solusi masalah saya adalah menambahkan index, sebagaimana saran pak subiyantoro dan para suhu fox-id lainnya...
setelah saya tambahkan index sudah cepat (kurang dari 1 detik), meskipun syntaxnya masih pakai syntax di post 1...
untuk syntax menggunakan inner join sudah saya coba, karena sama2 cepat saya belum tau perbedaannya, entah kalu jumlah recordnya jutaan gak tau lagi, untuk left join belum saya coba, tapi saya yakin suatu saat pasti saya butuh....
terima kasih semuanya.....
semua tabel yang masuk kriteria pencarian sudah saya tambahkan index.....
oh ya mau tanya lagi, jika sudah menambahkan index sekali, maka perlu di-update index dalam periode tertentu atau tidak perlu karena otomatis akan update dengan sendirinya ?? <mohon pencerahan>

Offline davidmustakim

  • Fox-id M.V.P
  • Hero Member
  • *
  • Posts: 14.053
  • Awas ya...
Re: [ask] Query MySQL Lambat
« Reply #21 on: May 22, 2013, 06:07:35 PM »
clipper, foxbase maupun foxpro mengenal command REINDEX, kemungkinan besar dibuat karna ada gunanya, kayaknya aja sih gitu... ato mungkin juga karna sekedar iseng ya.... hehehe, ... tapi untuk database lain saya belum pernah explore secara khusus seberapa jauh kehandalannya dengan sekali buat aja ... baca2 dulu lah ... http://dev.mysql.com/doc/refman/5.0/en/rebuilding-tables.html  dan   http://serverfault.com/questions/166398/when-do-i-reindex-my-mysql-database

Offline nia

  • Full Member
  • *
  • Posts: 76
Re: [ask] Query MySQL Lambat
« Reply #22 on: May 22, 2013, 07:24:07 PM »
kemrn2 nia pernah pake 'optimize table', tp skrg pake na 'analyze table' buat semcm reindex di mysql

Offline davidmustakim

  • Fox-id M.V.P
  • Hero Member
  • *
  • Posts: 14.053
  • Awas ya...
Re: [ask] Query MySQL Lambat
« Reply #23 on: May 22, 2013, 07:36:53 PM »
kalo ceritanya merasa perlu lakukan semacam "overhaul maintenance" menyeluruh ya sekalian aja ngedump dan re-load infile

Offline shee

  • Hero Member
  • *
  • Posts: 756
Re: [ask] Query MySQL Lambat
« Reply #24 on: May 23, 2013, 07:04:01 PM »
analyze table dan optimasi table hanya berfungsi jika engine table nya adalah myisam, kalau inndodb ga perlu di jamin tablenya akan sehat walafiat slamanya ....

Offline dan2y

  • Senior Member
  • *
  • Posts: 577
Re: [ask] Query MySQL Lambat
« Reply #25 on: May 23, 2013, 07:15:13 PM »
Code: [Select]
create curs tes(KdNgr c(xx), Negara c(xx), KdCust c(xx), NmCust c(xx), Plant c(xx),
jp c(xx), NPR c(xx), NoInq c(xx), KdInq c(xx), NoQuo c(xx), TglQuo d, JmlItm n(xx),
JmlNego n(xx), St c(xx), Nego n(xx), TglNego d, KdItemQuo c(xx),
KdItem c(xx), NmItem c(xx), Spec c(xx), Qty n(xx), Crcy n(xx), UPrc n(xx), Amount n(xx),
TotAmt n(xx), notes c(xx), dscrp n(xx), JmlItmA n(xx), NotPO c(xx), mexp c(xx), PO c(xx)

ckd="01"
sele itemquo
set order to kditemquo
seek CKD
SCAN WHILE kditemquo=CKD
cNoQuo=ItemQuo.NoQuo
cnego=ItemQuo.nego
ckditem=ItemQuo.KdItem

SELE TES
APPEN BLAN
REPLA nego WITH itemquo.nego
repla tglnego with itemquo.tglnego
repla kditemquo with itemquo.kditemquo
repla kditem with itemquo.kditem
repl uprc with itemquo.uprc
repla amount with itemquo.amount
repl totamt with itemquo.totamt
repla jmlitma with itemquo.jmlitma

sele Quotation
set order to NoQuo
seek cnoquo
if Quotation.mexp=0
if cnego=Quotation.Nego
repl TES.KdInq with Quotation.KdInq
repla TES.NoQuo with cnoquo
repla tes.TglQuo with Quotation.TglQuo
repl tes.JmlItm with Quotation.JmlItm
repl tes.JmlNego with Quotation.JmlNego
repl tes.St with Quotation.St
repl tes.Crcy with Quotation.Crcy
repla tes.notes with Quotation.notes
repla tes.dscrp with Quotation.dscrp
repl tes.NotPO with Quotation.NotPO
repla tes.mexp with Quotation.mexp
repla tes.PO with Quotation.PO
endif

sele Item2
set order to KdItem
seek ckditem
repl tes.jp with Item2.jp
repl tes.NmItem with Item2.NmItem
repla tes.Spec with Item2.Spec
endif
ENDSCAN

Coba juga kalo ga pake Query seperti contoh di atas, memang codingnya lebih panjang tapi hasilnya memuaskan, menggunakan Query di aplikasi multi user untuk data yang lumayan besar itu bisa bikin trafic jaringan naik drastis sampai-sampai bisa bikin lock database. Pengalamanku waktu kerja di pabrik dulu seperti itu  :icon_biggrin:
Atau kalo pakai SQL Server sebaiknya di manfaatkan semaksimal mungkin penggunaan Store Procedure.

Offline davidmustakim

  • Fox-id M.V.P
  • Hero Member
  • *
  • Posts: 14.053
  • Awas ya...
Re: [ask] Query MySQL Lambat
« Reply #26 on: May 23, 2013, 10:29:44 PM »
Code: [Select]
create curs tes(KdNgr c(xx), Negara c(xx), KdCust c(xx), NmCust c(xx), Plant c(xx),
jp c(xx), NPR c(xx), NoInq c(xx), KdInq c(xx), NoQuo c(xx), TglQuo d, JmlItm n(xx),
JmlNego n(xx), St c(xx), Nego n(xx), TglNego d, KdItemQuo c(xx),
KdItem c(xx), NmItem c(xx), Spec c(xx), Qty n(xx), Crcy n(xx), UPrc n(xx), Amount n(xx),
TotAmt n(xx), notes c(xx), dscrp n(xx), JmlItmA n(xx), NotPO c(xx), mexp c(xx), PO c(xx)

ckd="01"
sele itemquo
set order to kditemquo
seek CKD
SCAN WHILE kditemquo=CKD
cNoQuo=ItemQuo.NoQuo
cnego=ItemQuo.nego
ckditem=ItemQuo.KdItem

SELE TES
APPEN BLAN
REPLA nego WITH itemquo.nego
repla tglnego with itemquo.tglnego
repla kditemquo with itemquo.kditemquo
repla kditem with itemquo.kditem
repl uprc with itemquo.uprc
repla amount with itemquo.amount
repl totamt with itemquo.totamt
repla jmlitma with itemquo.jmlitma

sele Quotation
set order to NoQuo
seek cnoquo
if Quotation.mexp=0
if cnego=Quotation.Nego
repl TES.KdInq with Quotation.KdInq
repla TES.NoQuo with cnoquo
repla tes.TglQuo with Quotation.TglQuo
repl tes.JmlItm with Quotation.JmlItm
repl tes.JmlNego with Quotation.JmlNego
repl tes.St with Quotation.St
repl tes.Crcy with Quotation.Crcy
repla tes.notes with Quotation.notes
repla tes.dscrp with Quotation.dscrp
repl tes.NotPO with Quotation.NotPO
repla tes.mexp with Quotation.mexp
repla tes.PO with Quotation.PO
endif

sele Item2
set order to KdItem
seek ckditem
repl tes.jp with Item2.jp
repl tes.NmItem with Item2.NmItem
repla tes.Spec with Item2.Spec
endif
ENDSCAN

Coba juga kalo ga pake Query seperti contoh di atas, memang codingnya lebih panjang tapi hasilnya memuaskan, menggunakan Query di aplikasi multi user untuk data yang lumayan besar itu bisa bikin trafic jaringan naik drastis sampai-sampai bisa bikin lock database. Pengalamanku waktu kerja di pabrik dulu seperti itu  :icon_biggrin:
Atau kalo pakai SQL Server sebaiknya di manfaatkan semaksimal mungkin penggunaan Store Procedure.
apa nih hubungannya dengan "reindexing" tabel mySql??

Offline dan2y

  • Senior Member
  • *
  • Posts: 577
Re: [ask] Query MySQL Lambat
« Reply #27 on: May 23, 2013, 11:57:21 PM »
Code: [Select]
create curs tes(KdNgr c(xx), Negara c(xx), KdCust c(xx), NmCust c(xx), Plant c(xx),
jp c(xx), NPR c(xx), NoInq c(xx), KdInq c(xx), NoQuo c(xx), TglQuo d, JmlItm n(xx),
JmlNego n(xx), St c(xx), Nego n(xx), TglNego d, KdItemQuo c(xx),
KdItem c(xx), NmItem c(xx), Spec c(xx), Qty n(xx), Crcy n(xx), UPrc n(xx), Amount n(xx),
TotAmt n(xx), notes c(xx), dscrp n(xx), JmlItmA n(xx), NotPO c(xx), mexp c(xx), PO c(xx)

ckd="01"
sele itemquo
set order to kditemquo
seek CKD
SCAN WHILE kditemquo=CKD
cNoQuo=ItemQuo.NoQuo
cnego=ItemQuo.nego
ckditem=ItemQuo.KdItem

SELE TES
APPEN BLAN
REPLA nego WITH itemquo.nego
repla tglnego with itemquo.tglnego
repla kditemquo with itemquo.kditemquo
repla kditem with itemquo.kditem
repl uprc with itemquo.uprc
repla amount with itemquo.amount
repl totamt with itemquo.totamt
repla jmlitma with itemquo.jmlitma

sele Quotation
set order to NoQuo
seek cnoquo
if Quotation.mexp=0
if cnego=Quotation.Nego
repl TES.KdInq with Quotation.KdInq
repla TES.NoQuo with cnoquo
repla tes.TglQuo with Quotation.TglQuo
repl tes.JmlItm with Quotation.JmlItm
repl tes.JmlNego with Quotation.JmlNego
repl tes.St with Quotation.St
repl tes.Crcy with Quotation.Crcy
repla tes.notes with Quotation.notes
repla tes.dscrp with Quotation.dscrp
repl tes.NotPO with Quotation.NotPO
repla tes.mexp with Quotation.mexp
repla tes.PO with Quotation.PO
endif

sele Item2
set order to KdItem
seek ckditem
repl tes.jp with Item2.jp
repl tes.NmItem with Item2.NmItem
repla tes.Spec with Item2.Spec
endif
ENDSCAN

Coba juga kalo ga pake Query seperti contoh di atas, memang codingnya lebih panjang tapi hasilnya memuaskan, menggunakan Query di aplikasi multi user untuk data yang lumayan besar itu bisa bikin trafic jaringan naik drastis sampai-sampai bisa bikin lock database. Pengalamanku waktu kerja di pabrik dulu seperti itu  :icon_biggrin:
Atau kalo pakai SQL Server sebaiknya di manfaatkan semaksimal mungkin penggunaan Store Procedure.
apa nih hubungannya dengan "reindexing" tabel mySql??

Ga ada, iseng aja  :toothy4: :toothy4: :toothy4:

Offline nia

  • Full Member
  • *
  • Posts: 76
Re: [ask] Query MySQL Lambat
« Reply #28 on: May 24, 2013, 06:12:39 AM »