Accelerate XAF Application on Oracle Database 40 Times

by | Sep 5, 2014 | Xafari Framework Blog | 0 comments

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

SQLqueryTables involved in the queryRuntime secondsacceleration time
TableNumber of recordswithout Xafari.DBwith Xafari.DB
№1Position Expenditure norms
ProductOutNorm		797`1882`2304847
Nomenclature Position
Product		232`534
Position Bill of material (BOM)27`515
Document76
№2Basic Technology2`311`603359
File Libraries87
№3Basic Technology2`311`603241640
Nomenclature Position232`534
File Libraries87
Document76

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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:
SQL query №1 without using Xafari.DB module
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:
SQL query №1 with using Xafari.DB module
With an attached unit Xafari.DB, the index is picked up, the query runs faster.
Time SQL query №1 without module Xafari.DB
Fig. 1 - Time SQL query №1 without module Xafari.DB
Time of SQL query №1, but with the module Xafari.DB
Fig. 2 - Time of SQL query №1, but with the module Xafari.DB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

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

Submit a Comment

Share the Link
Write US