Session or Cache[Resolved]

[Magiaus]

Storing object in the session. In major client machine applications offten you create views and pull all the views data onto the local machine in a data cache. This cache is used across the entire application and updated as little as possible. The concept being that in most cases unless you your self add data you will be okay till the next day when you reload the application/ the global timer hits 12hrs and it updates the data. blah blah blah. I am wondering if storing a Collection of data in the session will work in a simular manner.

I've just created a CopyTo function on my DataCollection that creates a new DataCollection holding only Data from index to (index + count), also it can't be rebound because it was created using CopyTo and it's a sub cache. This is related to the paging thread but I'm just wondering if this idea is a good one.

Run down.
Load Data to sesion. (use a flash move to display load animation and run the pages)
From that point on Bind controls using the cache or a sub cache created with CopyTo.

If/when the data changes rebind the session data cache.

It sounds good but what about my Data Transfer/Bandwidth limit is this actual going to save me bandwidth like I think it will or raise it's use?

[Magiaus]

I just read up on the Cache object and I'm still not sure if I should use it or the session. If I am understanding the way the session works correctly I could use it for this but the Cache would be the more correct way to do it.

[Magiaus]

As I was affraid I was wrong about the session and even though it uses cookies to store it's id Session Var are kept on the server so Cache is the way to go.

[Magiaus]

Well between the Cache and the partial reduction in redundent data thanks to custom paging my page got allot faster. It still takes some serious time the first time it loads on dial-up though. Up to a minute or more.

As you can guess I am loading a good bit of data. unfortunatly for this page it's only half the load.

Anyone have any sugestions on improvinbg the cache or anything?

[hellswraith]

Are you just paging a data grid?

If so, are you pulling the entire table from SQL or something and storing it in the cache?

If the answers to those are yes, I have a much better approach for you to try.

I will await your response.

[Magiaus]

Yes.

[hellswraith]

OK, here is what I did to page the results from the posts in my forums. You don't want to retrieve every record from the database, it is too inefficient, and not all the records will be looked at anyway. So, instead, only select a subset of the information at a time. Here is a procedure I used. Basically pass in the pagesize you want returned, and the current page you are on. The CatID was an extra parameter for me to narrow down the data by category, it isn't needed if you are doing a whole table.

CREATE PROCEDURE dbo.spVXPagedGetTopicList
	@PageSize int,
	@CurrentPage int,
	@CatID int
AS
	CREATE TABLE #TempTable
	(
		IncreasingID int IDENTITY(1,1),
		OtherID int
	)
	
	INSERT INTO #TempTable (OtherID)
	SELECT TopicID FROM VX_Topics WHERE CatID = @CatID
	ORDER BY LastReplyDate DESC
	
	SELECT #TempTable.IncreasingID, VX_Topics.*
	FROM #TempTable
	INNER JOIN VX_Topics ON
	VX_Topics.TopicID = #TempTable.OtherID
	WHERE IncreasingID > @PageSize * @CurrentPage
	AND IncreasingID <= @PageSize * (@CurrentPage+1)
	ORDER BY #TempTable.IncreasingID
	RETURN

So, that solves the stored procedure side of things, but on the grid you need to handle some stuff. That comes in my next post.

[hellswraith]

Here is the datagrid html code on the page:

<asp:datagrid id="TopicListGrid" runat="server" AllowPaging="True" PageSize="30" AllowCustomPaging="True"
	Width="95%" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="3"
	GridLines="Vertical" AutoGenerateColumns="False">
	<SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#008A8C"></SelectedItemStyle>
	<AlternatingItemStyle BackColor="Gainsboro"></AlternatingItemStyle>
	<ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>
	<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084"></HeaderStyle>
	<FooterStyle ForeColor="Lime" BackColor="White"></FooterStyle>
	<Columns>
		<asp:TemplateColumn HeaderText="Thread">
			<ItemTemplate>
				<asp:HyperLink id=HyperLink1 runat="server" Text='<%# FormatPostTitle(DataBinder.Eval(Container, "DataItem.Title")) %>' NavigateUrl='<%# DataBinder.Eval(Container, "DataItem.TopicID", "PostDetails.aspx?PostID={0}") + DataBinder.Eval(Container, "DataItem.CatID", "&amp;CatID={0}") %>'>
				</asp:HyperLink>&nbsp;
				<asp:Literal id="litPageLinkCode" runat="server" Text='<%# FormatPagesLink(DataBinder.Eval(Container.DataItem, "ReplyCount"), DataBinder.Eval(Container, "DataItem.TopicID", "PostDetails.aspx?PostID={0}") + DataBinder.Eval(Container, "DataItem.CatID", "&amp;CatID={0}")) %>'></asp:Literal>
			</ItemTemplate>
		</asp:TemplateColumn>
		<asp:TemplateColumn HeaderText="Thread Starter">
			<ItemTemplate>
				<asp:HyperLink runat="server" Text='<%# FormatUserName(DataBinder.Eval(Container.DataItem, "UserName")) %>' NavigateUrl='<%# DataBinder.Eval(Container, "DataItem.UserName", "MemberDetails.aspx?UserName={0}") %>'>
				</asp:HyperLink>
			</ItemTemplate>
		</asp:TemplateColumn>
		<asp:BoundColumn DataField="ReplyCount" HeaderText="Replies">
			<HeaderStyle Width="10%"></HeaderStyle>
			<ItemStyle HorizontalAlign="Center" VerticalAlign="Middle"></ItemStyle>
		</asp:BoundColumn>
		<asp:BoundColumn DataField="ViewCount" HeaderText="Views">
			<HeaderStyle Width="10%"></HeaderStyle>
			<ItemStyle HorizontalAlign="Center" VerticalAlign="Middle"></ItemStyle>
		</asp:BoundColumn>
		<asp:TemplateColumn HeaderText="Last Post">
			<HeaderStyle Width="20%"></HeaderStyle>
			<ItemTemplate>
				<asp:Label id=Label1 runat="server" Text='<%# EncodeLastPostByInfo(DataBinder.Eval(Container, "DataItem.LastReplyUserName"), (System.DateTime)DataBinder.Eval(Container, "DataItem.LastReplyDate")) %>' Font-Size="X-Small">
				</asp:Label>
			</ItemTemplate>
			<EditItemTemplate>
				<asp:TextBox id=TextBox1 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.LastReplyDate") %>'>
				</asp:TextBox>
			</EditItemTemplate>
		</asp:TemplateColumn>
	</Columns>
	<PagerStyle HorizontalAlign="Center" ForeColor="Blue" BackColor="Silver" PageButtonCount="4"
		Mode="NumericPages"></PagerStyle>
