Accelerate XAF application on Oracle database 40 times | Xafari Framework

When Xafari applications on Oracle are used for large amounts of data, there has been some decline in the performance compared with the same application which runs on MS-SQL. Analysis of the problem showed that indices did not participate in complex queries in the database, although these indices were present in the database. Deeper localization revealed the cause for it.

Field "Oid" of most business classes have type Guid. When mappings of business classes into the Oracle database, these fields will have the type CHAR. When the application ran, complex parameterized queries raised with the conditions under which the transfer was carried out with the parameter type NVARCHAR2 to columns of type CHAR. It is for this reason the indices had not been picked up, since in the queries of such types Oracle performed an internal conversion from one type to another.

Thanks to a very flexible architecture of Devexpress XAF, which provides many extension points, this problem has been solved independently. To solve this problem, we developed module Xafari.DB, which presents the implementation of providers for data access Oracle: XafariODPConnectionProvider and XafariOracleConnectionProvider.

These classes are the derived classes of the DevExpress classes ODPConnectionProvider and OracleConnectionProvider, respectively. We had to override a method CreateParameter () in base classes so that for fields of type Guid parameter passing in the query is executed with the type CHAR. Below is the analysis of the results obtained in the comparison: without using the module Xafari.DB and with the connected Xafari.DB.

In general, due to Xafari.DB we were able to accelerate the execution of complex queries to 40 times or more.

The results of testing on a real application.
For testing, we chose the application where the problem is most marked. This is application Galaktika AMM.

The results are shown in the table below:

SQLquery Tables involved in the query Runtime seconds acceleration time
Table Number of records without Xafari.DB with Xafari.DB
№1 Position Expenditure norms
ProductOutNorm
797188 2230 48 47
Nomenclature Position
Product
232534
Position Bill of material (BOM) 27515
Document 76
№2 Basic Technology 2311603 3 59
File Libraries 87
№3 Basic Technology 2311603 241 6 40
Nomenclature Position 232`534
File Libraries 87
Document 76

Here are the queries themselves, and for the first query more detailed information on testing has been given.

SQL query №1 without using Xafari.DB module has the following query plan:

Analyzing the query execution plan, we can see that in our complex query a complete listing of all the rows in the table (TABLE ACCESS full) takes place. Access Method TABLE ACCESS full arises in the absence of an index, or for reasons that the built index has not been picked up. In our case, the index was present, but it was not picked up because of the call of the inner function Oracle SYS_OP_C2C. This function performs the conversion of one type to another.
Now examine the query execution plan, but with the connected Xafari.DB:

With an attached unit Xafari.DB, the index is picked up, the query runs faster.

Fig. 1 - Time SQL query №1 without module Xafari.DB

Fig. 2 - Time of SQL query №1, but with the module Xafari.DB