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:
178
| 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 |
797`188 | 2`230 | 48 | 47 |
| Nomenclature Position Product |
232`534 | ||||
| Position Bill of material (BOM) | 27`515 | ||||
| Document | 76 | ||||
| №2 | Basic Technology | 2`311`603 | 3 | 59 | |
| File Libraries | 87 | ||||
| №3 | Basic Technology | 2`311`603 | 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.
SELECT N0."Oid", N0."DateCreate", N0."Создатель",
N0."UserCreate", N0."DateModification",
N0."Writer", N0."UserModification",
N0."ExternalID", N0."ExternalID2", N0."ProductMan",
N1."ObjectType", N0."NumberProduct", N0."Component",
N2."ObjectType", N0."NumberActivity",
N0."Norm", N0."NormOnOrder", N0."Options",
N0."NormBeginEnable", N0."NormEndEnable",
N0."ManProduct", N3."ObjectType", N0."LimitOnNumber",
N0."NumberBegin", N0."NumberEnd", N0."Site",
N0."Comment", N0."Repair", N0."QtyLatter",
N0."Round", N0."FactorScrap",
N0."FactorUses", N0."Order",
N4."ObjectType", N0."OnlyForOrder",
N0."Processing", N0."AdviceNote",
N5."ObjectType", N0."NumberOnDrawing",
N0."BOMParagraph ", N0."BOMProduct",
N0."VersionBOMPP", N0."VersionBOMComponent",
N0."Offset", N0."MXDispatch", N0."BoxDispath",
N0."MXIssue", N0."BoxIssue",
N0."RuleManIssue",
N0."ImportantForStart", N0."EnableInRequirement",
N0."BaseInPack", N0."PecentSum",
N0."IndexNumber", N0."OptimisticLockField", N0."GCRecord"
FROM
(
(
(
(
("ProductBOM" N0
LEFT JOIN "Product" N1 ON (N0."ProductMan" = N1."Oid")
)
LEFT JOIN "Product" N2 ON (N0."Pack" = N2."Oid")
)
LEFT JOIN "Product" N3 ON (N0."ManProduct" = N3."Oid")
)
LEFT JOIN "Document" N4 ON (N0."Order" = N4."Oid")
)
LEFT JOIN "FileLibrary" N5 ON (N0."AdviceNote" = N5."Oid")
)
WHERE (N0."GCRecord" is null
AND (N0."ProductMan" = :p0)
AND (N0."VersionBOMPP" = :p1)
AND N0."ProductBOM" is null)
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
SELECT N0."Oid", N0."DateCreate", N0."Author",
N0."UserCreate", N0."DateModification",
N0."Writer", N0."UserModification",
N0."ExternalID", N0."ExternalID2", N0."DisplayName",
N0."Code", N0."Name", N0."LevelType",
N0."Box", N0."ActivityKind", N0."Duration",
N0."TyprDuration", N0." TimeOverlap",
N0."Parent", N1."ObjectType", N0."ProcessDescription ",
N2."ObjectType", N0." AdviceNote", N3."ObjectType",
N0."QtyGroupProduction",
N0."UOMTime", N0."TimePreparation",
N0."TimeFinished", N0."TimeCycle",
N0."UOMNorm", N0."QtyLot",
N0."CodeUniin", N0."PreviousStep",
N0."RelationType", N0."DisplacementMin",
N0."VertionGetTst", N0."FactorGetTst",
N0."Description", N0."MXOutMove", N0."BoxOutMove",
N0."MXIssue", N0."BoxIssue",
N0."OptimisticLockField", N0."GCRecord", N0."ObjectType"
FROM
(
(
("BaseTechnology" N0
LEFT JOIN "BaseTechnology" N1 ON (N0."Parent" = N1."Oid")
)
LEFT JOIN "BaseTechnology" N2 ON (N0."ProcessDefinition" = N2."Oid")
)
LEFT JOIN "FileLibrary" N3 ON (N0."AdviceNote" = N3."Oid")
)
WHERE (N0."GCRecord" is null
AND (N0. "Parent" = :p0))
SELECT *
FROM
(
SELECT (N0."Oid") as F0, (N0."DateCreate") as F1,
(N0."Author") as F2, (N0."UserCreate") as F3,
(N0."DateModification") as F4,
(N0."Writer") as F5, (N0."UserModification") as F6,
(N0."ExternalID") as F7, (N0."ExternalID2") as F8,
(N0."DisplayName") as F9,
(N0."Code") as F10, (N0."Name") as F11,
(N0."LevelType") as F12, (N0."Point") as F13,
(N0."ActivityType") as F14, (N0."Duration") as F15,
(N0."DurationType") as F16,
(N0."TimeOverlap") as F17,
(N0."Parent") as F18, (N1."ObjectType") as F19,
(N0."ProcessDefinition") as F20,
(N2."ObjectType") as F21, (N0."AdviceNote") as F22,
(N3."ObjectType") as F23, (N0."QtyBaseProductDCE") as F24,
(N0."UOMTime") as F25, (N0."TimePreparation") as F26,
(N0."TimeTeardown") as F27,
(N0."TimeCycle") as F28, (N0."UOMNorm") as F29,
(N0."QtyLot") as F30, (N0."UnionCode") as F31,
(N0."PrevSteps") as F32,
(N0."RelationType") as F33, (N0."Offset") as F34,
(N0."VersionGetTst") as F35,
(N0."FactorOnRceiptTst") as F36,
(N0."Description") as F37, (N0."MxDispatch") as F38,
(N0."BoxDispatch") as F39, (N0."MxIssue") as F40,
(N0."BoxIssue") as F41, (N0."OptimisticLockField") as F42,
(N0."GCRecord") as F43, (N0."ObjectType") as F44,
(N0."StatusТО") as F45, (N0."ProductMan") as F46,
(N4."ObjectType") as F47, (N0."BOMVersionPP") as F48,
(N0."Version") as F49, (N0."VersionPDM") as F50,
(N0."Date") as F51, (N0."DateEnd") as F52,
(N0."BOMType") as F53, (N0."BOMSourceNorm") as F54,
(N0."Order") as F55, (N5."ObjectType") as F56,
(N0."Execution") as F57, (N0."CostCalculation") as F58,
(N0."Planning") as F59, (N0."Description") as F60,
(N0."SchemeTO") as F61, (N0."ManLot") as F62,
(N0."InternalSemiproductEnable") as F63,
(N0."Semiproduct") as F64, (N6."ObjectType") as F65,
(N0."SemiproductSize") as F66,
(N0."SemiproductSize") as F67,
(N0."SemiproductProfileKind") as F68,
(N0."SemiproductSize1") as F69,
(N0."SemiproductSize2") as F70,
(N0."SemiproductSize3") as F71,
(N0."QtyDCE") as F72, (N0."QtySemiproducts") as F73,
(N0."ProductionMethod") as F74,
(N0."NumberActivitySemiproduct") as F75,
(N0."MassSemiproduct") as F76, (N0."TO_Source") as F77,
(N7."ObjectType") as F78, (N0."UseForPlanning") as F79,
(N0."ManEngineer") as F80, (N0."Estimator") as F81,
(N0."NumberStepLevel1") as F82,
(N0."NumberStepLevel2") as F83,
(N0."NumberStepLevel3") as F84,
(N0."NumberStepLevel1") as F85,
(N0."InstractionNumbers") as F86,
(N0."LiteraKTP") as F87, (N0."Site") as F88,
(N0."Typical") as F89, (N0."MXReceipt") as F90,
(N0."BoxReceipt") as F91
FROM
(
(
(
(
(
(
("BaseTechnology" N0
LEFT JOIN "BaseTechnology" N1 ON (N0."Parent" = N1."Oid")
)
LEFT JOIN "BaseTechnology" N2 ON (N0."ProcessDefinition" = N2."Oid")
)
LEFT JOIN "FileLibrary" N3 ON (N0."AdviceNote" = N3."Oid")
)
LEFT JOIN "Product" N4 ON (N0."ProductMan" = N4."Oid")
)
LEFT JOIN "Документ" N5 ON (N0."Order" = N5."Oid")
)
LEFT JOIN "Product" N6 ON (N0."SemiProduct" = N6."Oid")
)
LEFT JOIN "BaseTechnology" N7 ON (N0."TO_Source" = N7."Oid")
)
WHERE ((N0."ObjectType" = :p0)
AND N0."GCRecord" is null)
ORDER BY N0."Oid" asc nulls first
)
WHERE RowNum < = 128