Query performance in general

Hi ObjectDB team,

we are currently investigating performance issues that affect some of our customers. Unfortunately, we are not yet able to reproduce issues in a test case, so we rely on reports and log files from customers.

During internal analysis and discussions, we had some general questions - maybe you can clarify some of them:

• Database file page size: the default is 2kb and the documentation says, that should be fine for most applications. Are there scenarios you know of, that perform better with bigger page sizes (e.g. if an Entity has 'a lot' of member fields)?
• Order in WHERE clause of a query: so far we are not optimizing the order of conditions in the where clauses of our queries. Would it make sense to have indexed fields first and then the others, or is that something that the query compiler/optimizer is doing automatically? For example, Entity TestObject has fields id and date where id is indexed and date is not. Would it make sense to have "WHERE $1.id > ... AND $1.date >= ..." instead of "WHERE $.date >= ... AND $1.id > ..."?
• We saw that there is a special statement "objectdb statistics": given that we have customers with rather old database instances that where created with older versions of objectdb - would it make sense to run this statement on those databases? Or are the statistics created/updated automatically by some internal mechanism?

Thank you for your support.

Hi,

More information about the issue should help, but regarding your questions:

1. The page size may have some effect, but probably minor, so this is probably not the cause.
2. The order of the expressions in a query should not affect performance. ObjectDB checks different orders.
3. To use the new "objectdb statistics" update you need to use ObjectDB version 2.8.1 or later, but it should work with any older ObjectDB 2.x database (the file format has not been changed). It is not updated automatically. You have to run the "objectdb statistics" virtual query after major data changes that can affect query plan selection. It could be done as a background task (e.g. once every weekend). In practice, running it once, when the database already contains sufficient data, may be sufficient.

Updating the software to the last version and running "objectdb statistics" is the first recommended step in trying to solve this performance issue. If it does not help the alternative is manual analysis of possible query plans and using hints to select a better query plan.