Help! Site crashing on data access when busy!

Clearly, my code isn't written as well as it should be. I don't understand enough about data access and could use some help.

I have several database tables, but one primary table that is the most accessed. Generally, I need to build a list from the data based on some filter. I'm using a repeater control, since all I need to display per record is a name, maybe a city or birthday, and possibly a little graphic, and my customer doesn't want a grid type of display. The filter is determined by the page requested. The exception is a search page where the user builds the filter and a grid is used to display the results.

The results always contain a link to a page that has more detail on the selected record.

What is the best way to handle this? I'm still trying to get a handle on different ways to get data and I'm not doing much with caching. Would it make sense to keep the data in memory from the page that displays the list (or search page) to the detail page? What if the detail page is accessed directly, say from a bookmark? How do I cache this?

I'm currently using strongly typed datasets.

Below is an example of what I'm doing - this is from the code behind of one of the list pages - members with birthdays this month.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim theMonth As String = DateTime.Now.ToString("MMMM")
Me.LabelMonth.Text = theMonth

Dim MemberAdapter As New WAPTableAdapters.membersTableAdapter
Repeater1.DataSource = MemberAdapter.GetBirthday("Female", DatePart("m", Today))
Repeater1.DataBind()
Repeater2.DataSource = MemberAdapter.GetBirthday("Male", DatePart("m", Today))
Repeater2.DataBind()
End Sub

Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound
If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)
Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow)
If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then
LabelIcon.Visible = True
End If
End If
End Sub

Protected Sub Repeater2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater2.ItemDataBound
If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)
Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow)
If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then
LabelIcon.Visible = True
End If
End If
End Sub

It seems pretty simple and straightforward to me, but these pages shouldn't be crashing when the site gets busy, so I have to be doing something wrong.

Diane

I would definitely cache the datasets. It takes so much load off.

|||

How do I do that?

Diane

|||

I would probably put the repeaters into a user control and use partial page caching for it. Say update once per minute should help reduce your server load, and it's really easy to implement (And since you've moved it to a user control, you can reuse it on a different page if necessary). There are obviously more efficient caching mechanisms to gain even more efficiency, but I would start with that and see if it helps since it's so easy to implement.

http://asp.net/learn/videos/video-41.aspx

|||

If Cache("malebirthday") is nothing then

Dim ds as dataset = MemberAdapter.GetBirthday("Male", DatePart("m", Today))

Repeater2.DataSource = ds

Cache.Insert("malebirthday", ds, Nothing, now.addminutes(5), timespan.zero)

else

Repeater2.DataSource = Ctype(Cache("malebirthday"), dataset)

End if

Repeater2.DataBind()

|||

Thank you! That helps tremendously.

Diane

sql