1

I have an application that performs complex queries against what amounts to data organized in a "star schema". The gold-owner keeps adding new "axes" to perform searches on, with the result that performance becomes worse over time. Currently, the execution of a search operation, using a stored procedure to do all the work on the SQL server, takes about 2 seconds, which doesn't fit the gold-owner's desire to have the code be interactive (<0.1 sec response time). Looking at the SQL Server query analyzer, the search is IO-bound on 9 table scans of 100,000 records, and then doing brutal joins. Due to the nature of the queries I need to perform and the limitations of SQL, this cannot be improved.

In desperation, I've rewritten the query processor so that it sucks in the 100,000 records into a cache at application start, then perform the complex queries against the cached memory. Loading all the records from the database takes about 12 seconds. This expensive initial load is mitigated by my rewritten query processor. It now only needs to do a single scan through the records, and gives a response time of 0.02 seconds.

This good news is tainted by the gold-owner's discovery that the 12-second hit for populating the cache is being experienced every hour or so. I'm currently storing the data in the ASP.NET application state, as Application["FactTable"]. It seems the application state is being reset after the ASP.NET application is idle for longer than a dozen minutes or so.

If I move the 100,000 records into the ASP.NET application cache, will I be experiencing these evictions just as often, or can I rely on the data remaining in memory for the fast retrievals for longer periods of time? If the ASP.NET cache is also victim to application resets, what other mechanism should I use? A separate app domain hosting an instance of my database cache comes to mind, but I don't want to go down that route unless my other options are closed off.

John Källén
  • 7,551
  • 31
  • 64
  • 1
    What about adjusting the recycle time for the app pool you're running under? In IIS 7 you can adjust the recycling conditions to regular intervals, fixed number of requests, specific times or even memory used. Perhaps a fixed time(s) recycle? If you go down this route, however, I would suggest using a dedicated app pool that only runs that application, so you don't run into resource constraints from other apps (if you have them) running in the same pool. – Tim May 02 '13 at 07:40
  • @Tim: recycling is currently set at every 1740 minutes, which doesn't match the gold-owner's perceived 12-second delay every 15 minutes or so. I'm currently trying to see if there is anything that could be restarting the application that often, but so far I haven't identified any obvious culprits. – John Källén May 02 '13 at 07:45
  • Is the gold-owner using the application consistently when he experiences this issue? Idle time-out has a default of 20 minutes. – Tim May 02 '13 at 07:47
  • @Tim: he works for a while, then leaves the application. Certainly after 20 minutes of idleness he is seeing the delay. However, I'm storing the data in the Application state, not the Session state. Do idle time-outs reset the Application state as well as Session state? – John Källén May 02 '13 at 07:50
  • Idel timeouts will stop all idle worker processes so all cached data will be disposed. – Peter Kiss May 02 '13 at 07:54
  • My quick research agrees with @PeterKiss. Try increasing the idle timeout on the application pool to a larger number (or set it to never timeout). Just be aware of the possible performance/resource impact if you have other apps running in the same pool, as I mentioned before. – Tim May 02 '13 at 07:56
  • I've bumped up the idle timeout on our sandbox server to 2000 minutes -- which in practice makes it infinite as we always have visitors -- to see how the app behaves. @Tim: your concerns are still valid. I estimate the in-memory load of the cached 100,000 rows to be about 100 MiB. So far I'm not seeing any perf degradation on the server but will keep monitoring. – John Källén May 02 '13 at 08:03
  • Would you like to try NoSQL storage, try Redis, or Memcached or even MongoDB. I would use Redis as temp storage – Andrei May 02 '13 at 09:34
  • try to log application restart, see this http://stackoverflow.com/a/6259182/351383 – Antonio Bakula May 02 '13 at 10:11

1 Answers1

1

I realise that you have a lot of data and processing and you must have tried a few things to speed this scenario up, but using Application State which is managed by IIS will be volatile...

Have you thought of running the your calculations etc in another process, ie, create a windows service that periodically runs the queries to organise your data and save that "flat" data to a database cache. When the user requests the data, they will just get the last DB cached results... and then further speed this up by holding those results in the Application state which can just refresh itself if that gets destroyed?

Mark Redman
  • 24,079
  • 20
  • 92
  • 147
  • This is indeed what we ended up doing. We keep both the "structured" data, complete with relations, and "flattened" data as relation in the database. In addition, we keep an in-memory copy of the flattened data in the App state. When we need to rebuild the flattened data, the application can use the App data during the 12 - 15 seconds, after which the App state is reloaded with the new flattened data. – John Källén Jun 25 '13 at 10:40