Pages

Monday, May 11, 2015

Nhibernate paging for time consuming operations

Imagine, you have to implement batching for a time consuming operation using NHibernate. I would write it like this:

var items = new List<Item>();
for (var page = 0; page == 0 || items.Any(); page++)
{
    items = GetListOfItems(page);
    foreach (var items in items)

    {
        DoStuff(item);
    }
}

public void GetListOfItems(int page)
{
    return CurrentSession.QueryOver<Item>()
        .Select(Projections.Id())
        .Take(PageSize)
        .Skip(PageSize * page)
        .List();
}

But what I found is that if the DuStuff is time consuming then it is possible to have duplicated items from the query. Thatis because of NHibernate generated query:

SELECT TOP (@p0) y0_
FROM (
  SELECT this_.Id AS y0_
  ,ROW_NUMBER() OVER (
    ORDER BY CURRENT_TIMESTAMP
  ) AS __hibernate_sort_row
  FROM contact.sfContact this_
) AS query
WHERE query.__hibernate_sort_row > @p1
ORDER BY query.__hibernate_sort_row;

That order by CURRENT_TIMESTAMP... If you want consistent results you would have to apply specific ordering:

public void GetListOfItems(int page)
{
    return CurrentSession.QueryOver<Item>()
        .Select(Projections.Id())
        .OrderBy(i => i.Id).Asc
        .Take(PageSize)
        .Skip(PageSize * page)
        .List();
}

No comments:

Post a Comment