FirebirdSQL
OuterJoinConversion parameter set to True decreases Firebird performance
Official Firebird 3.0.13.
select T.NAZWA from TECHNOLOGIA T join OPERACJATECH OT on (T.ID_TECHNOLOGIA = OT.ID_TECHNOLOGIA) left outer join OPERACJATECHBOM OB on (OB.ID_OPERACJATECH = OT.ID_OPERACJATECH) left outer join BOMITEM_VIEW BI on (BI.ID_BOM = OB.ID_BOM) where BI.RODZAJ in (0, 1, 2) and T.STATUS = 1
Plan:
PLAN JOIN (JOIN (JOIN (T INDEX (MK_TECHNOLOGIA_STATUS), OT INDEX (FK_OPERACJATECH_TECHNOLOGIA)), OB INDEX (FK_OPERACJATECHBOM_OPERACJATECH)), JOIN (JOIN (BI BI INDEX (FK_BOMITEM_BOM), BI K INDEX (PK_KARTOTEKA)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI BI INDEX (FK_BOMITEM_BOM), BI F INDEX (PK_FANTOM)), BI M INDEX (PK_MAGAZYN)))
Official Firebird 5.0.3:
Plan:
PLAN HASH (JOIN (HASH (BI BI NATURAL, BI K NATURAL), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI F NATURAL, BI BI INDEX (FK_BOMITEM_FANTOM)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (T INDEX (MK_TECHNOLOGIA_STATUS), OT INDEX (FK_OPERACJATECH_TECHNOLOGIA)), OB INDEX (FK_OPERACJATECHBOM_OPERACJATECH)))
With parameter set to False, everything works as expected.
Below the DDL of the view:
CREATE OR ALTER VIEW BOMITEM_VIEW( ID_BOMITEM, ID_BOM, ID_BOMITEM_MAIN, ID_KARTOTEKA, ID_FANTOM, RODZAJ, LP, INDEKS, NAZWASKR, NAZWA, RODZAJMAT, ILOSC, WARTOSC, NARZUT, RODZAJCENY, AKTYWNY, WARIANTOWY, OPCJONALNY, OPCJAWYBRANA, ROZLICZBEZDOK, KARTOTEKA, ID_MAGAZYN, NAZWAMAG, POZIOM, ID_WYBRANYWARIANT, NIEWIDOCZNY, UZGODNIONY, CENA, RODZAJREALIZ, ZAPLANDOST, CZASOCZEKIWANIA) AS select BI.ID_BOMITEM, BI.ID_BOM, BI.ID_BOMITEM_MAIN, K.ID_KARTOTEKA, null, BI.Rodzaj, BI.Lptree, K.INDEKS, K.NAZWASKR, K.NAZWADL, BI.RODZAJMAT, BI.ILOSC, BI.WARTOSC, BI.NARZUT, BI.RODZAJCENY, BI.AKTYWNY, BI.WARIANTOWY, BI.OPCJONALNY, BI.OPCJAWYBRANA, BI.ROZLICZBEZDOK, BI.KARTOTEKA, M.ID_MAGAZYN, M.NAZWAMAG, BI.POZIOM, BI.ID_BOMITEMWYBRANY, BI.NIEWIDOCZNY, BI.UZGODNIONY, BI.CENA, BI.RODZAJREALIZ, BI.ZAPLANDOST, BI.CZASOCZEKIWANIA from BOMITEM BI join KARTOTEKA K on (K.ID_KARTOTEKA = BI.ID_KARTOTEKA) left outer join MAGAZYN M on (M.ID_MAGAZYN = BI.ID_MAGAZYN) union all select BI.ID_BOMITEM, BI.ID_BOM, BI.ID_BOMITEM_MAIN, null, F.Id_Fantom, BI.Rodzaj, BI.Lptree, F.INDEKS, F.INDEKS, F.Nazwa, BI.RODZAJMAT, BI.ILOSC, BI.WARTOSC, BI.NARZUT, BI.RODZAJCENY, BI.AKTYWNY, BI.WARIANTOWY, BI.OPCJONALNY, BI.OPCJAWYBRANA, BI.ROZLICZBEZDOK, BI.KARTOTEKA, M.ID_MAGAZYN, M.NAZWAMAG, BI.POZIOM, BI.ID_BOMITEMWYBRANY, BI.NIEWIDOCZNY, BI.UZGODNIONY, BI.CENA, BI.RODZAJREALIZ, BI.ZAPLANDOST, BI.CZASOCZEKIWANIA from BOMITEM BI join FANTOM F on (F.Id_Fantom = BI.Id_Fantom) left outer join MAGAZYN M on (M.ID_MAGAZYN = BI.ID_MAGAZYN) ;
What performance would be (in both FB versions) if the query is rewritten as:
select T.NAZWA
from TECHNOLOGIA T
join OPERACJATECH OT on (T.ID_TECHNOLOGIA = OT.ID_TECHNOLOGIA)
join OPERACJATECHBOM OB on (OB.ID_OPERACJATECH = OT.ID_OPERACJATECH)
join BOMITEM_VIEW BI on (BI.ID_BOM = OB.ID_BOM)
where BI.RODZAJ in (0, 1, 2) and T.STATUS = 1
?
3.0.13: PLAN JOIN (JOIN (JOIN (JOIN (JOIN (BI K NATURAL, BI BI INDEX (FK_BOMITEM_KARTOTEKA)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI F NATURAL, BI BI INDEX (FK_BOMITEM_FANTOM)), BI M INDEX (PK_MAGAZYN)), OB INDEX (FK_OPERACJATECHBOM_BOM)), OT INDEX (PK_OPERACJATECH)), T INDEX (PK_TECHNOLOGIA))
On our servers the problem is not as obvious as it is on the customer's server, but: Firebird 3.0.13: ------ Performance info ------ Prepare time = 16ms Execute time = 31ms Avg fetch time = 1,41 ms Current memory = 46 145 920 Max memory = 46 185 632 Memory buffers = 2 048 Reads from disk to cache = 2 409 Writes from cache to disk = 4 Fetches from cache = 229 731
Firebird 5.0.3: ------ Performance info ------ Prepare time = 16ms Execute time = 172ms Avg fetch time = 7,82 ms Current memory = 56 549 520 Max memory = 56 577 648 Memory buffers = 2 048 Reads from disk to cache = 489 Writes from cache to disk = 0 Fetches from cache = 90 560
Customer has less efficient server and there the original query was hung for minutes.
Sorry, IBExpert prints those informations incorrectly. Below info from audit services: Firebird 5.0.3: 2000 records fetched, 1383 ms, 489 read(s), 90560 fetch(es) Firebird 3.0.13: 2000 records fetched, 285 ms, 2409 read(s), 4 write(s), 229601 fetch(es)
I wonder how is it possible that 489 physical reads can take more time than 2409? Cold FS cache?..
Any chance to get a database to test against?
No. I will try to somehow reproduce it on clean database.
Could such example help you to analyse this problem? I ran original query on freshly created database for our ERP system, with only few records in needed tables.
Provide execution statistics for this query.
select count(*)
from TECHNOLOGIA T
join OPERACJATECH OT on (T.ID_TECHNOLOGIA = OT.ID_TECHNOLOGIA)
left outer join OPERACJATECHBOM OB on (OB.ID_OPERACJATECH = OT.ID_OPERACJATECH)
left outer join BOMITEM_VIEW BI on (BI.ID_BOM = OB.ID_BOM)
where BI.RODZAJ in (0, 1, 2) and T.STATUS = 1
I suspect there are many more records there than the 2000 you're extracting. Statistics for all counters, except execution time, are better for 5.0.3.
40116 is the result. 3.0: PLAN JOIN (JOIN (JOIN (T INDEX (MK_TECHNOLOGIA_STATUS), OT INDEX (FK_OPERACJATECH_TECHNOLOGIA)), OB INDEX (FK_OPERACJATECHBOM_OPERACJATECH)), JOIN (JOIN (BI BI INDEX (FK_BOMITEM_BOM), BI K INDEX (PK_KARTOTEKA)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI BI INDEX (FK_BOMITEM_BOM), BI F INDEX (PK_FANTOM)), BI M INDEX (PK_MAGAZYN))) 1 records fetched 3192 ms, 2071 read(s), 4 write(s), 703339 fetch(es)
5.0: PLAN HASH (JOIN (HASH (BI BI NATURAL, BI K NATURAL), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI F NATURAL, BI BI INDEX (FK_BOMITEM_FANTOM)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (T INDEX (MK_TECHNOLOGIA_STATUS), OT INDEX (FK_OPERACJATECH_TECHNOLOGIA)), OB INDEX (FK_OPERACJATECHBOM_OPERACJATECH))) 1 records fetched 56299(!) ms, 2679 read(s), 1 write(s), 1061383 fetch(es)
With this query performance dropped much more significantly.
Where did this result come from? It's better in every metric except execution time.
Firebird 5.0.3: 2000 records fetched, 1383 ms, 489 reads, 90,560 fetch(s) Firebird 3.0.13: 2000 records fetched, 285 ms, 2409 reads, 4 writes, 229,601 fetch(s)
Where did this result come from? It's better in every metric except execution time.
Firebird 5.0.3: 2000 records fetched, 1383 ms, 489 reads, 90,560 fetch(s) Firebird 3.0.13: 2000 records fetched, 285 ms, 2409 reads, 4 writes, 229,601 fetch(s)
It's from this query:
select T.NAZWA from TECHNOLOGIA T join OPERACJATECH OT on (T.ID_TECHNOLOGIA = OT.ID_TECHNOLOGIA) join OPERACJATECHBOM OB on (OB.ID_OPERACJATECH = OT.ID_OPERACJATECH) join BOMITEM_VIEW BI on (BI.ID_BOM = OB.ID_BOM) where BI.RODZAJ in (0, 1, 2) and T.STATUS = 1
Ran one more time: 3.0: select T.NAZWA from TECHNOLOGIA T join OPERACJATECH OT on (T.ID_TECHNOLOGIA = OT.ID_TECHNOLOGIA) join OPERACJATECHBOM OB on (OB.ID_OPERACJATECH = OT.ID_OPERACJATECH) join BOMITEM_VIEW BI on (BI.ID_BOM = OB.ID_BOM) where BI.RODZAJ in (0, 1, 2) and T.STATUS = 1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ PLAN JOIN (JOIN (JOIN (JOIN (JOIN (BI K NATURAL, BI BI INDEX (FK_BOMITEM_KARTOTEKA)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI F NATURAL, BI BI INDEX (FK_BOMITEM_FANTOM)), BI M INDEX (PK_MAGAZYN)), OB INDEX (FK_OPERACJATECHBOM_BOM)), OT INDEX (PK_OPERACJATECH)), T INDEX (PK_TECHNOLOGIA)) 2000 records fetched 787 ms, 1574 read(s), 1 write(s), 229527 fetch(es)
5.0: select T.NAZWA from TECHNOLOGIA T join OPERACJATECH OT on (T.ID_TECHNOLOGIA = OT.ID_TECHNOLOGIA) join OPERACJATECHBOM OB on (OB.ID_OPERACJATECH = OT.ID_OPERACJATECH) join BOMITEM_VIEW BI on (BI.ID_BOM = OB.ID_BOM) where BI.RODZAJ in (0, 1, 2) and T.STATUS = 1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ PLAN JOIN (JOIN (JOIN (JOIN (HASH (BI BI NATURAL, BI K NATURAL), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI F NATURAL, BI BI INDEX (FK_BOMITEM_FANTOM)), BI M INDEX (PK_MAGAZYN)), OB INDEX (FK_OPERACJATECHBOM_BOM)), OT INDEX (PK_OPERACJATECH)), T INDEX (PK_TECHNOLOGIA)) 2000 records fetched 1404 ms, 489 read(s), 90560 fetch(es)
I have similar, but much more extreme example. With parameter set to false, this query takes one indexed read from 2 tables (OPERACJATECHBOM, OPERACJATECH).
select BI.ID_BOM, BI.ID_BOMITEM_MAIN, BI.ID_KARTOTEKA, BI.ID_FANTOM, BI.INDEKS, BI.NAZWA, BI.ID_BOMITEM, BI.RODZAJMAT, BI.ILOSC, BI.RODZAJCENY, BI.ID_MAGAZYN, BI.NAZWAMAG, BI.RODZAJ, BI.LP, J.JM, M.NRMAG from OPERACJATECH OT left outer join OPERACJATECHBOM OB on (OB.ID_OPERACJATECH = OT.ID_OPERACJATECH) left outer join BOMITEM_VIEW BI on (BI.ID_BOM = OB.ID_BOM) left outer join KARTOTEKA K on (K.ID_KARTOTEKA = BI.ID_KARTOTEKA) left outer join JM J on (J.ID_JM = K.ID_JM) left outer join MAGAZYN M on (M.ID_MAGAZYN = BI.ID_MAGAZYN) where ((OT.ID_OPERACJASL = 10084) and (OT.ID_TECHNOLOGIA is null) and (BI.RODZAJ = 0))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (OT INDEX (FK_OPERACJATECH_TECHNOLOGIA, FK_OPERACJATECH_OPERACJASL), OB INDEX (FK_OPERACJATECHBOM_OPERACJATECH)), JOIN (JOIN (BI BI INDEX (FK_BOMITEM_BOM), BI K INDEX (PK_KARTOTEKA)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI BI INDEX (FK_BOMITEM_BOM), BI F INDEX (PK_FANTOM)), BI M INDEX (PK_MAGAZYN))), K INDEX (PK_KARTOTEKA)), J INDEX (PK_JM)), M INDEX (PK_MAGAZYN))
With parameter set to true, this runs for over 50 seconds: PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (HASH (BI BI NATURAL, BI K NATURAL), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI F NATURAL, BI BI INDEX (FK_BOMITEM_FANTOM)), BI M INDEX (PK_MAGAZYN)), OB INDEX (FK_OPERACJATECHBOM_BOM)), OT INDEX (PK_OPERACJATECH)), K INDEX (PK_KARTOTEKA)), J INDEX (PK_JM)), M INDEX (PK_MAGAZYN))
just as a info for your results: always use the execute and fetch all function if you want to have reliable performance infos for multiline result sets
data that is displayed in ibexpert performance anaysis is exactly what we get from server
if you execute a query with 100000 result records in sql editor and not use execute and fetch all button, the result is only created for the amount of fetched records. if dbgrid shows for example 40 records, it might be the performance result for only fetching 40 records, not for 100000 records, but that might have already used internally 50% of all table records to find the first 40 records of result set.
all performance analysis results compared to whatever other platform result make only sense if both sides use the same execute and fetch all
or just use count(*) instead of records
Performance Analysis is completely fine. I'm talking about Performance info:. Query ran for 1 second to give results, yet you claim it took only 30 ms.
perhaps @dyemanov can explain it better, but what time firebird internally needs to execute the query (and that is what we get from firebird server itsself in that value) and have the buffer of results filled that can be fetched by a client is not the time until a external client receives all records including perhaps blobs etc until the data is transmitted over a slow network with high latency to show it in a grid.
if you need that, you might use an ibeblock and measure ibec_gettickcount before and after fetching all records with the select into in between. result will also be shown in grid but that gives a client based time
I don't know whether we understand each other. I run this query in local LAN, no delay, great communication. I run trace and audit services, run query and wait 1 second, IBExpert shows 30 ms, audit services show 1 second.
ok, if you compare values to what is shown in isql with set stats on and it shows the same as we do in ibexpert, we can go back to your first problem here.
if also set stats on shows other values than trace it is definitly a case for @dyemanov and as always best with a reproducable example database