sub-query berat

Database dan Structured Query Language

sub-query berat

Postby ndoenk » 15 Dec 2010, 14:05

dulur2, tlg dibantu yaaaa,
untuk menghindari sub query, misal ini untuk menghitung sisa stock barang,

Code: Select all
  1. select b.kode_brg, b.nama_brg,

  2. (select sum(beli.qty) from beli where beli.kode_brg = b.kode_brg)

  3. - (select sum(jual.qty) from jual where jual.kode_brg = b.kode_brg) as sisa

  4. from barang b



itu sub-query sederhana,
kebetulan saya pake DB mic.sqlserver 2005,
knp waktu untuk mengeksekusinya lama sekali, apalagi kl record di tabel beli/jual ribuan jumlahnya,
gmn ya solusinya???

:help:
thx
ndoenk
Junior Member
 
Posts: 9
Joined: 24 May 2010, 17:02

RE: sub-query berat

Postby cyber_hecker » 15 Dec 2010, 14:12

databasenya apa ? emang klo menggunakan sub query lebih lama. coba pake union.
[align=center]
= NeVeR GivE uP =
Image[/align]
User avatar
cyber_hecker
Junior Member
 
Posts: 25
Joined: 06 May 2010, 12:57

RE: sub-query berat

Postby D.E » 15 Dec 2010, 14:15

Bisa ngga kalo begini... (perpanjangan tangan)

Code: Select all
  1. select b.kode_brg, b.nama_brg, sum(beli.qty) - sum(jual.qty) as sisa

  2. from barang b

  3. inner join beli bl on b.kode_brg = bl.kode_brg

  4. inner join jual jl on b.kode_brg = jl.kode_brg

:idea: Dude, if you don't understand the basics and just want to get someone else write the code for you, it means you really shouldn't study computer science. Find different field!
User avatar
D.E
Senior Member
 
Posts: 638
Joined: 04 May 2010, 18:12

RE: sub-query berat

Postby mambamaestro » 15 Dec 2010, 14:15

coba beri index untuk field kode_brg
mambamaestro
Member
 
Posts: 156
Joined: 06 May 2010, 14:14
Location: Surabaya

RE: sub-query berat

Postby reminder2k1 » 15 Dec 2010, 14:17

iya betul... dengan metode itu banyak record akan pengaruh ke kinerja query...
coba pake UNION

Code: Select all
  1. SELECT L.kode_brg, b.nama_brg, SUM(L.beli) AS beli, SUM(L.jual) AS jual

  2. FROM (

  3. SELECT beli.kode_brg, sum(beli.qty) AS beli, 0 AS jual

  4. FROM beli

  5. UNION

  6. SELECT jual.kode_brg, 0 as beli, sum(jual.qty) AS jual

  7. FROM jual

  8. ) AS L INNER JOIN barang b ON L.kode_brg = b.kode_brg

  9. ORDER BY b.nama_brg

Code: Select all
  1. begin

  2.   writeln('Delphi 4ever... ');

  3. end.

User avatar
reminder2k1
Junior Member
 
Posts: 28
Joined: 06 May 2010, 09:19

RE: sub-query berat

Postby ndoenk » 15 Dec 2010, 14:25

@cyber_hecker : pake database sqlserver 2005,
pake union ya, sempat kepikir tapi belum coba

@D.E : inner join opo left join yo
Code: Select all
  1. select b.kode, b.nama,

  2. sum(bl.qty) - sum(jl.qty) as sisa

  3. from barang b

  4. left join beli bl on bl.kode_brg = b.kode

  5. left join jual jl on jl.kode_brg = b.kode



kalo butuh filter, misal untuk table beli tanggal 1-5 aja dan yg tidak batal, sementara jual, untuk tanggal 1 - 15, misal

Code: Select all
  1. select b.kode_brg, b.nama_brg,

  2. (select sum(beli.qty) from beli where beli.kode_brg = b.kode_brg and (beli.tanggal between '01/12/2010' and '10/12/2010'))

  3. - (select sum(jual.qty) from jual where jual.kode_brg = b.kode_brg and jual.tanggal between '01/12/2010' and '15/12/2010') as sisa

  4. from barang b



gmn ya,[hr]
@mambaestro : sudah coba mas, tapi tetep, ASC kan ya

@reminder : woh, kurang pergaulan aku mas, model query ini baru ya, hii hii, ok ok, ini lagi tak coba
solusi anda, thx
ndoenk
Junior Member
 
Posts: 9
Joined: 24 May 2010, 17:02

RE: sub-query berat

Postby D.E » 15 Dec 2010, 14:34

ndoenk wrote:@D.E : inner join opo left join yo


Kalo mau match, inner join 1-1, kalo mau 1-n gunakan left.
:idea: Dude, if you don't understand the basics and just want to get someone else write the code for you, it means you really shouldn't study computer science. Find different field!
User avatar
D.E
Senior Member
 
Posts: 638
Joined: 04 May 2010, 18:12

RE: sub-query berat

Postby tendafahmi » 15 Dec 2010, 14:38

Coba yang ini semoga membantu :-)

Code: Select all
  1. SELECT a.kode_brg,a.nama_brg,b.beli-c.jual as sisa FROM barang a

  2. LEFT JOIN (SELECT a.kode_brg,sum(a.qty) as beli FROM beli a GROUP BY a.kode_brg) b ON a.kode_brg=b.kode_brg

  3. LEFT JOIN (SELECT a.kode_brg,sum(a.qty) as jual FROM jual a GROUP BY a.kode_brg) c ON a.kode_brg=c.kode_brg

signature siapa yah : kecantikanku kecantikan gadis 70an....hehehehehe
User avatar
tendafahmi
Member
 
Posts: 51
Joined: 06 May 2010, 10:22

RE: sub-query berat

Postby cyber_hecker » 15 Dec 2010, 14:52

@tendafahmi : waduh.. querymu lebih sakit prosesnya tuh.. udah pake sub query pake outer join lagi..
[align=center]
= NeVeR GivE uP =
Image[/align]
User avatar
cyber_hecker
Junior Member
 
Posts: 25
Joined: 06 May 2010, 12:57

RE: sub-query berat

Postby ndoenk » 15 Dec 2010, 15:09

@tendafahmi : weleh, bacanya aja aku mumet boz, blm coba itu
ndoenk
Junior Member
 
Posts: 9
Joined: 24 May 2010, 17:02

Next

Who is online

Users browsing this forum: No registered users and 1 guest

cron