LightSpeed performance question

Hello,

We're considering a big change in our database structure in order to have it more flexible. Instead of having tables of different objects with many fields, we'd like to have a single table of objects and a table of fields (both tables are in 1-to-many relation). We use SQLite as database provider.

To evaluate such solution I made a prototype approach to this solution and compared it to our current solution. Unfortunately performance of a new solution appeared to be much worse (10-12 times) compared to old solution. I made also a simple approach with pure SQLiteConnection and it was not that bad.

Please download the attachment and look at the PKStorageTests.PerformanceTest() method. It creates and then reads back objects. "Small" notion means new approach with objects and fields in separate tables, "Big" means regular objects with many fields. I repeat the test 4 times, to avoid jitter overhead, etc. The times on my machine are: Iter: 3, FillDBSmall: 4572 ms Iter: 3, ReadDBSmall: 463 ms Iter: 3, ReadDBSmallADO_NET: 118 ms Iter: 3, ReadDBSmallPartial: 178 ms Iter: 3, FillDBBig: 389 ms Iter: 3, ReadDBBig: 28 ms Iter: 3, ReadDBBigPartial: 14 ms

Please compare times of ReadDBSmall and ReadDBSmallADO_NET.

My questions are:

1. Why is the duration of reading the database using LightSpeed so long compared to pure SQLiteConnection reading? I know that Lightspeed must create, register and maintain its objects, but the overhead is really huge.
2. If the overhead is caused by complexity of LightSpeed objects and cannot be simply reduced, would it be possible to provide some lightweight Lightspeed entities with limited functionality and improved performance?

With best regards, Dariusz Wąsacz, InstalSoft

You are seeing an overhead for the LINQ query to be parsed and you are seeing an overhead from object construction and wire-up between objects for tracking purposes. Since in your ADO.NET query you are doing away with both of these concerns you can approximate the query more precisely with LightSpeed by not using LINQ and using a projection returning a data reader to work with the results.

e.g. For the fields

var query = new Query(typeof(DBField));

query.Projection.Add("Id");
query.Projection.Add("FId");
query.Projection.Add("V");
query.Projection.Add("DBObjectId");

var reader = uow.Project(query);

Making that changes gives me these results which yields pretty equivalent performance by removing the overhead above.

Iter: 0, ReadDBSmall: 155 ms
Iter: 0, ReadDBSmallADO_NET: 118 ms
Iter: 1, ReadDBSmall: 103 ms
Iter: 1, ReadDBSmallADO_NET: 108 ms
Iter: 2, ReadDBSmall: 104 ms
Iter: 2, ReadDBSmallADO_NET: 109 ms
Iter: 3, ReadDBSmall: 106 ms
Iter: 3, ReadDBSmallADO_NET: 110 ms

From the original time of ~500ms, approx 200ms was spent in the LINQ parsing so simply switching to using the underlying querying API provides a definite performance gain and approx 200ms was spent in object materialization and wire-up of children. The latter wire-up due to an association is the main performance cost there, so not having these defined with a strong association would yield a performance gain.

Jeremy

Hello Jeremy,

Thank you very much for the answer. If 2/5 of the cost is spent in LINQ parsing, would it be possible to somehow prepare this LINQ earlier and then use it many times? And the wire-up: is the cost due to reflection or something else? Maybe this wire-up could be split into several threads/tasks to make it faster?

The same situation is probably while filling DB: FillDBSmall: 4572 ms - a huge time on a really fast machine (8 cores, 4 GHz AMD). I haven't made an ADO.NET equivalent, so I don't know theoretical gain, but I suppose that LINQ parsing could take a big portion of this time.

Summarizing: I don't want to get rid of LightSpeed objects, because it is the main gain I have using it. I'm asking myself if it was possible to have a better performance without losing the main functionality.

Darek Wąsacz InstalSoft

In terms of the LINQ queries, yes you could look at using compiled queries if you are able to reuse the query with the parameters being the variance, have a read through http://www.mindscapehq.com/documentation/lightspeed/Advanced-Querying-Techniques/Compiled-Queries for the detail on this. You will still have the upfront cost of compiling the query of course.

In terms of the FillDBSmall I would think that almost all of the time consumed in that operation would be from calling SaveChanges. There is quite a bit of work involved in processing the entities into SQL statements. If you are intending to do a bulk insert where you dont particularly need to deal with the objects until a subsequent querying round, performing the inserts via command objects (e.g. UnitOfWork.PrepareCommand) directly would clearly be a lot more performant.

Jeremy