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: [email protected] [mailto:[email protected]] Sent: Saturday, January 23, 2016 1:42 AM To: [email protected] 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
