0

It take

2021-03-10 15:48:07,593 INFO  [com.ery.integrator.erpcrm.schedular.CustomerTeamsJmsServiceImpl] (EJB default - 2) In CustomerTeamsJmsServiceImpl: ******************************** Batch Processing Started ********************************
2021-03-10 15:48:07,593 INFO  [com.ery.integrator.erpcrm.schedular.CustomerTeamsJmsServiceImpl] (EJB default - 2) In CustomerTeamsJmsServiceImpl: ******************************** Batch No. 22| Total 182| Size:5000 Started ********************************
2021-03-10 16:14:11,227 INFO  [com.ery.integrator.erpcrm.schedular.CustomerTeamsJmsServiceImpl] (EJB default - 2) In CustomerTeamsJmsServiceImpl: ******************************** Batch No.22 Pushed and Flushed ********************************

There is no Composite Key involved at Database Level in which we insert and update.

 CREATE TABLE "MDATA"."CUSTOMER_TEAMS" 
   (    "ACCOUNT_REGISTRY_ID" VARCHAR2(30 BYTE), 
    "EDGE_ACCOUNT_ID" VARCHAR2(150 BYTE), 
    "ACCOUNT_OWNER_EMAIL" VARCHAR2(250 BYTE), 
    "CRT_MEMBER_EMAIL" VARCHAR2(250 BYTE), 
    "ROLE_NAME" VARCHAR2(150 BYTE), 
    "SOURCE" VARCHAR2(30 BYTE), 
    "PERSON_PARTY_ID" NUMBER(15,0), 
    "DELETED_IN_TERP" VARCHAR2(30 BYTE), 
    "DELETED_DATE" DATE, 
    "LAST_MODIFIED_DATE" DATE NOT NULL ENABLE
   )

but in JPA we have composite key on First four columns. @Id and update happens on those four composite key

How to do performance tuning of Database Table so that process goes fast? As of now there is no primary key at database level as well as there is no indexes. Kindly suggest

fatherazrael
  • 5,511
  • 16
  • 71
  • 155
  • 1
    If your update statement is updating based on the first four columns and those four columns are unique, there should be a unique index on those first four columns. – Justin Cave Mar 10 '21 at 16:49
  • @JustinCave: Those four columns are composite key. So individually they ae not unique but collectively they are unique. Added unique to all four together. Any more inputs? – fatherazrael Mar 11 '21 at 04:28
  • Right. A unique index on the first four columns would mean that the four columns together are unique. – Justin Cave Mar 11 '21 at 06:08

1 Answers1

0

It is a good practice (default practice I would say) to create some constraints and indexes for your table in the first place. If your UPDATE has a WHERE clause(probably It has), the presence of indexes is an important factor.

Then you can consider the following: In Oracle there is something called "bulk processing". If you use this when you are selecting/inserting/updating a lot of records at the same time the performance of the operation is much better.

There are other factors to consider as well when talking about performance in Oracle:

  • Oracle version: There is a huge gap of optimizing features in Oracle Enterprise and Oracle XE for example.
  • Hardware: The hard drive I/O speed, CPU and RAM are important as well.

If you still cannot get an acceptable time, you may try to dig a bit deeper and enable the trace for the your sessions so you can have more data to work with: Using Oracle Trace

crocarneiro
  • 134
  • 8