Hi, sorry for a bit late reply. I had to rewrite your query to actually understand it, I'm not used to SELECT FROM (SELECT FROM (SELECT...
This is what I ended up with (though I am tired, so it wouldn't surprise me if I made some mistakes). WITH UBAZ as (SELECT AdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi FROM BAZLISTE WHERE BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'), UT as (SELECT AdresKodu , IslemTuru, BelgeNo, Barkod, OlcuBirimi, SUM(IIF(TekParca='H', Miktar, 0)) AS Miktar FROM TERMINAL_SAYIM WHERE BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA' GROUP BY BelgeNo, Barkod, OlcuBirimi, IslemTuru, AdresKodu), UNB as (SELECT AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi FROM UBAZ UNION SELECT AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi FROM UT) SELECT UNB.AdresKodu "Adres Kodu", UNB.IslemTuru "İşlem Türü", UNB.BelgeNo "Belge No", UNB.barkod, UNB.olcubirimi "Ölçü Birimi", coalesce(UT.Miktar,0) "Okutulan Miktar", coalesce(UBAZ.Miktar, 0) "Baz Miktar", coalesce(UT.Miktar, 0) - coalesce(UBAZ.Miktar, 0) Fark, URUN.UrunKodu "Ürün Kodu", URUN.UrunAciklamasi "Ürün Açıklaması", URUN.UrunGrubu "Ürün Grubu", URUN.Renk, URUN.Beden, URUN.Cup FROM UNB LEFT JOIN URUN ON UNB.olcubirimi = URUN.OlcuBirimi AND UNB.barkod = URUN.Barkod LEFT JOIN UBAZ ON UNB.olcubirimi = UBAZ.olcubirimi AND UNB.barkod = UBAZ.Barkod LEFT JOIN UT ON UNB.olcubirimi = UT.olcubirimi AND UNB.barkod = UT.Barkod Then I looked at your PLAN: PLAN JOIN (JOIN (JOIN (SORT (BAZKARSILASTIRMA DBOVWUNB BAZLISTE INDEX (RDB$PRIMARY3), BAZKARSILASTIRMA DBOVWUNB TERMINAL_SAYIM NATURAL), BAZKARSILASTIRMA URUN INDEX (RDB$PRIMARY2)), BAZKARSILASTIRMA DBOVWUBAZ BAZLISTE INDEX (RDB$PRIMARY3)), SORT (BAZKARSILASTIRMA DBOVWUT TERMINAL_SAYIM NATURAL)) and compared the indexes to your table definitions. The first thing I notice, is that RDB$PRIMARY3 = PRIMARY KEY (BELGENO,BARKOD,ADRESKODU,ISLEMTURU) I dislike this key for two reasons: (a) It contains fields which have some meaning attached to it. This may be OK for now, but it is more difficult to modify fields later on if field definitions change (e.g. if ADRESKODU is increased to 40 characters). Hence, I always prefer to use meaningless integer fields for primary key. (b) Composite indexes can hide problems. Generally, unlike many other databases, Firebird has no problems using several indexes for each table and combining several fields in one index is only a bit faster than having indexes on individual fields. In your case, you would probably benefit from having four separate indexes rather than one combined index (I'm calling your primary key for a combined index here). The reason is that your query can use the index for BELGENO, but since BARKOD is the next field in the index and BARKOD is not part of your WHERE clause, nothing more can be used from it (i.e. AdresKodu and IslemTuru doesn't benefit from this index. if you had had separate indexes for each field, Firebird could have used indexes for both barkod, AdresKodu and IslemTuru. TERMINAL_SAYIM is NATURAL, I would recommend adding indexes for at least AdresKodu, BelgeNo and possibly IslemTuru (probably also an index for Barkod, although that one is irrelevant for this query). URUN seems good (well, apart from point (a) that I made above about the PK having some meaning). Doing these changes, I'd expect your query to perform significantly faster. HTH, Set
