CODEWITHSUNDEEP
c#sql-serverdatabaselinq-to-sql
ango
ango

Reputation: 859

Caching some tables of a Database in RAM

I am building a database on SQL Server.

This DB is going to be really huge. However, there are few tables which need to be queried very frequently and are quite small.
Is there a way to cache these tables in RAM for faster querying ?

Any ideas/links to make the database insertions/query faster will be highly appreciated.
Also, do I get any performance boost if I migrate from SQL Express to SQL Server Enterprise ?

Thanks in advance.

Upvotes: 0

Views: 1972

Answers (5)

Ben Thul
Ben Thul

Reputation: 32687

Depending on how often these small tables are changed, Query Notifications might be a good option. Essentially, you subscribe your application to changes in a data set in the db. A canonical example is a list of vendors. Doesn't change much over time but you want the application to know when it does change.

Upvotes: 0

Gulli Meel
Gulli Meel

Reputation: 891

More a page is used by queries more are chances that the page will be in memory.But it will be at page level rather than table level. Everytime it will be referenced its count will be increased and a background process (lazy writer) usualy decrease the count for all the pages. When a new page is required to bring to memory ;sql server will write the page with least count to disk.Thus if your table's pages are accessed frequently there are high chances that the count will be high and thus those will stay in memory for longer.But if you will have some kind of a big query which reads lots of data from different tables which say is more than your memory then even those pages might be thrown out of the cache.But if you do not have those kind of queries then the chances are high that pages will stay in the memory.

Also, it means the same page is accessed a number of times.If diff processes will read diff pages from same table then you might not have very high use count for all of your pages and thus some of them could be written to disk.

Read below blog for more details on how buffers etc works.

http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/bufferpool-performance-counters.aspx

Upvotes: 0

user841123
user841123

Reputation:

If you are working with the C# then you may have try data caching.
You just need to follow 3 steps:

  • Fetch your result to a list
  • Now cache the list of data
  • Whenever you need to query cache result, cast your cache object to concern list type.

Following is the example code:

List<type> result = (Linq-query).ToList();
Cache["resultSet"] = optresult;
List<type> cachedList = (List<type>)Cache["resultSet"];

Now you may perform Linq query over cachedList which actually uses cached object.

Note: For caching any object you may use more precise approach like following, this provides better control over caching.

Cache cacheObjectName = new Cache();
cacheObjectName.Insert("Key", value, Dependency, DateTime, TimeSpan, CacheItemPriority, CacheItemRemovedCallback)

Upvotes: 0

Prince
Prince

Reputation: 199

You can read the data from the table and store into the DataTable Variable。 You Should create suitable index and you and make the query faster.

Upvotes: 0

Eric J.
Eric J.

Reputation: 150108

SQL server will do an outstanding job of keeping small tables that are frequently accessed in RAM.

However, a small frequently accessed table does sound like a good candidate for caching at the application layer to avoid ever hitting the database.

If your database really is "huge", you will hit the 1GB RAM limit of SQL Express (and/or the 10GB per DB storage limitation) and will want an edition that does not have that constraint.

http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx

Upvotes: 2

Related Questions