Caching a table

Question

Post reply

Caching a table

neelsmail

Old Hand

Points: 320
More actions
February 14, 2010 at 2:38 am

#229952

Hi!
I am trying to find best strategy for following scenario:
- One or more applications wants to query data in SQL Server 2005 table through interface through dll that I will write.
- The table needs to be cached for performance reasons
- Cache should be shared between all the applications
- The cache lookup should not cross process boundry if possible
- All of this is on the same machine
One or more applications --> DLL provided by me --> shared SQL Server 2005 Cached Table
I have following questions:
- I don't seem to find the correct way to cache a table. DBCC PINTABLE is obsolete. I keep on getting *** ASP.NET cache dependency *** articles which is confusing me because I am writing this all in C#. Articles I read:
http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency.aspx
http://msdn.microsoft.com/en-us/library/6hbbsfk6.aspx
- I am not certain how can I avoid crossing process boundry (for performance reasons) even if I do find a way to cache table since cache will be maintained by SQL server. I could keep manually cached table in DLL in windows shared memory but I want to keep that as last resort if SQL server does not offer any help in this scenario.
Any thoughts/comments welcome.
Thanks in advance,
-Neel.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

AFAIK there's no way to force a table to be hold in cache when using SS2K5.

SQL Server manages the cache internally and will hold data in cache if possible and required.

There are a lot of other options to tune a query than forcing the table to be hold in cache. Some options are proper indexing of tables, efficient queries (SET based instead of loops) and some more.

It would be nice to know some rough numbers like table size, your concept on how to query the tables (e.g. sample query and description of general concept).

Side note: How would you deal with the scenario where your table size would exceed the cache available for SQL Server?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I think you might find this thread to be an interesting read.

It is suggested to use Global temp tables there. However, temp tables are not stored entirely in memory.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 3

@jason:

Wouldn't SQL Server decide on his own whether a temp table is required or not - even without defining global temp tables?

I, personally, try to avoid ##tables, since it cannot really (easily) be predicted over time when the global temp table will be "visible" or not.

as per BOL:

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

If apps permanently connect and disconnect to SQL Server chances are high to frequently drop and create the ##table. Another disadvantage is the existence check (including creation, if needed) required within each session that will use that table.

I'd rather leave it to SQL Server and focus on other ways to improve performance (as stated in my prev. post).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Lutz, It would definitely be better to let SQL determine what data got cached. That was one of the points made in the article I referenced. Another point was that the use of Pintable was used as a Crux and caused some very bad performance. Thus, trying to do the same sort of thing by other means than the DB Engine could also lead to poor performance.

The Global temp table is not something that I would do - and it certainly does not put the entire table into 100% cache. Just laying that option out there. I was hoping that seeing both sides of the argument for trying to do that would lead to a tuning of the query in the end.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

krayknot SSCertifiable Points: 7998 More actions · Answer 5

you want data caching at database level or at the application level????

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

there is also notification services which allows your application to cache the data locally and only refresh it when it receives a notification message from sqlserver.

I'm not a .net specialist, but I believe .net has that kind of construct itself.

( which would be one of the reasons sqlserver notification services is deprecated )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me