2

Ok so I'm working on an ASP MVC Web Application that queries a fairly large amount of data from an SQL Server 2008. When the application starts, the user is presented with a search mask that includes several fields. Using the search mask, the user can search for data in the data base and also filter the search by specifying parameters in the mask. In order to speed up searching I'm storing the result set returned by the data base query in the server session. During subsequent searches I can then search the data I have in the session thus avoiding unecessary trips to the DB.

Since the amount of data that can be returned by a data base query can be quite large, the scalability of the web application is severily limited. If there are, let's say, 100 users using the application at the same time, the server will keep search results in its session for each separate user. This will eventually eat up quite a bit of memory. My question now is, what's the best alternative to storing the data in session? The query in the DB can take quite a while at times so, at the moment, I would like to avoid having to run the query on subsequent searches if the data I already retrieved earlier contains the data that is now being searched for. Options I've considered are creating a temp table in the DB in my search query that stores the retrieved data and which can be used for subsequent searches. The problem with that is, I don't have all too much experience with SQL Server so I don't know if the SQL Server would create temp tables for each user if there are multiple users performing the search. Are there any other possibilities? Could the idea with the temp table in the SQL Server work or would it only lead to memory issues on the SQL Server? Thanks for the help! :)

Edit: Thanks a lot for the helpful and insightful answers, guys! However, I failed to mention a detail that's kind of important. When I query the database, the format of the result set can vary from user to user. This is because the user can decide which columns the result table can have by selecting columns from a predefined multiselect box in the search mask. If user A wants ColA, ColB and ColC to be displayed in his result table, he selects those values from the multiselect box in the search mask. User B, however, might select ColA and ColC only. Therefore, caching the results in a single table for all users might be a bit tricky since the table columsn are not necessarily going to be the same for all users. Therefore, I'm thinking, I'll almost have to use an alternative that saves each user's cached table separately. The HTML5 Local Storage alternative option mentioned below sounds interesting. Since this is an intranet application, it might be fair to assume (or require) that users have an up to date browser that supports HTML5. What do you guys think? Again, thanks for the help :)

tereško
  • 56,151
  • 24
  • 92
  • 147
cbbcloud
  • 449
  • 4
  • 14

4 Answers4

1

If you want to cache query results, they'll have to be either on the web server or client in some form or another. All options will require memory, and since search results are user-specific, that memory usage will increase as a linear function of the number of current users.

My suggestions are to limit the number of rows returned from SQL (with TOP) and/or to look into optimizing your query on the SQL end. If your DB query takes a noticeable amount of time there's a good chance it can be optimized in SQL.

pseudocoder
  • 4,206
  • 2
  • 21
  • 40
1

Have you already tought about the NoSql databases?

The idea of a NoSql database is to store information that is optimized for reading or writing and is accessed with 'easy queries' (for example a look-up on search terms). They scale easily horizontally and would allow you to search trough a whole lot of data very fast (Think of Google's BigData for example!)

Wouter de Kort
  • 36,302
  • 10
  • 78
  • 99
  • Thanks a lot for the tip. However, my product is for a customer company and I don't have permission to change their database. – cbbcloud Nov 04 '11 at 14:47
1

if HTML5 is a possibility, you could use Local Storage.

Ryand.Johnson
  • 1,884
  • 2
  • 16
  • 21
  • This sounds really interesting. Is it possible to serialize whole .NET objects into Local storage and "send" it over to the client or would you have to do it on the client side using javascript? Do you happen to have some links or tutorials you could refer me to? I have not worked with HTML 5 yet. – cbbcloud Nov 07 '11 at 10:59
  • I just read [here](http://stackoverflow.com/questions/3220660/local-storage-vs-cookies) that the use of local storage should only be used if the client side needs to access the data frequently. In my scenario, however, the server needs to work more with the data than the client. Therefore, cookies might be a more suitable alternative than local storage. – cbbcloud Nov 07 '11 at 12:41
  • 1
    with cookies you are limited to 4MB per cookie and 20 total cookies per site. Do you know how much data you would be pulling down? Then you have to persist those changes back to the database and then you have to worry about concurrency. You can use the framework to help you with that task. Check out this blog. http://stephenwalther.com/blog/archive/2011/01/12/asp-net-and-html5-local-storage.aspx – Ryand.Johnson Nov 07 '11 at 16:20
0

You could try turning on sql session state.

http://support.microsoft.com/kb/317604

Plus: Effortless, you will find out if this fixes the memory pressure and has acceptable performance (i.e. reading and writing the cache to the DB). If the perf is okay, then you may want to implement the sort of thing that sql session does yourself because there is a ... Down side: If you aren't aggressive about removing it from session, it will be serialized and deserialized on each request on unrelated pages.

MatthewMartin
  • 29,993
  • 30
  • 102
  • 160