caching search results in session vs keeping large object heap clean

Question

Ok so I've been working on an ASP.NET project for a while and it seems I've made some bad design choices that are coming back to haunt me as the project keeps on getting bigger and bigger in terms of contained data.

After reading up on .NET memory management, I think I've identified a whole set of potential reasons. Since the stuff I'm doing isn't particularly special, I'm wondering if there's a standard pattern to achieve what I want to do that I'm missing.

So I have a (somewhat expensive query) which yields something between 1 and 20000 results. On subsequent requests, we may just be paging through the result set, so I store this result in the session. Session is InProc. I'm wondering:

• Does it make sense a) to store the result b) in a session c) in-process? I want the speed of (a). I don't know if there's a more efficient way than to store it by user (b) and if I use a more sophisticated state server - doesn't it rather get slower (c)? Or could that be the solution, disposing of those large objects more quickly instead of keeping the last resultset in RAM until the session expires?

• If any result set > ~ 20000 rows ends up potentially messing up the LOH, is there a generic way to get around that?

I know this question is slightly underspecified. I just realized my overall design might be flawed (w.r.t. scalability), and I'm just trying to estimate just how flawed exactly. I hope that some hints about standard patterns might be collected that turn this into a generally useful question nevertheless.

Answer 1

Why return always all records ?? I think the best way to speed up your query is to return only the data needed to user.. so only the data that fit in the page!

Try googling for ROW_NUMBER() (SQL Server) or LIMIT (mySQL).

Here are 2 goods tutorial

1) ScottGu's Blog

2) 15 Second Tutorial

Answer 2

You should try to avoid storing the results in the session. Likely your application won't work well if the user employs multiple browser tabs in the same session (it happens).

If you do use the session, definitely don't use InProc mode because as the users grow the process will eat up memory and eventually recycle and the users' sessions will be lost even if the timeout hasn't elapsed.

Try to page with the database as Keltex mentioned only pull the data that you're displaying.

Answer 3

Not knowing what your query is, but why would you pull more rows from your database than you need to show your user at one time? With good indexes, pulling up subsequent pages should be pretty quick and then you only need to do that if you need those pages.

An alternative is to just save the IDs of the resultset for the 20000 items. That way if you need to page through them, you can just pull up the individual rows quickly via a primary key.

Finally maybe you should consider using the Cache object to store your results rather than the Session. That way you let .NET decide when to dispose of objects and they don't result in a bloated Session.