current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have noticed that our web application queries a particular table an enormous amount of times. The table is relatively small, with only about a hundred rows that are used.

I'm wondering if there is a way to store this table once every 15 minutes or so in memory in the website application, so the system doesn't have to make so many queries to get the same information over and over again. This would be available across many different users.

The table is the Client table, so users login from many different clients. The data is pretty static, probably getting updated perhaps once a day.

Updates: SQL profiler is showing the query is run quite a bit, so that's what concerns me. The website is not notably slow. I just thought this could help make it even faster.

share|improve this question
2  
Do these queries actually slow the application down appreciably? Is the table just used as a lookup or does it participate in other queries or stored procedures that your caching won't address? Before you decide to cache the table, make sure you're going to get a noticeable benefit. –  Bob Kaufman Jul 12 '13 at 16:53
 
If it doesn't affect the site performance you should let it be –  codingbiz Jul 12 '13 at 16:55
 
Are you using ASP.Net or ASP.Net MVC for your web application? –  Yogendra Jul 12 '13 at 17:05
add comment

3 Answers

up vote 4 down vote

If the table is small and frequently queried, there is an outstanding chance that the data and any indices is entirely in SQL Server's memory, the query plan is cached, and that the query will be extremely fast.

Measure the actual performance impact before making any changes.

If you see there is a performance impact, there are many caching strategies that you can use to reduce trips to the database. More information about access patterns to the table and the need for information consistency would be needed to recommend a specific caching strategy.

You state

to get the same information over and over again

but also state

once every 15 minutes

If the information really is the same over and over, you can load it once into the ASP.Net cache at application start. If it might change every so often, but it is OK for the data to be a little out-of-date for a given user, you can use a time-based cache expiration policy. If the data changes only every so often but must be up-to-date immediately after it changes, you can consider a SQL Dependency for cache expiration.

For more information on ASP.Net caching see

http://msdn.microsoft.com/en-us/library/xsbfdd8c(v=vs.100).aspx

and specifically

http://msdn.microsoft.com/en-us/library/6hbbsfk6(v=vs.100).aspx

share|improve this answer
 
I guess I'm just seeing a lot of hits when I run SQL Profiler on it. –  Narnian Jul 12 '13 at 16:54
 
Don't spend time tuning code without knowing how it really impacts overall application performance. It is straightforward to measure the impact e.g. using the profiler built in to Visual Studio. –  Eric J. Jul 12 '13 at 16:55
add comment
up vote 0 down vote

My suggestion would be to create a WCF windows service - using REST you could easily cache the SQLDataReader (or other DataReader) and implement a TTL metric to re-query at an interval.

share|improve this answer
add comment
up vote 0 down vote

Well,there is few solutions.

  1. If you want to load data in memory every 15 minutes you should use some of the .net caching library's,for example system .NET Caching where you could set expiration polices,and other.

  2. You could try optimize you query with nonclustered indexes

  3. You could use App Fabric caching,or something similar

  4. And last,try to add more memory on sql server server
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.