CODEWITHSUNDEEP
postgresqljpaejbjboss7.x
VJ Vélan Solutions
VJ Vélan Solutions

Reputation: 6564

insert taking too long

My setup is: JBOSS AS 7.1.1 and PostGreSql

When there are no or very less (a couple of hundred) records in the table, my inserts/updates completes very fast. But when the number of records get to a couple of thousand, the inserts or updates take an extremely long time. For ex: inserting a record takes more than 1 or 2 seconds.

Not sure where the problem is or where to start.

My code to update the database looks like this -

public long updateRecord(long id, List<MyData> myDataList) {
    Event eventFromDB = findById(id).get(0);
    List<MyData> myDataListFromDB = eventFromDB.getMyData();
    for(MyData myData : myDataList) {
        myDataListFromDB.add(myData);
    }
    em.merge(eventFromDB);
    return myDataList.size();
}

I am a newbie and have set up the jboss as to the best of my knowledge.

I haven't done any configuration settings on either the JBOSS AS or the PostGreSQL.

The JBOSS AS is running in the standalone mode using the standalone-full.xml config file.

Thanks in advance.

jbossql=# \d event_mydata
         Table "public.event_mydata"
 Column          |          Type          | Modifiers 
-----------------+------------------------+-----------
 event_id        | bigint                 | not null
 date_time       | character varying(255) | 
 secs_since_1970 | double precision       | 
 value           | real                   | 
Foreign-key constraints:
    "fkcf2bc134ec016855" FOREIGN KEY (event_id) REFERENCES event(id)

And List is an element collection

@ElementCollection
private List<MyData> myData;
public List<MyData> getMyData() {
    return myData;
}

Upvotes: 0

Views: 1434

Answers (1)

VJ V&#233;lan Solutions
VJ V&#233;lan Solutions

Reputation: 6564

I think the problem is that using an ElementCollection is not a very good idea if one is thinking about populating large quantities of data.

related issue here

I re-architected the database such that i am now using a one-to-many with birectional relationship. Seems to help in inserting a new entry in the child table when there is a lot of data already in the child table.

Hope it helps someone.

Thanks all for looking and responding.

Upvotes: 1

Related Questions