Hi Salih,
The query has following where clause
WHERE A.F_FIRMA_KOD ='02200'
AND B.F_FIRMA_KOD ='02200'
AND A.TARIH<='2006-01-14'
AND A.TARIH>='2006-01-01'
AND A.IPTAL='h'
AND B.F_HESAP_KOD>='1000100100001'
AND B.F_HESAP_KOD<='3000100100001'
AND B.ISLEM_TIP<>'1'
Can you pleae modify it to
WHERE A.F_FIRMA_KOD = B.F_FIRMA_KOD
AND B.F_FIRMA_KOD ='02200'
AND A.TARIH between '2006-01-14' and A.TARIH>='2006-01-01'
AND A.IPTAL='h'
AND B.F_HESAP_KOD between '1000100100001' and '3000100100001'
AND B.ISLEM_TIP<>'1'
Also (B.ISLEM_TIP field does not have too many distinct values then use
B.ISLEM_TIP IN ('<value1>','<value2>'.....)
Regards,
Abhishek.
-----Original Message-----
From: s [mailto:db2udbdba-ezmlmshield-x90849707.[Email address protected]
Sent: Thursday, December 14, 2006 12:54 PM
To: LazyDBA Discussion
Subject: This Query Hangs :((... Urgent
***********************
Your mail has been scanned by InterScan.
***********-***********
I can't found out why.....
SELECT
SUM(B.FIRMA_BORC_TUTAR) AS TOPLAM_BORC,
SUM(B.FIRMA_ALACAK_TUTAR) AS TOPLAM_ALACAK , B.F_HESAP_KOD AS HESAP_KOD,B.HESAP_TANIM AS HESAP_TANIM, SUM(CASE WHEN DAYS('2006-12-14')-DAYS(A.TARIH)<15 THEN B.FIRMA_BORC_TUTAR ELSE 0 END) AS BORC_0_15, SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<30) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=15) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_15_30,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<45) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=30) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_30_45,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<60) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=45) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_45_60,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<90) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=60) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_60_90,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<120) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=90) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_90_120,
SUM(CASE WHEN DAYS('2006-12-14')-DAYS(A.TARIH)>=120 THEN B.FIRMA_BORC_TUTAR ELSE 0 END) AS BORC_120,SUM(CASE WHEN
DAYS('2006-12-14')-DAYS(A.TARIH)<15 THEN B.FIRMA_ALACAK_TUTAR ELSE 0 END) AS ALACAK_0_15, SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<30) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=15) ) THEN B.FIRMA_ALACAK_TUTAR ELSE 0
END) AS ALACAK_15_30,SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<45)
AND (DAYS('2006-12-14')-DAYS(A.TARIH)>=30) ) THEN B.FIRMA_ALACAK_TUTAR ELSE 0 END) AS ALACAK_30_45, SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<60) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=45) ) THEN B.FIRMA_ALACAK_TUTAR ELSE 0
END) AS ALACAK_45_60,SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<90)
AND (DAYS('2006-12-14')-DAYS(A.TARIH)>=60) ) THEN B.FIRMA_ALACAK_TUTAR ELSE 0 END) AS ALACAK_60_90, SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<120) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=90) ) THEN B.FIRMA_ALACAK_TUTAR ELSE 0
END) AS ALACAK_90_120,SUM(CASE WHEN DAYS('2006-12-14')-DAYS(A.TARIH)>=120
THEN B.FIRMA_ALACAK_TUTAR ELSE 0 END) AS ALACAK_120, B.F_FIRMA_MALIYET_MERKEZ_KOD AS MALIYET_MERKEZ, COALESCE(F_MUSTERI_GRUP_KOD,'--' ) AS MUSTERI_GRUP, COALESCE(F_SATIS_DEPT_KOD,'--' ) AS SATIS_DEPT FROM FINANS.MUHASEBE_FIS AS A, FINANS.MUHASEBE_FIS_KALEM AS B WHERE A.F_FIRMA_KOD ='02200' AND B.F_FIRMA_KOD ='02200'
AND A.TARIH<='2006-01-14' AND A.TARIH>='2006-01-01'
AND A.IPTAL='h'
AND B.F_HESAP_KOD>='1000100100001'
AND B.F_HESAP_KOD<='3000100100001'
AND B.ISLEM_TIP<>'1'
GROUP BY (B.F_HESAP_KOD,B.HESAP_TANIM,B.F_FIRMA_MALIYET_MERKEZ_KOD
,COALESCE(F_MUSTERI_GRUP_KOD,'--' ),COALESCE(F_SATIS_DEPT_KOD,'--' )) ORDER BY B.F_HESAP_KOD
I have 2 tables ... Finans.Muhasebe_Fis A ,Finans.Muhasebe_Fis_Kalem B
Finans.Muhasebe_Fis has >250.000 rows and Finans.Muhasebe_Fis_Kalem has
>3.000.000 rows... (Both of the tables have more than 50 columns)
A has an well reorganized index F_firma_kod,Tarih,Iptal...... and B
has F_firma_kod , F_muhasebe_fis_no (this field is attached to finans.muhasebe_fis via foreign key),islem_tip,f_hesap_kod
When I "SQL EXPLAIN" the query the diagram shows me that the indexes are
used all right and the timeron is 7434,43253 etc...
But we can't obtain the results although we have been waiting for a long time.There are sum functions in the query.Could it be the reason for that?
What must I do to improve the performance???
Our system is AIX , DB2 7.2.9 ..... with 4 GB of Ram...
Salih Sipahi
Veritabanı Yöneticisi
Hitit Güneşi
Hattusas
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To unsubscribe: http://www.lazydba.com/unsubscribe.html
--------------------------------------------------------
NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.
DB2 & UDB email list listserv db2-l LazyDBA home page