Currently I am maintaining a legacy C# application that uses Castle ActiveRecord to retrieve data from the database. Our client has complained about the performance of the application when a certain page is requested. The page in question shows a table with a large number of invoices. This is done through the ActiveRecordMediator.FindAll method that takes a parameter for the requested type and a parameter for an ICriterion expression. Once the gridview is filled with the array of invoices a very large number of queries is executed. I suspect that this is causing the bad performance. Ideally I would like to limit the number of queries that are executed. Unfortunately my attempts so far have not been successful since I am unfamiliar with Castle ActiveRecord and ORMs in general.

I presume this problem is related to lazy loading and possibly the 'select N+1' problem. I have tried adjusting the FetchMode to no avail. So far Google searches on how to solve this problem related to the 'ActiveRecordMediator' have not resulted in any applicable strategies. So I was hoping someone could point me in the right direction on how to improve the performance of the 'ActiveRecordMediator.FindAll' method.

Below you will find a condensed version of the code that results in the numerous queries.

Methods that are called on page load:

var invoices = new List<Invoice>(
     Invoice.GetAll(Expression.Eq("BetaalSpecificatieVerstuurd", false)))

invoices.Sort(
    new Comparison<Invoice>(
        delegate (Factuur f1, Factuur f2)
        {
            return -1 * f1.ID.CompareTo(f2.ID);
        }));

// After this point all the queries are executed.
gridviewList.DataSource = invoices;
gridviewList.DataBind();

GetAll method:

public static T[] GetAll(ICriterion criterion)
{
    if (criterion == null)
        throw new ArgumentNullException("criteria");

    return Source.FindAll(typeof(T), criterion) as T[];
}

FindAll method implementation:

public Array FindAll(Type t, ICriterion criterion)
{
    ICriterion additionalCriterion = Expression.Eq("CompanyID", CurrentCompanyId);
    ICriterion criterionToApply = Expression.And(criterion, additionalCriterion);

    return ActiveRecordMediator.FindAll(t, criterionToApply);
}

Query that gets executed multiple times:

SELECT
    this_.ID as ID0_0_,
    this_.var1 as var12_0_0_,
    this_.var2 as var23_0_0_,
    this_.var3 as var34_0_0_,
    this_.var4 as var45_0_0_,
    ...
FROM
    Opmerking this_
WHERE
    ((this_.RelevantId = @p0 and this_.Type = @p1) and
    this_.CompanyID = @p2)
ORDER BY
    this_.IsImportant desc,
    this_.Processed asc,
    this_.Date desc

EDIT:

After some help from @mjwills I was able to isolate the problem. In the .ascx webpage different properties of the class Invoice were displayed in a gridview. Most of them were already loaded because they were contained in the database table invoices. However a field 'remarks' was not. This resulted in a call to the database every time the 'remarks' field was retrieved. My first hunch is to eagerly load the remarks data as well. I am unsure how this can be achieved. Will update when I know more.

0

There are 0 answers