Tablo Birleştirme-Join

[sahinemrah44]

Merhaba yaklaşık 1 yıldır üzeriden çalıştığım ve şuanda kullanılan barkod entegreli bir proje var. Vt firebrid 2.0 yeni kurdum(önce interbase 6.0 idi) 18 adet tablom var bunların içinde en büyük tablomda yaklaşık 160.000 kayıt var, vt boyutu 250 mb, delphi 7.0 ve ibx bileşenlerini kullanıyorum. Rapor oluşturmak için aşağıdaki sql kodunu kullanarak 4 adet tabloyu birleştirerek ve istenilen alanlara(index tanımlı) göre sorgulama yaptırıyorum. Formda bir çok yerde anlatılmış join işlemleri ile ilgi konular hepsini okudum sanırım sql kodunu en son bu hale getirdim ve şuanda çalışıyor(inşallah başkalarının da işine yarar).
1- Sorguyu çalıştırdığım zaman seçtiğim alan sayısına göre hız artıyor/azalıyor 10 adet alana göre sorgulama yaptığımda 25-30 saniye sürüyor sorgu sonucunu görmem. En son kayıta gitmem ise yaklaşık 1-2 dakikamı alıyor bu sorunu nasıl çözebilirim???
2- Kayıtları çekince IbQuery kapatmak 2 dakikamı alıyor nasıl hızlandıra bilirim???
3- Performansı artırmak için terminator arkadaşımızın yazmış olduğu ayarları kontrol ettim. Sorgu çalışınca temp dizininde sort benzeri dosyalar oluşuyor ve Ibquery kapatınca kayboluyor. bu nedenle SortMemBlockSize = 1048576 den 1073741824 çıkardım. Sorgu çalışınca ram kullanımı birden arttı ama sadece ilk seferde oldu ve temp dizininde yine sort yazılı bir dosya oluştu bundan sonrada daha hiçbir sorguda ram birden arttığını görmedim. Nedeni????
İyi çalışmalar.


Not: Server P4 3.0, ram 1.536, 80 gb hdd- client dell d600
Aşağıdaki kod 4 alana göre sorgulama yapılmıştır.

Kod: Tümünü seç

SELECT 
fırma_adı,ıplık_ne,ıplık_tıpı,partı_no
, coalesce(sum(sıparıs_mktr),0) as sıparıs_mktr

,(select coalesce(sum(net_uretım),0) from uretim_bilgisi  where uretım_emrı.fırma_adı=uretım_bılgısı.fırma_adı and uretım_emrı.ıplık_ne=uretım_bılgısı.ıplık_ne and uretım_emrı.ıplık_tıpı=uretım_bılgısı.ıplık_tıpı and (uretım_emrı.partı_no=uretım_bılgısı.partı_no or (uretım_emrı.partı_no is null and uretım_bılgısı.partı_no is null)) and uretım_bılgısı.uretım_trh>'01.04.2006 07:00' and uretım_bılgısı.uretım_trh<'11.04.2006 07:00')as net_uretım

,(select coalesce(sum(brut_uretım),0) from uretim_bilgisi  where uretım_emrı.fırma_adı=uretım_bılgısı.fırma_adı and uretım_emrı.ıplık_ne=uretım_bılgısı.ıplık_ne and uretım_emrı.ıplık_tıpı=uretım_bılgısı.ıplık_tıpı and (uretım_emrı.partı_no=uretım_bılgısı.partı_no or (uretım_emrı.partı_no is null and uretım_bılgısı.partı_no is null)) and uretım_bılgısı.uretım_trh>'01.04.2006 07:00' and uretım_bılgısı.uretım_trh<'11.04.2006 07:00')as brut_uretım

,(select coalesce(sum(net_sevk),0) from sevk_bilgisi  where uretım_emrı.fırma_adı=sevk_bılgısı.fırma_adı and uretım_emrı.ıplık_ne=sevk_bılgısı.ıplık_ne and uretım_emrı.ıplık_tıpı=sevk_bılgısı.ıplık_tıpı and (uretım_emrı.partı_no=sevk_bılgısı.partı_no or (uretım_emrı.partı_no is null and sevk_bılgısı.partı_no is null)))as net_sevk

