Queries are slow on a large database

Hi,

I have a 12GB database with 787603 entries.
It is much larger than usual and there are performances issues.

I am doing small queries and they take much longer than I expect, i.e.:
Get the total number of entries
Get 25 consecutive entries (i.e. 0-25)
Get one specific entry

On average each query needs 4 to 5 minutes to return a result.

I have been adviced to use indexes but they are already used through the JDO Metadata file.

For instance I use this class:

public class MessageImpl implements Message, LogItem, Comparable {
   
    private int Id;
    private int logTestRunId;
    private int logEntryId;
    private String sendingNodeId;
    private String receivingNodeId;
    private Date timeStamp;
    private String messageName;
    private String messageDetail;
    private transient LogTestRunImpl logTestRun;
    private transient LogEntryImpl logEntryImpl;

...

And I use such query:
Query query = pm.newQuery(MessageImpl.class,
    "this.logTestRunId == " + logTestRun.getId());
query.setOrdering("this.Id ascending");

The package.jdo contains for this 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>

I profiled the application and ObjectDB calls on RandomAccessFile.readFully and I have been told that because ObjectDB have to scan the entire database file and is not using indexes.
Therefore I think I am not doing something wrong. Any idea what the problem is?

Thanks.

How many objects should be returned by this sample query? If there are many Message instances with the same requested logTestRunId, then ordering the results may be the problem, since to return the first 25 objects, all the objects have to be retrieved.

For diagnosis please try the same query without the ORDER BY clause.

A composite index in which the first component is logTestRunId and the second is Id can be the solution for this specific query, because results can be ordered by the index rather than by accessing the data. If you add a new index to an existing database use the Doctor to create a new database in which the index is active.

If this doesn't help please turn on query processing logging:

<general>
    :
    :
  <logger name="query" level="trace" />
</general>

Thanks, as you suggested there are many Message instances with the same logTestRunId.
I started to use different queries and the performances are much better. I will try composite index later.

However there is one thing I haven't managed to figure out: how to get the total number of object in a database without retrieving everything?

Alternatively how to get the highest value of an indexed entry? Specifically in my MessageImpl example, I want to get the object with the highest Id. I tried a query like "count(this.Id)" or "max(this.Id)" but I get an exception:
[ObjectDB 2.5.0_01]  ==> count <== (this.Id)
javax.jdo.JDOUserException
Invalid context for aggregate 'count' (error 743)
(position 0) at com.objectdb.jdo.JdoQuery.execute0(JdoQuery.java:840)
at com.objectdb.jdo.JdoQuery.execute(JdoQuery.java:720)

Thanks.

In JPQL the count query is:

SELECT COUNT(m) FROM MessageImpl m

You are using JDO. I am not not about the JDOQL syntax, but maybe:

SELECT COUNT(this) FROM MessageImpl

Anyway, both should work if you build your query as a single string query, with very fast execution time.

Similarly to get MAX/MIN values:

SELECT MAX(m.id) FROM MessageImpl m

or in JDOQL (with a single query string):

SELECT MAX(id) FROM MessageImpl

If the field is indexed these queries also should be executed very fast.

Thanks, it is much better now.
FYI, here is the query I use:

query = pm.newQuery (MessageImpl.class);
query.setResult ("min(this.Id), max(this.Id), count(this.Id)");
Object[] results = (Object[])query.execute();

Integer minId = (Integer)results[0];
Integer maxId = (Integer)results[1];
Long countId = (Long)results[2];

Now I want to use the doctor to rebuild a database with a new index.
I had

        <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>

I added a new index on timestamp:

        <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="timestamp"/>
         <extension vendor-name="objectdb" key="index" value="name"/>
         <extension vendor-name="objectdb" key="index" value="context"/>
        </class>

My class is:

public class LogVolatileEntityImpl implements LogVolatileEntity, LogItem
{
    private int                    Id;
    private String                 name;
    private String                 value;
    private String                 context;
    private int                    logEntryId;
    private long                   timestamp  = -1;
...

I rebuild and enhanced my classes.
I put my new classes, objectDB.jar and my old database in the same folder.
Then I executed the doctor as such:

java -cp objectdb.jar com.objectdb.Doctor C:\temp\ODBTests\LogVolatileEntity.odb C:\temp\ODBTests\LogVolatileEntityNew.odb

It appears to succeed: "New database copy is ready at: C:\temp\ODBTests\LogVolatileEntityNew.odb"

Can you confirm it is the correct way to add an index to an existing database?

Thanks

Rebuilding indexes with the Doctor is a bit tricky, because you have to verify that the new schema is available. Otherwise, you may generate a new database but with the old indexes.

If you persist a new entity or modify an existing entity using the new classes before running the Doctor is should work. If the the new enhanced class and the XML metadata are in the correct location in the classpath it should also work, but it could be missed easily. Just putting the classes in the same directory as the database file is not sufficient.

In addition, consider using composite indexes as suggested in #2 above. The sample query in #1 above can benefit from a composite index on 2 fields: logTestRunId and Id.