Subject RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help
Author Ertan Küçükoğlu

I needed some time to understand (at least try to understand) your post. Please, note that my SQL knowledge is very limited. I really don’t know internals of database any system. Though, I need to develop an application which can use Firebird, Interbase, MSSQL, MySQL, Oracle. Some of users databases may be Firebird, others MSSQL, and an Oracle maybe. User decides what to use.

Saying that;
- I wonder if it would be possible to make a view and decrease one SELECT depth in the SQL? This will make statement a little easier to read for sure. Or, that will be a problem in the long run? Some say views are not good. They are generated completely for all the data in TABLE not considering WHERE clauses. Not saved as TABLE data and generated at each run in a temp disk space/memory.
- Having some indexes added query now performs very nicely. Runs & fetches all records below one second. Cold run around 0.8 second, 2nd run right after cold is around 0.4 seconds.
- I read and re-read your comments on having separate indexes is better for SQL. However, I do need that “combined” primary index for my application, for sure. Though, I wonder if it will be of any help to define separate indexes for each field as you suggested? I don't know if that will be lots of indexes defined in the end. Especially considering other tables I need to define as application is still being developed and new TABLES are added. I could not understand what maybe the problem if a/some field(s) length in "combined" index is increased.
- Finally, I needed to test SQL statement that you re-write in other SQL databases running in Windows 10 x64bit In order to see if it is executing OK. All databases I use are Free to use versions. I don't do any test on Interbase since I don't have it installed on my development computer and assuming it will be very much same with Firebird. My observations:
Firebird: No problem.
MSSQL: No problem.
MySQL: ERROR near UBAZ (at very beginning)
Oracle: Error at Line: 7 Column: 17, ORA-00907: missing right parenthesis (line 7: SUM(IIF(TekParca='H', Miktar, 0)) AS Miktar)
- One last thing to tell as an information. Oracle was able to run my first posted statement with only Primary Keys and no additional indexes defined less than one second. This is nothing to be said for comparing Firebird and Oracle. It is just I cannot understand how Oracle handles things. Moreover, it is less than a month that I ever installed and used Oracle as a database. I always preferred Firebird as my first line of database.

Thanks & regards.

From: []
Sent: Saturday, January 23, 2016 1:42 AM
Subject: Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

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).

(SELECT AdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi
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
WHERE BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
GROUP BY BelgeNo, Barkod, OlcuBirimi, IslemTuru, AdresKodu),
UNB as
(SELECT AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
SELECT AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi

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
LEFT JOIN URUN ON UNB.olcubirimi = URUN.OlcuBirimi AND UNB.barkod =
LEFT JOIN UBAZ ON UNB.olcubirimi = UBAZ.olcubirimi AND UNB.barkod =
LEFT JOIN UT ON UNB.olcubirimi = UT.olcubirimi AND UNB.barkod =

Then I looked at your PLAN:


and compared the indexes to your table definitions. The first thing I
notice, is that

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

Doing these changes, I'd expect your query to perform significantly faster.