.NET - Efficient collection of database entities?

Question

I have a Page class and a PageCollection class in a 3d party ORM framework. I can fill the PageCollection based on parameters (pageid, parentid, url etc..) (SQL query). But I need the data multiple times around the ASP.NET MVC website (Sitemap, Authentication), so I chose to load all pages 1 time and reference that (global) collection.

GlobalClass.Pages //is PageCollection containing all pages

I have now created functions which return a temporary subcollection or single entity based on the parameters mentioned before (pageid, parentid, url etc..).

GlobalClass.Pages.GetByPageId(id) //returns single page entity
GlobalClass.Pages.GetByParentId(parentid) //returns subcollection

The site however got very slow.

What is the way to go here?

• cache the subcollections (GetByParent())?
• create internal hash-lookup tables for the collection?
• Something else...?

---

Namespace BLL
Public Class PageCollection
    Inherits CustomCollectionBase

    Public Sub New()

    End Sub


    Public Sub LoadByParent(ByVal PagParent As Integer)
        If PagParent = 0 Then
            Me.whereAdd('parent IS NULL')
        Else
            Me.whereAdd('parent = ' & PagParent.ToString())
        End If

        Me.Load(Me.data)
    End Sub

    Public Function GetBySiteMapNode(ByVal node As SiteMapNode) As BLL.Page
        Return Me.GetByUrl(node.Url)
    End Function

    Public Function GetById(ByVal id As Integer) As BLL.Page
        For Each p In Me
            If p.PagAutoKey = id Then Return p
        Next
        Return Nothing
    End Function

    Public Function GetByUrl(ByVal url As String) As BLL.Page
        For Each p In Me
            If p.Url = url Then Return p
        Next
        Return Nothing
    End Function

    Public Function GetByParent(ByVal parent As Integer) As BLL.PageCollection
        Dim pc As New PageCollection
        For Each p In Me
            If p.PagParent = parent Then pc.Add(p)
        Next
        Return pc
    End Function
End Class
End Namespace

Answer 1

The first thing I would do is determine why your site is slow. If I had a dollar for every time I assumed the cause of poor performance and it turned out to be something else...

Set trace points throughout your code and figure out where you're spending your time. Your current approach may be fine but is getting hung up on some implementation detail. Only fix what you know is broken.