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.