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