,(select coalesce(sum(brut_sevk),0) from sevk_bilgisi  where uretım_emrı.fırma_adı=sevk_bılgısı.fırma_adı and uretım_emrı.ıplık_ne=sevk_bılgısı.ıplık_ne and uretım_emrı.ıplık_tıpı=sevk_bılgısı.ıplık_tıpı and (uretım_emrı.partı_no=sevk_bılgısı.partı_no or (uretım_emrı.partı_no is null and sevk_bılgısı.partı_no is null)))as brut_sevk

,(select coalesce(sum(net_uretım),0) from ambar  where uretım_emrı.fırma_adı=ambar.fırma_adı and uretım_emrı.ıplık_ne=ambar.ıplık_ne and uretım_emrı.ıplık_tıpı=ambar.ıplık_tıpı and (uretım_emrı.partı_no=ambar.partı_no or (uretım_emrı.partı_no is null and ambar.partı_no is null) ))as net_ambar

,(select coalesce(sum(brut_uretım),0) from ambar  where uretım_emrı.fırma_adı=ambar.fırma_adı and uretım_emrı.ıplık_ne=ambar.ıplık_ne and uretım_emrı.ıplık_tıpı=ambar.ıplık_tıpı and (uretım_emrı.partı_no=ambar.partı_no or (uretım_emrı.partı_no is null and ambar.partı_no is null) ))as brut_ambar

FROM URETIM_EMRI

GROUP BY
fırma_adı,ıplık_ne,ıplık_tıpı,partı_no

[rsimsek]

Sorgunda sanırım kopyala yapıştır yaparken i harfleri ı şeklinde olmuş.

Sorguda anladığım kadarıyla URETIM_EMRI dediğin üretim master kayıtlarının tutulduğu bir tablo. Bu çok yoğun kullanılan bir tablo ise ve ayrıca bir FIRMA kayıt tablosu varsa FIRMA_KOD ları ile ilişkilendirmen lazım. STRING ifadelerden ve tekrarlayan bilgilerden ne kadar kaçınırsan VT perfomansı o kadar artacaktır. Ayrıca sorgulamada kullandığın alanlar indekslenmiş alanlar olması perfomansı katlayacaktır Tablo desenlerinde / structure ne tip ve boyda bilgiler var bilemiyorum fakat 250 MB. verdiğin en büyük tabloya göre yüksek bir rakkam

Tekrar baktığımda sorgularda URETIM_EMRI.FIRMA_ADI = URETIM_BILGISI.FIRMA_ADI şeklinde değil de yapıyı URETIM_EMRI.FIRMA_KODU = URETIM_BILGISI.FIRMA_KODU şekline dönüştürmek sorunu büyük ölçüde çözecektir. URETIM_EMRI tablosunda FIRMA_KODU alanının da olduğunu düşünürsek sorgun şu şekile gelecektir;

Kod: Tümünü seç

SELECT
  FIRMALAR.FIRMA_ADI, iplik_ne, iplik_tipi, parti_no, coalesce(sum(sıparıs_mktr),0) as sıparıs_mktr,  
  (select coalesce(sum(net_uretım),0) 
   from uretim_bilgisi  
   where URETIM_BILGISI.FIRMA_KODU = URETIM_EMRI.FIRMA_KODU and uretım_emrı.ıplık_ne=uretım_bılgısı.ıplık_ne and uretım_emrı.ıplık_tıpı=uretım_bılgısı.ıplık_tıpı and (uretım_emrı.partı_no=uretım_bılgısı.partı_no or (uretım_emrı.partı_no is null and uretım_bılgısı.partı_no is null)) and uretım_bılgısı.uretım_trh>'01.04.2006 07:00' and uretım_bılgısı.uretım_trh<'11.04.2006 07:00') as net_uretım

-- ....

