Hello,

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

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Saturday, January 23, 2016 1:42 AM
To: firebird-support@yahoogroups.com
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).

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