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

Data Caching in reports?

I wrote an invoice report that gets its data from an Oracle database. This is working fine except when users change something on the server (like adding a new charge to the invoice) then when they re-run the SSRS report the new line doesn't show up. They wait 15 minutes and re-run the SSRS report and then it appears.

It is like the SSRS report is caching the old data of the report.

Points to note though...

To execute the report I am using the SSRS 2005 webservice. I understand that there is 2 cachings, 1 server and 1 IIS side. I have not cached the server and don't know how to clear the IIS side. I see there is a rs:ClearSession=true parameter that you can pass in but by using the SSRS 2005 webservice this is not an option (I don't see how you can pass query strings in to the call).

Does SSRS cache the Oracle DataSet at all?

Thanks heaps for any help,

Craig

I'm having issues with caching too, but it may be a different problem than yours. Check the settings on your report server to make sure it is set to not caching.

Under the Properties tab, execution left menu there is a "Render this report with the most recent Data" radio button. Make sure the "Do not cache temporary copies of this report" radio button is checked.

Daily Caching

I have set up a data-drive subscription to do a null report on a report
that takes 3 minutes to run.
I have it set up to run at 3 am and it works fine.
My question is when a user runs it at 6 am, does it use what I cached
plus the new data, or does it re-cache the whole thing?
This report is intense and I really do not want to run it during the day
if I don't have to.
If it constantly re-caches the report, would creating a snapshot on a
schedule be the better option?
Thanks.It depends on the execution settings of the report. If the report is set to
cache the execution and the DD subscription runs with all parameter
combinations, and the cache expiration is sufficiently long enough then
users will get cached renderings, they will not get any recent data.
Using a snapshot is another possibility, the only problem is that any
parameters that affect the query will be locked and users will not be able
to change them.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:uXbnj4a7EHA.2124@.TK2MSFTNGP15.phx.gbl...
>I have set up a data-drive subscription to do a null report on a report
>that takes 3 minutes to run.
> I have it set up to run at 3 am and it works fine.
> My question is when a user runs it at 6 am, does it use what I cached plus
> the new data, or does it re-cache the whole thing?
> This report is intense and I really do not want to run it during the day
> if I don't have to.
> If it constantly re-caches the report, would creating a snapshot on a
> schedule be the better option?
> Thanks.
>