My application deployed in JBoss 3.2.6 uses a CMP with fields(similar to):
entitynamePK String In DB varchar(255) PK
entitytime Timestamp In DB datatime
entitystate Integer In DB int
entitysize Integer In DB int
entitystr String In DB varchar(50)
DB used is MS SQL Server. The field 'entitynamePK' is indexed in the DB.
My problem is,
In one of my session bean methods..say x(), I use findByPrimaryKey(String entitynamePK)
for finding the entity by PK. Now, based on certain business logic, all other fields would be updated. I tried pre-filling the DB with 100 records, and ran a performance test on method x() to know the processing time reqd for this method. For 100 pre-filled records, the processing time was say 't1'.
Then I filled the DB with a count of 20,000 records, and ran the same performance test, now the processing time raised to some where near..(10*t1).
Later I found that the additional processing time is entirely because of the
findByPrimaryKey() - finder.
I'am little worried about the multiple by which the processing time is varying between the 100 pre-filled records & the 20,000 pre-filled records.
I'am not sure - where to attack the problem:: In DB or in JBoss??
Any pointers regarding the same would be much appreciated.
Thanks in advance..
Do you check sql statement generated by finder method ? (turn on debug/trace on plugins.cmp classes) ?
Are you able to catch query plan on your DB ?