Lex.DB Performance Tips.

Hi there,

Last week I’ve read the article about Lex.DB performance by Diederik Krols (link). This article has inspired me to provide some valuable feedback on usage of Lex.DB.

– Why don’t you like orcs? You just cannot cook them properly.
MMORPG Folklore

There are several principles one has to know to use Lex.DB efficiently.

1. Database files kept consistent all the time. During updates a short write file lock is acquired to prevent concurrent updates. As soon as concurrent update is detected, all related data is reloaded. This is important in a world of suspended/resumed applications like W8/WP8 as well as concurrent access from different threads (aka ASP.NET).

2. As consequence of (1.) every update operation always flushes & closes the data files.

3. Read/Writes are possible from within corresponding read/write transaction only. If there is no ambient transaction in a thread scope, the one will be created automatically.

4. Cost of transaction creation/disposal is constant. Cost of write transaction is bigger than cost of read one (because of highly probable data index update).

5. If several records are read/updated in a loop, the transaction costs are multiplied.

6. Overhead of async/await methods is relatively high. If several records are read/updated in async/await manner, these costs skyrocket. General performance hint: avoid async/await unless necessary.

7. To address these inefficiencies Lex.DB has bulk methods like BulkRead, BulkWrite, Save<T>(IEnumerable<T>), Delete<T>(IEnumerable<T>), DeleteByKeys<K>(IEnumerable<K>) and asynchronous extensions for them.

8. BulkRead allows to pack several read operations into single read transaction.

Here is a fastest way to load several tables at once:

db.BulkRead(() =>
{
  _myPeople = db.LoadAll<Person>();
  _myAddresses = db.LoadAll<Address>();
  _myCompanies = db.LoadAll<Company>();
});

Just single async/await for BulkRead will move all loading work in background thread:

await db.BulkReadAsync(()=>
{
  _myPeople = db.LoadAll<Person>();
  _myAddresses = db.LoadAll<Address>();
  _myCompanies = db.LoadAll<Company>();
});

Here is a BAD example (opening 3 read transactions, doing async/await marshalling 3 times, doing 3 background threads repeatedly):

// DON'T
await _myPeople = db.LoadAllAsync<Person>();
await _myAddresses = db.LoadAllAsync<Address>();
await _myCompanies = db.LoadAllAsync<Company>();

9.  BulkWrite/BulkWriteAsync can speed up several writes like this:

db.BulkWrite(() =>
{
  db.Save(myPerson);
  db.Save(myAddress);
  db.Save(myCompany);
  db.Delete(anotherPerson);
});

or like this with async/await:

await db.BulkWriteAsync(() =>
{
  db.Save(myPerson);
  db.Save(myAddress);
  db.Save(myCompany);
  db.Delete(anotherPerson);
});

And here is a typical BAD example:

// DON'T, UNLESS NO OTHER WAY
await db.SaveAsync(myPerson);
await db.SaveAsync(myAddress);
await db.SaveAsync(myCompany);
await db.DeleteAsync(anotherPerson);

10. Don’t save/delete collections of records using loops. And moreover – asynchronously.
Extremely wrong:

// DON'T. EVER
foreach (var person in personsToUpdate)
   await db.SaveAsync(person);

foreach (var person in personsToDelete)
   await db.DeleteAsync(person);

The proper async way at least looks like this:

await db.SaveAsync(persons);
await db.DeleteAsync(persons);

But the best ever async way is:

await db.BulkWriteAsync(() =>
{
   db.Save(persons);
   db.Delete(persons);
});

Keep these ideas in mind and Lex.DB will always be the fastest way to your data.

King regards,
Lex

3 thoughts on “Lex.DB Performance Tips.”

  1. hi lex,

    I need a help regarding lex.db transaction scope. if error occur in one of the entity then how we can rollback whole transaction in lex.db.

    regards,

    saaed sajjad

    1. There is currently no support of transaction of ACID properties. Some of ACID properties will be supported in Lex.Db2, which is currently in works.

Leave a Reply

Your email address will not be published. Required fields are marked *