Enhance performance with for loop and linq query

Question

i have an object array and i need to get some object Info from database based on certain parameters, i am implementing this:

public IList<object[]> GetRelevants(Registro[] records)
    {
        List<object[]> returnList = new List<object[]>();
        using (var session = SessionFactory.OpenStatelessSession())
        {
            for (int kr = 0; kr < records.Length; kr++)
            {
                Registro record = records[kr];
                var query = session.QueryOver<Registro>()
                                    .Where(sb => sb.Asunto == record.Asunto)
                                    .And(sb => sb.FechaInicial == record.FechaInicial)
                                    .And(sb => sb.FechaFinal == record.FechaFinal)
                                    .And(sb => sb.FoliosDesde == record.FoliosDesde)
                                    .And(sb => sb.FoliosHasta == record.FoliosHasta)
                                    .And(sb => sb.TomoNumero == record.TomoNumero)
                                    .And(sb => sb.TomoTotal == record.TomoTotal)
                                    .And(sb => sb.SerieCodigo == record.SerieCodigo)
                                    .And(sb => sb.Caja == record.Caja)
                                    .And(sb => sb.Carpeta == record.Carpeta).SelectList(list => list
                                      .Select(p => p.Id)
                                      .Select(p => p.NuevaCaja)
                                      .Select(p => p.NuevaCarpeta)
                                      .Select(p => p.Periodo));

                var result = query.SingleOrDefault<object[]>();
                returnList.Add(result);
            }
        }
        return returnList;
    }

In records however, there are more than 10000 items so NHibernate takes about 10 minutes to do that.

Is there any way to enhance performance in this?

Answer 1

A good solution would probably be to ditch NHibernate for this task and insert your data into a temporary table, then join on that temporary table.

However, if you want to use NHibernate, you could speed this up by not issuing 10,000 separate queries (which is what's happening now). You could try to break your query into reasonably sized chunks instead:

List<object[]> ProcessChunk(
    IStatelessSession session,
    int start,
    IEnumerable<Registro> currentChunk)
{
    var disjunction = Restrictions.Disjunction();

    foreach (var item in currentChunk)
    {
        var restriction = Restrictions.Conjunction()
            .Add(Restrictions.Where<Registro>(t => t.Asunto == item.Asunto))
            /* etc, calling .Add(..) for every field you want to include */

        disjunction.Add(restriction);
    }

    return session.QueryOver<Registro>()
        .Where(disjunction)
        .SelectList(list => list
            .Select(t => t.Id)
            /* etc, the rest of the select list */
        .List<object[]>()
        .ToList();
}

Then call that method from your main loop:

const int chunkSize = 500;   

for (int kr = 0; kr < records.Length; kr += chunkSize)
{
    var currentChunk = records.Skip(i).Take(chunkSize);

    resultList.AddRange(ProcessChunk(session, i, currentChunk));
}

What you're doing here is issuing 20 (instead of 10,000) queries that look like:

select
    /* select list */
from
    [Registro]
where
    ([Registro].[Asunto] = 'somevalue' and ... and ... and .. ) or
    ([Registro].[Asunto] = 'someothervalue' and ... and ... and ... )
    /* x500, one for each item in the chunk */

and so on. Each query will return up to 500 records if 500 is the size of each chunk.

This is still not going to be blazing fast. My local test about halved the running time.

Depending on your database engine, you might quickly run up against a maximum number of parameters that you can pass. You'll probably have to play with the chunkSize to get it to work.

You could probably get this down to a few seconds if you used a temporary table and ditched NHibernate though.