FROM URETIM_EMRI, FIRMALAR 

WHERE URETIM_EMRI.FIRMA_KODU = FIRMALAR.FIRMA_KODU

GROUP BY fırma_adı, ıplık_ne, ıplık_tıpı, partı_no 

Tüm bunlara rağmen, sorguda binlerce kayıtın seçilmesi de (çünkü URETIM_EMRI tablosu için herhangi bir şart / where verilmemiş), yavaşlamaya sebep olacaktır

[sahinemrah44]

Merhaba kopyalarken dediğin gibi i-ı olmuş.
URETIM_EMRI tablom master ve bir şart belirtmedim çünkü formda runtime kullanıcı oluşturuyor sorgu orada 4 alana göre karşılaştırma yapılmış. kullanıcı bunu isterse 15 alana kadar çıkara biliyor(Sql kodu 7 sayfa oluduğu için 4 alana göre sogulayıp gönderdim) firma kodunu kullan demişsin onu öyle kullana bilirim lakin kullanıcı firmayı seçmedende sorgulama yapabiliyor böyle olunca yine hız sorunu yaşıyorum ve URETIM_EMRI tablomda 2.000 kayıt var. asıl diğer detay tablolarda çok kayıt var mesala URETIM_BILGISI tablomda 158.000 kayıt var. sorgulama yapılan tüm alanlar indexli. aslında 20-25 saniye sorguya razıyım ama ibquery kapatmak ve son kayıta gitmek 2 dakikamı alıyor.sanki sebebi temp dizininde oluşturduğu sort dosyaları gibi geliyor hardisk üzerinde okumaya çalışıyor oyüzden çok yavaş bir türülü ram kullanarak(hardiske sort dosyaları oluşturmadan) sorgulama yaptıramadım sadece ilk sefer ram kullanımı birden artı fakat daha sonra ki sorgularda yine ram yükselmedi temp dizininde dosya oluşturmaya devam ediyor.
İyi çalışmalar.

[Hakan Can]

Query'yi şu şekilde test edebilir misiniz:

Kod: Tümünü seç

SELECT
  T1.firma_adi,
  T1.iplik_ne,
  T1.iplik_tipi,
  T1.parti_no,
  coalesce(sum(T1.siparis_mktr),0) as siparis_mktr,
  coalesce(sum(T2.net_uretim),0) as net_uretim,
  coalesce(sum(T2.brut_uretim),0) as brut_uretim,
  coalesce(sum(T3.net_sevk),0) as net_sevk,
  coalesce(sum(T3.brut_sevk),0) as brut_sevk,
  coalesce(sum(T4.net_uretim),0) as net_ambar,
  coalesce(sum(T4.brut_uretim),0) as brut_ambar
FROM URETIM_EMRI T1
  LEFT JOIN uretim_bilgisi T2 ON T1.firma_adi=T2.firma_adi and T1.iplik_ne=T2.iplik_ne and T1.iplik_tipi=T2.iplik_tipi and (T1.parti_no=T2.parti_no or (T1.parti_no is null and T2.parti_no is null)) and T2.uretim_trh>'01.04.2006 07:00' and T2.uretim_trh<'11.04.2006 07:00'
  LEFT JOIN sevk_bilgisi T3 ON T1.firma_adi=T3.firma_adi and T1.iplik_ne=T3.iplik_ne and T1.iplik_tipi=T3.iplik_tipi and (T1.parti_no=T3.parti_no or (T1.parti_no is null and T3.parti_no is null))
  LEFT JOIN ambar T4 ON T1.firma_adi=T4.firma_adi and T1.iplik_ne=T4.iplik_ne and T1.iplik_tipi=T4.iplik_tipi and (T1.parti_no=T4.parti_no or (T1.parti_no is null and T4.parti_no is null))
GROUP BY T1.firma_adi, T1.iplik_ne, T1.iplik_tipi, T1.parti_no
ORDER BY T1.firma_adi, T1.iplik_ne, T1.iplik_tipi, T1.parti_no

