Query becomes slower on moving from ObjectDB 1.x to ObjectDB 2.x

Hi,

I upgraded the odb file using converter.jar which is having 15943 entry. Previously, with object 1.x, the same query was getting executed within an seconds and now the same is taking around 40 seconds in object db 2.x

I have build the query using the follwoing

Query query = persistanceManager.newQuery("LogEntryAttrebuteImpl.calss", "typeIndexes.contains(this.type) && logEntryIdList.contains(this.logEntryId)");

query.declareParameters("java.util.List typeIndexes,java.util.List logEntryIdList");

List param1 has the following data [5]

List param2 , data is attached in the file as it was big

query.execute(param1, param2);

This takes 1 second to execute using objectdb1.x, but the same takes around 35 seconds in objectdb2.x. I have also uploaded the snapshot of hte database contents in objectdb explorer

ObjectDB 2.x is usually faster than ObjectDB 1.x, so it should be possible to get at least similar performance for this query with ObjectDB 2.x.

Maybe an index is missing?

It would help if you could upload a sample database and a simple test program that demonstrates this performance problem.

Your other question will be moved to a separate forum thread (please do not use one post for more than one issue).

Thanks for the reply. Please find the odb file which i am using.

I have already pasted the code which is being exeucted to run the query.

In the database that you uploaded there is an index on field logEntryId, which is inactive.

An inactive index is usually the result of adding a new index to an existing database, but it could be also some issue with the conversion from 1.x to 2.x.

To activate the index the database has to be rebuilt by running the Doctor in repair mode.

After fixing the index in that database (by running the Doctor) the query runs fast.

Thanks. i will check it and let you know. I will use the following command after i convert from 1.x to 2.x

String commandLine = java -cp objectdb.jar com.objectdb.Doctor old.odb new.odb

As i am doing this inside my application, i need to use Runtime.getRuntime().exec( commandLine );

But, i dont want to spawn a new process to do this. Do you have any static method, so that i can directly call that and achieve the same goal.

The direct command is:

com.objectdb.Doctor.main(new String[] {oldOdbFileName, newOdbFileName});

but this has not been tested, so following the problems that you had in using the Converter this way, spawning a new process may be preferred.

After converting each odb file using converer.jar (through Process spawning) and then i am using Doctor (using process spawn) to to repair.  I got the following log when using doctor

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] ObjectDB Doctor [version 2.3.7_25]

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] Copyright (c) 2012, ObjectDB Software. All rights reserved.

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46]

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] Scanning the database file...

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46]  1MB (total)

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46]

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] Analyzing database structure...

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] 10% 20% 30% 40% 50% 60% 70% 80% 90% 100%

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46]

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] -------------------------

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] No errors have been found

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] -------------------------

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46]

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] Creating a database copy...

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46]   15944 entity objects (total) have been migrated.

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46]

11-Sep 14:55:33 ERROR  [ProcessSpawner.execute:46] New database copy is ready at: E:\temp\\LogEntryAttribute.odb

When i am trying to execute the query, still it is taking the same amount of time.

11-Sep 14:55:58 ERROR  [JDORepository.retrieve:482] Start Time : class com.anritsu.eu.rtd.logger.rtdtool.LogEntryAttributeImpl
11-Sep 14:56:34 ERROR  [JDORepository.retrieve:484] End Time Time
11-Sep 14:56:34 ERROR  [JDORepository.retrieve:482] Start Time : class com.anritsu.eu.rtd.logger.rtdtool.LogEntryAttributeImpl
11-Sep 14:57:09 ERROR  [JDORepository.retrieve:484] End Time Time
11-Sep 14:57:09 ERROR  [JDORepository.retrieve:482] Start Time : class com.anritsu.eu.rtd.logger.rtdtool.LogEntryAttributeImpl
11-Sep 14:57:44 ERROR  [JDORepository.retrieve:484] End Time Time
11-Sep 14:57:44 ERROR  [JDORepository.retrieve:482] Start Time : class com.anritsu.eu.rtd.logger.rtdtool.LogEntryAttributeImpl
11-Sep 14:58:19 ERROR  [JDORepository.retrieve:484] End Time Time
11-Sep 14:58:19 ERROR  [JDORepository.retrieve:482] Start Time : class com.anritsu.eu.rtd.logger.rtdtool.LogEntryAttributeImpl
11-Sep 14:58:19 ERROR  [JDORepository.retrieve:484] End Time Time
11-Sep 14:58:19 ERROR  [JDORepository.retrieve:482] Start Time : class com.anritsu.eu.rtd.logger.rtdtool.LogEntryAttributeImpl
11-Sep 14:58:55 ERROR  [JDORepository.retrieve:484] End Time Time
11-Sep 14:58:55 ERROR  [JDORepository.retrieve:482] Start Time : class com.anritsu.eu.rtd.logger.rtdtool.LogEntryAttributeImpl
11-Sep 14:58:55 ERROR  [JDORepository.retrieve:484] End Time Time

