Comparative tests showed that Xafari applications process large volumes of data with a significant decline in performance when being run on Oracle compared with the same applications running on MS-SQL. The analysis of the problem showed that the indexes were not involved in the process of forming complex requests to the database despite the fact these indexes existed in the database. Further investigation discovered the root cause of the problem.
Most business classes had Guid type in their Oid property fields. Such fields got CHAR type when being mapped into the Oracle database. Then, complex parametrized requests were emerging during the application activities. These requests included conditions that were passing some parameters with NVARCHAR2 to the columns of CHAR type making Oracle internally convert the types. That led to the described problem of the indexes not being utilized by the system.
This problem was solved independently thanks to a very flexible Devexpress XAF architecture providing many extension points. Xafari.DB module has been implemented. It contains two providers intended to access Oracle data: XafariODPConnectionProvider and XafariOracleConnectionProvider. They are the descendants of ODPConnectionProvider and OracleConnectionProvider. It was necessary to change the CreateParameter() method to keep CHAR type for the parameters of Guid property fields when the requests get to the queue.
In general, Xafari.DB implementation resulted in accelerating the execution of complex queries up to 40 times and more.