Optimising performance

Hi,

I'm using Lightspeed 5.0.2590.0 with VistaDB 4.1

I've a nested entity structure that matches a large collection of XML documents broken down into folders, sections pages and page content. I'm parsing all of this and placing both the hierarchy and ultimately some selected page content into a my database.

In total I'm syncing ~ 2000 particular content items from within ~ 3000 pages over 200 sections from a pool of 7 folders. The 'files' are already within a separate application (OneNote) so there's no filesystem I/O, I can rapidly get the XML using a COM API.

The first parsing run took > 10 mins. I've since spent considerable time re-organising my logic as well as tweaking how I use Lightspeed, for a start I stopped calling SaveChanges() all the time and now leave that to a single call right at the end of the parsing tree. I've also removed the identity auto increment field from the PK of each table and am allowing Lightspeed to use a keytable, I've also upped the blockcount to 100 and have also added some indexes where relevant.

This has got me down to 4 mins 30.

I then tried enabling Eager Referencing but that added a minute back on to the sync time up to 5:35.

Whilst I'm not expecting this to be insanely fast due to the xDocument parsing overhead (and probably some further inefficiencies on my part) when I enable the tracelogger I'm seeing far more SQL activity than I was expecting so i was hoping to understand a bit more what's going on when I query a Lightspeed entity to see if I can improve my logic and possibly further reduce sync time.

Take this (rough!) pseudo C# example:

public void Sync(someXml)
{
    //process XML, this is made up, for brevity
    var incomingSections = processedXml;

    using (_unitOfWork)
    {
        syncSections(incomingSections);

        _unitOfWork.SaveChanges();
    }
}

private void syncSections(incomingSections)
{
        foreach (var incomingSection in incomingSections)
        {
            var existingSection = _unitOfWork.Sections.SingleOrDefault(o => o.someProperty == incomingSection.someProperty);

            if (existingSection != null)
            {
                existingSection.someOtherProperty = incomingSection.someOtherProperty;
            }
            else
            {
                var newSection = new Section
                {
                    someProperty = incomingSection.someProperty,
                    someOtherProperty = incomingSection.someOtherProperty,
                };
                _unitOfWork.Add(newSection);

                // now sync its Children
                syncPages(newSection);
            }
        }
    }
}

private void syncPages(section)
{
    // more iterations, per page
    syncContent(aPage);
}

private void syncContent(page)
{
    // do even more work on content nodes
}

So my question is what's happening when I call var existingSection = _unitOfWork.Sections.SingleOrDefault(o => o.someProperty == incomingSection.someProperty);

Looking at the tracelogger every time I refer to the existing entity I'm seeing a select, I had naively assumed that on the first select it would load all sections and then I could refer to these in memory?

I'm also seeing lots of updates well before we get to the end of the sync process, in this case I assume there's a full SaveChanges() every time Lightspeed needs to reserve some more keys?

Calling .SingleOrDefault performs a standard find (and then expects there to be a single instance). Because there is a query in play we will trigger a database call for each call you make here. If you want to "load all sections" and then deal with that in-memory first just assign a variable to _unitOfWork.Sections.ToList() and then you will have an in-memory set to work with for your subsequent queries.

On the update side of the world, no, key reservation is handled at a separate transaction if needed (e.g. KeyTable) and we dont call SaveChanges as part of this. If we run out of keys during allocation we need to fetch more and this will result in update calls for KeyTable if you are using that strategy.

Jeremy