Subject | Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help |
---|---|
Author | setysvar |
Post date | 2016-01-22T23:41:53Z |
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
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