Title: Comparative productivity of access means to FireBird/Interbase
Question: Comparative productivity of access means to FireBird/Interbase
Answer:
Comparative productivity of access means to FireBird/Interbase
Comparative productivity of access means to FireBird/Interbase.
First I would like to say that this article just expresses an opinion of a
group of programmers and it does not claim to be indisputable. Still while writing
this article, I was trying to make it as unprejudiced as possible.
Before starting any serious project every developer evaluates the productivity
of different libraries of access to FireBird/Interbase. From the very beginning
we also have been doing the same because all existent products are permanently
developed. Here I would like to examine almost all similar libraries of direct
access to FireBird/Interbase such as FIBPlus, IBX, IBObjects and ZeosDB.
At first, we are interested in performance of FIBPlus in comparison to other
libraries. But judging by our tests it becomes clearer how some products correlate
with the rest ones. For measuring time we used the standard GetTicksCount function.
Our analysis went through the following stages:
Small selections (less than 20 records)
Middle selections (about 300-350 records)
Big selections (more than 10000 records taken from a table with a big number
of fields)
We used the following tables:
1. Table #1
CREATE TABLE CITIES (
ID_CITI CHAR(3) NOT NULL,
KOD_COUNTRY CHAR(3) NOT NULL,
NAME_E CHAR(50) NOT NULL,
NAME_R CHAR(50) NOT NULL);
/* Primary keys definition */
ALTER TABLE CITIES ADD CONSTRAINT PK_CITI PRIMARY KEY (ID_CITI);
379 records.
2. Table #2
CREATE TABLE RC_COUPONS (
ID INTEGER NOT NULL,
AIRLINE_CODE CHAR(4) default 204 NOT NULL,
SERIAL_NO CHAR(15),
DOC_TYPE INTEGER default 1 NOT NULL,
COUPON_NO INTEGER NOT NULL,
FLIGHT_ID INTEGER,
FLIGHT_NO CHAR(4),
FLIGHT_DATE DATE,
CONTROLLED INTEGER,
DATE_SALE DATE,
DOC_CURR CHAR(3),
FARE CHAR(5),
DISCOUNT CHAR(5),
QUANTITY INTEGER,
PRICE_USD_FARE NUMERIC(15,2),
PRICE_USD_TAX NUMERIC(15,2),
PRICE_UAH_FARE NUMERIC(15,2),
PRICE_UAH_TAX NUMERIC(15,2),
COMISSION_USD NUMERIC(9,2),
COMISSION_UAH NUMERIC(9,2),
RA_DOCUMENT_ID INTEGER,
FAREDISCOUNT CHAR(10),
VALIDATOR_ID INTEGER,
TYPE_KIND INTEGER default 1 NOT NULL,
COMISSION NUMERIC(15,2),
PRICE_FARE NUMERIC(15,2),
PRICE_TAX NUMERIC(15,2),
COUPON_CURR CHAR(3),
INVOICE_ID INTEGER,
ACCOUNT_AIRLINE_CODE CHAR(4),
COMISSION_PERCENT NUMERIC(9,2),
ID_RACPN INTEGER NOT NULL);
/* Primary keys definition */
ALTER TABLE RC_COUPONS ADD CONSTRAINT RC_COUPONS_PK PRIMARY KEY (ID, AIRLINE_CODE);
So it is obvious that unlike the second table the first one has rather small
number of records and fields.
FIBPlus and ZeosDB.
For comparison we took the following analogue components: TpFIBDataSet from
FIBPlus and TZIbSqlQuery from ZeosDB. The data were taken from the first table:
SELECT
CIT.ID_CITI,
CIT.KOD_COUNTRY,
CIT.NAME_E,
CIT.NAME_R
FROM CITIES CIT
WHERE CIT.ID_CITI
This query returns 18 records. The series of 100 queries with fetching all
data (Fetch All) returned the following results:
FIBPlus: 410 ticks
ZeosDB: 3800 ticks
FIBPlus surpasses ZeosDB almost in 10 times.
SELECT
CIT.ID_CITI,
CIT.KOD_COUNTRY,
CIT.NAME_E,
CIT.NAME_R
FROM CITIES CIT
This query returns 359 records. The series of 100 queries with fetching all
data (Fetch All) returned the following results:
FIBPlus: 3600 ticks
ZeosDB: 5600 ticks
So now we see that FIBPlus has higher productivity and these results are convincing.
In the last test we used a query with a big number of result records taken
from the second table:
Select * from RC_COUPONS
We had the series of 10 queries with fetching all data:
FIBPlus: 36200 ticks
ZeosDB: 35800 ticks
These results show small superiority of ZeosDB.
FIBPlus and IBX.
Here above all there are compared two pairs of components: TpFIBDataSet and
TpFIBQuery from FIBPlus and their analogues TIBDataSet and TIBSQL from IBX.
Taking the same table and making comparison we can see that depending on different
query conditions the productivity of FIBPlus is 15-45% higher than that of IBX.
It is necessary to mention that for TpFIBDataSet and TIBDataSet the productivity
of FIBPlus is the highest. The comparison of TpFIBDataSet with TIBQuery and
TIBTable also shows higher results in FIBPlus favour.
FIBPlus and IBObjects.
Then we tested performance using TpFIBDataSet (FIBPlus) and TIBOQuery (IBO).
The comparison of FIBPlus with IBObjects required more precise approach because
the author of IBObjects uses a non-standard approach to data receiving. It is
also necessary to remember that the comparison was done only with the help of
those components, which are the descendants of TDataSet. Besides IBO can run
queries in different modes. We can switch over these modes setting a value of
the TIBOQuery.FetchWholeRows property.
TIBOQuery.FetchWholeRows = false. IBOQuery executes a query that returns
values of the primary key. Values of other fields are returned when navigating
in IBOQuery.
IBOQuery.FetchWholeRows = true. IBOQuery executes an exact query described
in IBOQuery.SQL.Text. All data are placed in the dataset cache.
Taking into account all the above-mentioned we tested twice the performance
of both FIBPlus and IBO with different values of FetchWholeRows.
Note: By IBO(F) we mean that TIBOQuery.FetchWholeRows = true.
At the first stage we took a small number of records:
SELECT
CIT.ID_CITI,
CIT.KOD_COUNTRY,
CIT.NAME_E,
CIT.NAME_R
FROM CITIES CIT
WHERE CIT.ID_CITI
This query returns 18 records. It was executed 100 times with fetching all
data.
FIBPlus : 410 ticks
IBO : 1700 ticks
IBO(F) : 900 ticks
The second stage comprised selections of a middle number of records:
SELECT
CIT.ID_CITI,
CIT.KOD_COUNTRY,
CIT.NAME_E,
CIT.NAME_R
FROM CITIES CIT
This query returns 359 records and it was executed 100 times with fetching
all data.
FIBPlus: 3600 ticks.
IBO: 4050 ticks.
IBO(F): 4080 ticks.
At the last stage there was taken a big number of records from table 2:
Select * from RC_COUPONS
We had a series of 10 queries with fetching all data.
FIBPlus : 36200 ticks
IBO : 9050 ticks
IBO(F) : 30600 ticks
Results.
Now let's try to sum up our results:
20
350
10000
FIBPlus
410
3600
36200
Zeos
3800
5600
35800
IBX
800
5800
44900
IBO
1700
4050
9050
IBO(F)
900
4080
30600
% of the winner's gap from the nearest
48%=100*(800-410)/800
18%=100*(4050-3600)/ 4050
74.7%=100*(35800-9050)/ 35800
% of FIBPlus loss in comparison with the winner
0
0
75%=100*(36200-9050)/ 36200
As you can see these results do not have a single meaning. By no means we want
to thrust our opinion on you but we would like to share our conclusions with
you:
IBObjects is more productive when dealing with the queries, which have larger
number of records. One can dispute about value of this superiority. Besides
we would not take into consideration the results for IBO(F) because:
this capability is possible in very rare cases such as for queries from
a single table or from a view, which has no DISTINCT or UNION commands.
in this case IBObjects does not finish its work. If we navigate in a dataset
the server load increases. As a result the total server load will be bigger
than anywhere else. Dealing with small and middle queries FIBPlus shows the
best results.
Again I want to emphasize that this article just expresses an opinion of a
group of developers. And it depends whether you would like to regard or disregard
IBO(F) results.
But it goes without saying that FIBPlus becomes the best dealing with small
and middle queries.
These results are very important! Our experience shows that most frequent queries
are those, which return a small and middle number of result records.
Moreover in most cases if queries return more than 10000 records it means that
the client software is wrongly designed. Though even in the cases if such queries
are really necessary, they are very rare. So if we agree with this statement
it becomes clear from the results of the comparison that at present FIBPlus
is the best.
I would be very glad to receive your comments about this article because we
want to get the most exact comparison results. To our mind at present FIBPlus
is the most effective means of access to FireBird/Interbase (dealing with the
Client/Server technology).