Sonundaki ORDER BY'ı kaldırıp da test edebilirsiniz.

Aslında geçen süre çok yüksek; nedeni de karşılaştırmada OR kullanmanız. Normalde tablolar arasında birebir düzgün bir ilişkilendirme olsaydı bu query 1-2 saniye sürmesi gerekirdi ve OR kullanmanıza gerek kalmazdı. Zira dönecek maksimum kayıt sayısı 2000. (Birebir ilişkilendirmede bilhassa tek alanlı Primary Key tanımlamasını kastediyorum.)

Hatta isterseniz OR olan bölümleri kaldırıp da bir test edin. Gerçi sonuçta dönen Query aynı olmayabilecek ama en azından hız sorununun nedeni anlaşılabilir.

İyi çalışmalar.

[sahinemrah44]

daha önceden join ve union kodlarını denemiştim lakin çok yavaştı en hızlısı yine yukarıdaki yazdığım sql kodu. yinede sizin vermiş olduğunuz kodu ibexpert de çalışıtırdım
order by ile sorgu sonucu 1 dk. 15 sn sürdü
order by olmadan denedim 1 dk. 10 sn sürdü.
or kullanmamın sebebi tablolardaki kayıtların birbirini tutmaması değil karşılaştırma yapılan alan eğer null ise(tüm tablolarda aynı) toplam vermiyor formda bir yerde bu tür bir örnek verilmişti hatta M$ql bu şekile *= kullanılıyor null lar için ancak firebride olmadığı için or kullandım.
runtime alan seçimi olduğu için alanlar az seçilirse sorgu daha hızlı sonuç veriyor.sorgu sonucunun dönmesi okadar çok yavaş değil idare ediyor(daha iyi olabilir) lakin son kayıta gitmem ve Ibquery,ibexperti kapatmak çook uzun sürüyor rahat 1-1.5 dakika sürüyor.

İyi çalışmalar

[Hakan Can]

Sizin kendi query'nizi de ORDER BY'lı ve ORDER By'sız IBExpert'te test eder misiniz? (Çünkü IBX tabloları normalde Query'nin hepsini döndürmüyor. Last deyince Query tamamen çalışıyor gibi...)

Bir de sizin kullandığınız bu tablolarda REFERENTIAL INTEGRITY mevcut mu? Normalde klasik RDBMS mantığına göre bu tabloları dizayn etmeniz gerekiyor.

Gerçi yapıyı değiştirmek zor diyorsanız ki zor galiba o zaman zaten tavsiye edileni yapın yani kullandığınız her karşılaştırma alanı için indeks oluşturun (eğer yapmadıysanız). Bunun dışında da fazla bir alternatif yok galiba.

İyi çalışmalar.

[sahinemrah44]

Merhaba hakan bey tüm tablolarımda PK, FK lar mevcut. ordey by sız kullandığımda falza bir fark göremedim. dediğinz gibi tüm kayıtlar çekilmiyor lakin fazla kayıtta yok yani sorgu sonucu gelen kayıtlar 100-500 arası değişiyor ama son kayıt dediği zaman 1-5 dakika sürüyor veya Ibquery kapatmak isteiğimde yaklaşık bu kadar sürüyor.son kayıda gidip Ibqurey kapmak istersem hemen kapatıyor çünkü 1-1.5 dakikada tüm kayıtları çekmiş oluyor ama anlamadığım şey ben kapatmak istemem rağmen niye bu kadar uzun sürüyor yinemi tüm kayıtları çekiyor ve kayıt saysıda çok fazla değil 100-500 arası değişiyor.saki herşeyin nedeni temp dizinde oluturduğu geçici dosyadan kaynaklanıyor gibi geliyor bana hdd okuması uzun sürüyor gibi
İyi çalışmalar

[sahinemrah44]

Sanırım daha fazla yorum yok. ben bu sorun için hdd den şüpeleniyorum. hdd değiştireceğim scsı 15.000 rpm hdd aldım değiştirdikten sonra sonuclarını yazarım.
İyi çalışmalar.