Here I am executing the different query for 7 times and total execution time is approx 3 minutes, which is highly unexpectable for our software release. Please find the database file after repair

In this database there is no index on field logEntryId. Did you have an index on that field in ObjectDB 1.x?

You have to define an index and open the database in your application after conversion and before running the Doctor, otherwise the Doctor cannot help.

Please find the configuration which we are using to define the index as follows

jdo>
    <package name="com.anritsu.eu.rtd.logger.rtdtool">
       
        <class name="ArchiveTestRuns">
         <!--<extension vendor-name="objectdb" key="unique-index" value="Id"/>-->
         <extension vendor-name="objectdb" key="index" value="testName"/>
        </class>
       
        <class name="LogEntryAttributeImpl">
         <extension vendor-name="objectdb" key="unique-index" value="Id"/>
         <extension vendor-name="objectdb" key="index" value="logEntryId"/>
        </class>
       
        <class name="LogEntryImpl">
         <extension vendor-name="objectdb" key="unique-index" value="Id"/>
         <extension vendor-name="objectdb" key="index" value="logTestRunId"/>
        </class>
       
        <class name="MessageImpl">
         <extension vendor-name="objectdb" key="unique-index" value="Id"/>
         <extension vendor-name="objectdb" key="index" value="logTestRunId"/>
         <extension vendor-name="objectdb" key="index" value="logEntryId"/>
        </class>
       
        <class name="LogTestRunImpl">
         <field name="detectedNodes" embedded="true">
   <collection embedded-element="true" />
  </field>
  <extension vendor-name="objectdb" key="unique-index" value="Id"/>
        </class>
       
        <class name="LogVolatileEntityImpl">
         <extension vendor-name="objectdb" key="unique-index" value="Id"/>
         <extension vendor-name="objectdb" key="index" value="logEntryId"/>
         <extension vendor-name="objectdb" key="index" value="name"/>
         <extension vendor-name="objectdb" key="index" value="context"/>
        </class>
       
        <class name="LogEntryAttributeImpl">
         <extension vendor-name="objectdb" key="unique-index" value="Id"/>
        </class>
       
    </package>
</jdo>

Just now i realized that LogEntryAttributeImpl is defined twice in the package.jdo file. It was written long time ago so please suggest me if i need to delete one ?

But i am wondering, if logEntryId is not indexed in objectdb 1.x, then how the query is very fast when i am using object db 1.x.

You have mentioned that  "You have to define an index and open the database in your application after conversion"

Does it mean that, before conversion, i need to do the following steps

1. Update the package.jdo with an index defined

2. Convert the old odb file

3. Connect to database, basically created a Persistance manager object for odb file 2.x

4. Run the doctor

Am i right ?

ObjectDB 1.x and ObjectDB 2.x read the XML metadata differently, so in case of duplication it is possible that ObjectDB 1.x uses the first definition (with the index) and ObjectDB 2.x uses the second definition (without the index).

Try to remove the second definition and follow the steps that you described.

Actually, maybe opening the database with that package.jdo file removed the index.

So maybe after fixing the package.jdo file you will not need to run the Doctor at all.

If objectdb 1.x is using the first defination then ideally the database should have been indexed for logEntryId column. But as you mentioned database is not indexed so ideally it would be using the second defination.

Anyways, i tried removing the second defination and tried the following steps

1. Update the package.jdo with an index defined

2. Convert the old odb file

3. Connect to database, basically created a Persistance manager object for odb file 2.x

But still no improvement :-(

Please try at ur side by running the query which i have already given after converting. May be then you can simulate it and will be able to diagnose more.

So, please let me know what exactly i need to do to solve this ?

You didn't mention step 4 from post #9 above. You may need to run the Doctor to build the index.

hmmm.. i did the following steps

1. Update the package.jdo with an index defined

2. Convert the old odb file

3. Connect to database, basically created a Persistance manager object for odb file 2.x

4. Close the database connection

5. Run the doctor

and finally, after running the doctor, it is working fine :-) Thanks a lot for the support.

But now the problem is how do i know that when do I need to run the Doctor ? Do you have any utilies to check if database requires some repair or something ? Otherwise, i have to do some attribute setting somewhere to know this, which I think would not be the right way to do this ?

The Doctor itself can diagnose problems in the database (when running in diagnosis mode) but currently inactive indexes are not detected.

Anyway, this procedure is a one time operation per database, as part of the conversion from ObjectDB 1.x to 2.x. You will need to run the Doctor again only after adding new indexes to existing databases.