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.

Unregenerate

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

Gittle

Thanks. The answers have all been very useful so far, but this actually fits very nice to the issue at hand. We've started out with an Access backend. Now we're about to transition to SQL Server, however I thought if the .NET-part of the application cannot handle the memory load, there's no point in a costly db migration if performance may not increase noticably. This might however be a nice point showing how we can better optimize the .net part with a decent db behind it (assuming after a short google research that this isn't possible with access for arbitrary sortings?) – Unregenerate

well actually found a way to do pagination in access #1901135 but given the ugliness of the solution as compared to a proper sql formulation there might be still a point for a long-overdue migration – Unregenerate

one thing you can do with access is try to Paging with some SQL like this : SELECT TOP(10) FROM Table1 ORDER BY Id ... Then store the last ID in a variable and next you can do this : SELECT TOP(10) FROM Table1 Where Id > LastIdStored .... But the best way is to migrate on other DB! – Gittle

yea we have dynamic sorting criteria this isn't going to end well in access – Unregenerate

Answer 2

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.

Gaylordgaylussac

thanks. I ended up marking 2GDev's answer for the ROW_NUMBER() reference but you guys basically agree, now I have some major rebuilding coming ;) – Unregenerate

Answer 3

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.

Campus