Maximizing SquiDB performance

While SquiDB is fast and lightweight out of the box, there are a few things you can do to optimize performance even more for certain use cases. We've documented here some of the most common things you can do to squeeze the out the best possible performance.

Reuse model objects

In some ORMs, the cost of inflating model objects from a cursor can be a big dent in performance. SquiDB's model objects are implemented in such a way that they can be reused/reinflated with new data, so to speak, without any noticeable negative effects in speed or memory usage.

For example, suppose you're iterating through a cursor and want to operate on each row using a model object, but don't actually need the information about each model object to live outside the scope of the loop. A naive implementation might look like this:

SquidCursor<Person> cursor = ...;
while (cursor.moveToNext()) {
    Person person = new Person(cursor);
    doSomethingWithPerson(person);
}

In this version a new Person object is allocated for each iteration of the loop, and is discarded soon thereafter. Especially for a large cursor, that can be a lot of new objects added to the heap, which temporarily reduces available memory and gives the garbage collector a lot of extra work to do. Instead, you should use a single instance of the model object, and repopulate it on each loop iteration:

SquidCursor<Person> cursor = ...;
Person person = new Person(); // Allocate model outside the loop
while (cursor.moveToNext()) {
    person.readPropertiesFromCursor(cursor);
    doSomethingWithPerson(person);
}
person = null; // Now you're done with it and it can be collected

In this version you only ever allocate a single instance of the model class. Furthermore, once the model has been initialized with data from one row in the cursor, its internal state is set up and can be efficiently reused across calls to readPropertiesFromCursor. Repopulating the object a second (or third, or fourth, or nth) time incurs no additional memory overhead after the first time the object was populated.

This pattern only works if you don't need the model object to live beyond its loop iteration. If you need that object to stick around, then you have no choice but to allocate the object each time.

Reuse Query objects

If you find yourself building the same Query object over and over again with just a slight change in arguments each time, you might notice that you're incurring a lot of StringBuilder allocations and garbage collections as the SQL string is constructed and discarded. If you have a query that is always the same except for some different arguments, SquiDB enables a pattern for you to reuse that query without repeatedly building it. Here's what bad usage might look like:

// Don't do this
for (int i = 0; i < 100; i++) {
    Query query = Query.select(Person.NAME).from(Person.TABLE).where(Person.AGE.eq(i));
    SquidCursor<Person> cursor = database.query(Person.class, query);
    try {
        Log.i("There are " + cursor.getCount() + " people of age " + i);
        printNames(cursor);
    } finally {
        cursor.close();
    }
}

This code will allocate 100 different Query objects, each with a different value for the age criterion, and recompile the same SQL string 100 times. That's a lot of objects and unnecessary String allocations.

Instead, you can use AtomicReferences in your criterions to let SquiDB know that you might be reusing the query object with different arguments. You'll only have to allocate one Query object, and the SQL string corresponding to the query will only be compiled once, with the variable arguments being dynamically bound at query time:

// A better approach
AtomicReference<Integer> age = new AtomicReference<Integer>(0); // AtomicInteger would also work
Query query = Query.select(Person.NAME).from(Person.TABLE).where(Person.AGE.eq(age));
for (int i = 0; i < 100; i++) {
    age.set(i);
    SquidCursor<Person> cursor = database.query(Person.class, query);
    try {
        Log.i("There are " + cursor.getCount() + " people of age " + i);
        printNames(cursor);
    } finally {
        cursor.close();
    }
}

In this version only a single Query object is allocated, and it is only compiled into SQL once. The cached SQL with dynamically bound arguments is used for each iteration of the loop, saving lots of String allocation overhead.

A caveat: Query objects are not thread safe, so if you are reusing a Query object that might be accessed from multiple threads or in some kind of reentrant method, you might want to consider having separate query objects (maybe using a ThreadLocal) for those cases so that some other thread doesn't change the arguments to the query out from under you.

Don't query for more information than you need

While it might be tempting to always use Query.select() with no arguments (i.e. select *) or Query.select(Model.PROPERTIES) because they are cleaner and include everything you might need, sometimes selecting more than you need can have undesirable memory overhead. For example:

Query query = Query.select(Person.PROPERTIES).where(Person.AGE.gt(18));
SquidCursor<Person> cursor = database.query(Person.class, query);
try {
    Person person = new Person();
    while (cursor.moveToNext()) {
        person.readPropertiesFromCursor(cursor);
        Log.i(person.getName() + " is " + person.getAge() + " years old");
    }
} finally {
    cursor.close();
}

Look at you, reusing your model objects! Nice job! However, in this loop, you're only interested in two properties of Person: name and age. If your model object has a lot of columns, that means the database is returning a lot more data than you actually need, which means the cursor is allocating memory to give you information you don't actually care about, and populating your models incurs a little extra overhead. You can do better by only querying for the columns you need:

Query query = Query.select(Person.NAME, Person.AGE).where(Person.AGE.gt(18));
/* The rest is the same as the above example */

Read directly from the cursor

Model objects are very lightweight and easily reusable, but in some cases you may not need the model object, just the cursor data. In these situations SquidCursor makes it easy to read just what you want from the cursor directly.

Map<Long, String> idsToNames = new HashMap<Long, String>();
Query query = Query.select(Person.NAME, Person.AGE).where(Person.AGE.gt(18));
SquidCursor<Person> cursor = database.query(Person.class, query);
try {
    while (cursor.moveToNext()) {
        Long id = cursor.get(Person.ID);
        String name = cursor.get(Person.NAME);
        idsToNames.put(id, name);
    }
} finally {
    cursor.close();
}