Flush very slow

Mar 15, 2011 at 9:34 AM

Hey guys,

We successfully implemented the Sterling database in our Silverlight application and it's working fine except for one problem. The flush method seems to take a very long time. First we flushed the database every time we saved something, which can literally take up to 90 seconds. We're looking for a way to handle this. Basically, we're going on the assumption a flush is necessary to save the index keys of the database and if we don't use a flush the database will be corrupt.

A few things we tried or we're trying at the moment:

We tried flushing the database in a different thread but if you make a call to the database while flushing you get all sorts of threading issues.

We tried only using a flush when deleting something, because this causes the errors. However this doesn't seem very good practise and causes potential errors.

We're now trying to flush when users exit the application in the on_close event.

Does anyone have any suggestions? I'm sure this problem has come up before.

Mar 16, 2011 at 1:31 PM

How many instances do you have? That will help speak to any potential issues. I'd be curious if the latest drop still has threading issues on a separate thread as well ... will need to wait a bit for me to check in the phone builds. How many instances in your table? How many indexes per type?

Mar 16, 2011 at 2:44 PM

Hey Jeremy,

Thanks for your time. I'm not quite sure what you mean with how many instances so I'll give you some information I have at hand and hope that this can give you some idea of what's going on. Could you maybe specify what you mean with instances?

Anyway, here's where we register the tables and create the index:


 protected override List _RegisterTables()
            return new List
                            CreateTableDefinition(databaseInfo => databaseInfo.ID),
                            CreateTableDefinition(record => record.RootEntityID)
                                .WithIndex(INDEX_SNAPSHOTRECORD_ID, record => record.RootEntityID)    
                                .WithIndex(INDEX_SNAPSHOTRECORD_ASSEMBLY_QUALIFIED_NAME, record => record.AssemblyQualifiedName)
                                .WithIndex(INDEX_SNAPSHOTRECORD_ASSEMBLY_QUALIFIED_NAME_BASE_CLASS, record => record.AssemblyQualifiedNameBaseClass),
                            CreateTableDefinition(record => record.RootEntityID)
                            .WithIndex(INDEX_ROOTENTITYRECORD_ID, record => record.RootEntityID)    
                            .WithIndex(INDEX_ROOTENTITYRECORD_ASSEMBLY_QUALIFIED_NAME, record => record.AssemblyQualifiedName)
                            .WithIndex(INDEX_ROOTENTITYRECORD_ASSEMBLY_QUALIFIED_NAME_BASE_CLASS, record => record.AssemblyQualifiedNameBaseClass),
                            CreateTableDefinition(record =>record.RootEntityID)
                            .WithIndex(INDEX_ENTITYRELATIONRECORD_ID, record=>record.RootEntityID)
                            .WithIndex(INDEX_ENTITYRELATIONRECORD_ASSEMBLY_QUALIFIED_NAME , record=>record.AssemblyQualifiedName)
                            .WithIndex(INDEX_ENTITYRELATIONRECORD_ASSEMBLY_QUALIFIED_NAME_BASE_CLASS , record=>record.AssemblyQualifiedNameBaseClass)
                            .WithIndex(INDEX_ENTITYRELATIONRECORD_SOURCEID , record=>record.SourceEntityGuid)
                            .WithIndex(INDEX_ENTITYRELATIONRECORD_TARGETID , record=>record.TargetEntityGuid)

From what I'm aware of is that these indexes is what gets persisted when we flush the database, and persisting these takes pretty long for us.
As you can see we basically have three different Definitions, the SnapShots, RootEntities and EntityRelations. How this works isn't very important for you, since it's specific for our software. What you should know is that for every RootEntity we have a SnapShot, which is a slimmed down version of the RootEntity. We have about 50 different kinds of RootEntities and a big customer of ours has about 20.000 RootEntities. So that's 20.000 SnapShots as well and usually not much less EntityRelations.

Is this what you were asking about? If not please specify what you mean so we can give you useful information. Being able to call the Flush in a seperate thread would solve all our problems.

Mar 17, 2011 at 11:45 AM

20,000 may be pushing the limits, especially on the phone, of what Sterling can handle. However, I also see some issues above:

1. The key is automatically an index, so for the entry record=>record.RootEntityID as a key, you definitely don't need the record => record.RootEntityID as an index

2. Indexes are serialized to separate files. Isolated storage penalizes us per write due to quota tracking and abstractions. I would consolidate the AssemblyQualifiedName and AssemblyQualifiedNameBaseClass into a single index (you can do an index with two values)

3. Same with the SourceentityGuid & TargetEntityGuid, I'd consolidate those into a single index

If you do that, it will remove several of the required writes and may get you closer to the zone you are looking for. Let me know if that helps.