879 words

Speed of queries that return many objects

2015-08-29 21:10


I have a DB of 146MB - with 1_000_000 elements.

Loading the contents (objectDB query in java) takes about 10sec.

I am using a recent MacBook (SSD blabla - disk I/O speed is about 400MB/sec - according to blackMagicTest app for mac) - so I was expecting the query to take less than one sec (146/400 = 0.35sec.. + some overhead).

I was wondering if this kind of speed is "normal", or if this is something I should investigate.

So I created a database with 1_000_000 dummy entities (example below) - the DB size is only about 8MB - and it takes about 3sec to load the 1mio entities... still seems slow.



import java.io.Serializable;
import javax.persistence.Entity;
public class BenchEnt implements Serializable {
    private static final long serialVersionUID = 1L;
    public BenchEnt() {
        String d = System.getProperty("user.dir");
        EntityManagerFactory emf = Persistence.createEntityManagerFactory(d + "/T1.odb");
        EntityManager em = emf.createEntityManager();
        if( false){
            for( int i = 0; i < 1_000_000; i++){
                BenchEnt ent = new BenchEnt();
        if( true){
            long t1 = System.currentTimeMillis();
            TypedQuery<BenchEnt> query = em.createQuery("SELECT a FROM BenchEnt a", BenchEnt.class);
            List<BenchEnt> L = query.getResultList();
            long t2 = System.currentTimeMillis();
            long dt = t2-t1;
            System.out.println("LIST " + L.size() + " dt= " + dt);
EKK's picture
Joined on 2013-03-05
User Post #34
2015-08-31 12:14

3 seconds for this test case seems to be normal, although with enhancement and tuning it could be in the range of 1-2 seconds.

The overhead of preparing entity objects is more than you may expect and much slower than just reading the data from the disk. Using embedded objects or report queries (which return fields rather than entity objects) may help.

If you can load the data lazily then the query can return results much faster:

    TypedQuery<BenchEnt> query = em.createQuery(
        "SELECT a FROM BenchEnt a", BenchEnt.class);
    query.setHint("objectdb.result-fetch", "LAZY");
    List<BenchEnt> L = query.getResultList();

but of course, this may not help if you need all the objects in memory at once.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support's picture
Joined on 2010-05-03
User Post #2,301
2015-10-18 22:32

Thanks, the problem is that I can't do lazy loading as I need the full content of each object.

There is still something I don't understand.

I measure how long it takes to instantiate the objects - I get something around 50ms for 1mio objects.

The way I understand how retrieving something from a DB works is as follows:

1/ load from the disk - the odb file size for the example above is around 8MB. So it should take 20ms to load the file into memory.

2/ then there is some magic to instantiate the objects - should take around 50ms.

so overall it should take about 70ms.

Can you give some information of what I am missing to reach the 3 sec?

Also when I profiled the loading of the 1mio object - 50% of the time is spent in java.lang.System.identityHashCode(Object).

The rest is split between seek0, readBytes, writeBytes etc... (see screenshot attached).


I am using ObjectDB for two applications - and in both cases - loading from the ObjectDB is the bottleneck of my two applications. I have 100+ DBs - and each one is about 4GB. So in the end - loading the DBs takes hours.




EKK's picture
Joined on 2013-03-05
User Post #35
2015-10-20 10:42

Unfortunately your expectations are unrealistic. Managing entity objects involves a significant overhead which is non proportional to the cost of object instantiation, so the 50ms estimate is irrelevant. 20ms for the I/O operations is also unrealistic when we have to load one million objects. It is not copying a file sequentially.

Apparently your use case is not a standard database (or object database) activity. The main idea of a database is that large amount of data is stored in a secondary storage and only portion of that data is retrieved to memory at a time. You want to load the entire database into memory at once, and an ODBMS (or maybe DBMS in general) may not be the most efficient tool for this purpose.

Although an ODBMS may not be the right tool, you may still try using it in a non standard way. For example, by using fewer but larger objects (optionally using embedded objects) or by retrieving the data without the entity objects using "report queries" (using projection). Of course, you will lose much of the advantages of using ODBMS if you use such methods.

There is no way for the System.identityHashCode method to take 50% of the total execution time. This must be one of these cases in which profiling output is distorted (e.g. is the result of excessive JVM / profiling preparations during one of the calls to that method).

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support's picture
Joined on 2010-05-03
User Post #2,322
2017-06-02 04:27

I have the same issue - slow performance on the Mac. Latest JDK 1.8 , and newest Mac

Attached is the sample application. Instructions:

- Point to a local folder in com.example.demo.db.Db.java class

- Build using maven clean install -DskipTests

- Start Spring Boot application in target folder: java -jar demo-0.0.1-SNAPSHOT.jar

- Generate sample file (about 70k records) with http://localhost:8080/hello

- Run query with URL: http://localhost:8080/1/age

=> If you try this on Windows, last call is working blazing fast (100ms). On Mac is very very slow (3sec).

duskov's picture
Joined on 2017-06-02
User Post #1
2017-06-02 12:38

Are you sure that this is the same issue of a slow query that returns many objects? In that case, the same answer at #4 above still applies.

Please provide more details about your issue. The original post doesn't discuss fast execution on Windows and slow execution only on Mac. If your issue is different please create a new forum thread and close this one.

Finally, please provide a simple minimized console application (see these posting instructions).

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support's picture
Joined on 2010-05-03
User Post #2,761

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Maximum file size: 32 MB