Db4o StartsWith and ignore case

Question

The following query takes a while to return:

db.Query<Person>(x => x.StartsWith("Chr", StringComparison.CurrentCultureIgnoreCase))

is there a way to get this working correctly? ie faster?

Answer 1

Maybe you ran into a limitation of db4o’s query-optimization. Normally Native Queries and LINQ-Queries are translated into a low level SODA-query. When this optimization fails, db4o instantiates the objects in the database in order to execute the query. As you can imagine this can be quite slow.

The best current solution is to use a SODA directly for this case. For example a class with one property:

 public class SimpleObject
 {
     private string name;
     public string Name
     {
         get { return name; }
        set { name = value; }
     }
 }

The native query like this:

var result = db.Query<SimpleObject>(x => x.Name.StartsWith ("Chr",StringComparison.CurrentCultureIgnoreCase));

Can be represented by this SODA-Query:

IQuery query = db.Query();
query.Constrain(typeof (SimpleObject)); // restrict to a certain class
query.Descend("name").Constrain("Chr").StartsWith(false); // the field 'name' starts with  'chr', case-insensitive

foreach (var s in query.Execute())
{
    // 
}

I hope future versions of the Query-Optimizer support this case directly.

Answer 2

adding and index on the column you're comparing would probably help.

http://developer.db4o.com/Documentation/Reference/db4o-7.4/net35/tutorial/docs/Indexes.html#outline219