data caching on SQL Server?

Suppose 40 winform clients of SQL Server(SS) 2005 request the same data (select * from sometable).

Is there any proxy service in SS or all clients will be queueing/competing for the same answer?

Hi,

I think its the dot net that do the caching not Sql server.

http://www.codeproject.com/csharp/webservicecache.asp

regards,

joey

|||

Different winform clients are on different machines with different .NET frameworks generally without any knowledge about each other connecting to one central MS SQL Server db?

So, each winform client may cache data to avoid repeting the request to SQL Server... according to your link. Or it is done by ASP.NET/IIS (for webforms)

But the question is about caching by SQL Server, to avoid the server to service the same results to different WINFORM clients.

Since SQL Server 2005 is integrated with .NET, it is quite logical to expect such proxy service from SQL Server or .NET framework whith which MSS2005 is integrated with, isn't it?

Is it available by MS SQL Server 2005? how it is called? or I should create it?

|||

hi,

you might want to create a 'view' or an 'indexed view'

if the query is not parameterized or a stored procedure if it is parameterized

if you're into a very fast performance i'll recommend index view though you

will be penalized for disk space.

if its is a stored procedure execution is cached not the the result .

the link i gave you was caching the data by having winforms to consume

a webservice which stores the cached dataset

regards,

joey

|||

joeydj , Thanks for your guidance

Having rephrased my doubt more concisely:
if ASP.NET server data webcontrols permit declarative specification for caching data (on server side), I do not see why winforms controls have not the ability to do the same, say, through one of MS SQL Server2005 configurable and built.in (web)sevice...

|||

In my question I really wanted to ask whether the MSSSdb engine needs to compute/execute data according query again (even according to compiled/cached plan) and whether it repeats I/O operations in order to to get the same data structures.

This is rather important to know in order to make decisions on where to customly cache (on server or client), when, for how long and how much data.

Reporting Services has cached reports.
Really(and obviously) there are data buffers in memory. Also one can explicitly place data structures into memory [1]

Code Snippet

--Use the pubs database USE pubs DECLARE @.dbid INTEGER SET @.dbid = DB_ID('pubs') --Determine id number for the dbo.authorstable DECLARE @.obid INTEGER SET @.obid = OBJECT_ID('dbo.authors') --Pin the dbo.authors table to memory DBCC PINTABLE (@.dbid,@.obid) GO

Though details how to control memory management are not very explicit in docs

[1]
Introduction into Caching in SQL Server 2000

http://www.extremeexperts.com/SQL/Articles/SQLCacheObjects.aspx

|||

Well, after all there is direct match to my question.

ADO.NET 2.0 (SQL Server 2005) supports

1)
server-side cursors

ExecuteResultSet of SqlResultSet

2)

Paging

ExecutePageReader(CommandBehavior.Default, nStartRow, nPageSize);

3)

Asynchronous execution of commands

4)

MARS - Multiple Result Sets