CODEWITHSUNDEEP
c#performancesqlitenhibernate
Steve
Steve

Reputation: 1316

NHibernate with linq paging performance

I'm using NHibernate on an SQLite DB in a c# project. I have a generic bulk data processing method that looks like this:

    private void DataProcess<Tobj>(int pageSize, Expression<Func<Tobj, bool>> whereClause,
        Action<Tobj, ISession> dataProcessingCallback) where Tobj : IModelBase
    {
        int offset = 0;
        bool moreToGet = true;

        while (moreToGet)
        {
            DataAccess((ISession session) =>
            {
                IEnumerable<Tobj> result = session.Query<Tobj>().Where(whereClause);
                List<Tobj> data = result.Skip(offset)
                    .Take(pageSize)
                    .ToList();
                foreach (Tobj item in data) { dataProcessingCallback(item, session); }

                if (data.Count == pageSize) { offset += pageSize; }
                else { moreToGet = false; }
            });
        }
    }

(The DataAccess method gives us our session object to work with, handles the transaction, etc.)

Having looked around, this seems pretty similar to most of the other linq paging implementations. They generally do a .Skip().Take()

My problem is that for large data sets (in the test case I'm looking at there are around 200k rows) it takes AGES (about 20 seconds in debugger) to perform the result.Skip(offset).Take(pageSize).ToList(); line. This is with pageSize = 100 and offset = 0.

My understanding was that, due to deferred execution, the SELECT wouldn't happen until it needs to (in this case .ToList()). At this point, it knows that which rows it needs to it should only select the relevant 100 rows.

'data' has the expected 100 rows but it seems as if the system has fetched all 200k odd rows from the DB and THEN done the paging in code.

Is my understanding of LINQ/NHibernate incorrect? If so, I guess I'll need to do something like this with the criteria API: NHibernate Paging performance (Better option)

Upvotes: 0

Views: 714

Answers (1)

Lukazoid
Lukazoid

Reputation: 19416

Look at the following line:

IEnumerable<Tobj> result = session.Query<Tobj>().Where(whereClause);

You are using an IEnumerable<Tobj> field, hence when you invoke Skip, you are invoking the Enumerable.Skip method. I believe what you want is the Queryable.Skip method.

Try the following line of code instead:

IQueryable<Tobj> result = session.Query<Tobj>().Where(whereClause);

This will ensure the correct extension method is invoked.

Explanation

The Enumerable.Skip method will loop over each entity in the source IEnumerable<Tobj> and only then return the results after N elements. This will force NHibernate to load all of the data into memory, as NHibernate is only aware that data is required.

The Queryable.Skip method will instead build up an expression tree so that NHibernate may handle this later when accessing the data. At that point NHibernate knows to skip the first N records.

Be warned though, I am not sure if the LINQ provider currently supports the Skip and Take methods.

Upvotes: 1

Related Questions