Mega Code Archive

Comparative productivity of access means to FireBirdInterbase

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