</asp:datagrid>

Next post will be the events to fill it.

[hellswraith]

This is my page code, notice that I load the first page worth of data during load the first time, after that, on postbacks I don't load it anymore, that is handled by the PageIndexChanged event of the grid.

private void Page_Load(object sender, System.EventArgs e)
{			
	if(!Page.IsPostBack)
	{
		DataAccess.Post po = new DataAccess.Post();
		
		TopicListGrid.VirtualItemCount = po.GetTotalItemsInQueryPosts(catID);
		TopicListGrid.CurrentPageIndex = 0;

		BindData();
	}
}

private void TopicListGrid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
	TopicListGrid.CurrentPageIndex = e.NewPageIndex;
	BindData();
}


private void BindData()
{
	DataAccess.Post po = new DataAccess.Post();
	TopicListGrid.DataSource = po.GetPostList(TopicListGrid.PageSize, TopicListGrid.CurrentPageIndex, catID);
	TopicListGrid.DataBind();
}

[hellswraith]

Here is the last bit of code that is part of this solution. You probably noticed in the above post I was calling a custom Post object. Here are the methods that were being called:

		/// <summary>
		/// Used to pull paged posts from the topic table.
		/// </summary>
		/// <param name="PageSize">How many records to retrieve.</param>
		/// <param name="CurrentPage">The 'page' of data to retrieve</param>
		/// <param name="CatID">The category the posts belong to.</param>
		/// <returns>SqlDataReader holding the recordset of posts.</returns>
		public SqlDataReader GetPostList(int PageSize, int CurrentPage, int CatID)
		{
			SqlParameter[] parameters =
				{
					new SqlParameter("@PageSize", SqlDbType.Int, 4),
					new SqlParameter("@CurrentPage", SqlDbType.Int, 4),
					new SqlParameter("@CatID", SqlDbType.Int, 4)};

			parameters[0].Value = PageSize;
			parameters[1].Value = CurrentPage;
			parameters[2].Value = CatID;

			return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, "spVXPagedGetTopicList", parameters);
		}

		/// <summary>
		/// Counts the total items so we can page the posts page.
		/// </summary>
		/// <param name="CatID">Category ID of the posts.</param>
		/// <returns>Integer representing the total post count.</returns>
		public int GetTotalItemsInQueryPosts(int CatID)
		{
			string sqlString = "SELECT COUNT(*) FROM VX_Topics WHERE CatID = " + CatID.ToString();
			return (int)SqlHelper.ExecuteScalar(connectionString, CommandType.Text, sqlString);
		}

[hellswraith]

Sorry for the multiple posts, but it was needed as there was four different sections of the code, and I can only post so many characters each post on these forums.

[Magiaus]

I see. I'm going to have to think about it but I see.

[Magiaus]

Final I get to work on this again....

Okay my questions are about the SQL.

1) What does #TempTable do/represent? I mean is it a parameter or is it something else?

2) The increasing ID takes care of the whole selecting the right group of info correct? And OtherID is used to Join the Data from whatever tables I am going to join/it's a primary key.

This is very cool. I think I need a book on SQL Server and Transact SQL though.

[Magiaus]

I see now. Very nice by the way.

I still need to do some reading on sql servers version of sql though.

Thanks.

I have one more question. Since Create Table is fired every time we call this procedure what happens on the sql server side of things? Does it throw an exception or just drop the existing table and create the new one?

[Magiaus]

Not to redundantly say thanks and what not but Thanks. I just got it working 100% the way it should and unless there is something going on I don't see I'm happy.

I would still like to know what #TempTable does though and if the table gets dropped on it's own or not. Also, I noticed it isn't a Temporary Table, is that the way it needs to be?

One more question. I noticed you using DataBinder.Eval are you doing that for a reason or did you just do it that way? I'm asking because it is supposed to be high cost to use it and I have been trying to avoid it where I can. Nevermind I just reread the grid html and I see that you are doing formatting.

Thanks.

[hellswraith]

Sorry, been busy with work and had the flu, so I haven't been online last couple days much.

The #tempTable is a table variable so to say. SQL creates it like we told it to, and destroys it when it goes out of scope. You shouldn't have to worry about it at all. Nothing is written to disk or anything from my understanding.

I got the idea from a book that I highly recommend. It is:
http://www.amazon.com/exec/obidos/tg...glance&s=books

Glad to see you got it working, I know it was kind of hard to digest, and I will try to write up a mini tutorial on it sometime in the future to explain it in more detail. Who knows when I would have the time though.

[Magiaus]

Ugh, I have flu right now. I hadn't had time to look at it until today, but once I actual read the stored procedure a few times it pretty much all became clear.

This is the kind of thing that should be in this Essential ASP.Net with Examples in C# book I have. It's a good book, but nothing in it is nearly as useful as this little trick.

I need to get a new bank card the book stores don't have anything nearly on that level it's all basic stuff.