1

Topic: How correctly to organize caching of report SSRS?

Initial data:
There is a report which long enough collects the data, but  the dial-up is very small <2000 lines.
Report urgency - days, i.e. within days it will show all the same data.
Between users a difference only that they look the data with different filters (depending on access level) - one well everything, another only a part, the third other part.
Number of users nearby 100.
The task:
To lower loading on the server, to raise high-speed performance.
There can be it a crutch, but want to make caching which works so:
Any first user who has launched the report in a new bottom, fills a cache. I.e. the report does it automatically.
In  it is written: if in table 1 there is data for the period from Data1 on Data2 + other filters to take the data from table 1, if the data is not present to launch a row of requests:
To clear table 1 of the old data (to delete everything that is less than Data2)
To fulfill request which collects the resultant data with the maximum access and without filters (i.e. not only that to it is authorized to look and irrespective of the filters exposed at it) and places them in table 1
Fulfills request to table 1 taking into account access level and filters of the user. This data will be already displayed
In  something of type

if not exists (select null from t1 where date1 = date1 and date2 = date2 and p1 = @p1 and p2 = @p2 and...)
begin
delete from t1 where date2<@date2
insert into t1
select
@date1 date1
,@date2 date2
,@p1 p1
,@p2 p2
,...
from
...
end
else
select... from t1

From request it is visible that as soon as the user requests the data with new parameters which are not present in a cache the cache is added, but all delayed given data is deleted irrespective of parameters. @date2 always = current date - 1, the user cannot change it, as well as @date1
Question following:
How most easier to provide integrity of the data that if two simultaneously launched the report for morning of next day only one of them captured a cache and updated it? It would not be desirable to fence any special table of locks. For certain the decision idle time, but something is visible I did not sleep...
It is possible to make, of course, , which time will fill a day a cache, but this variant to me does not approach for the several reasons one of which - the report use not always

2

Re: How correctly to organize caching of report SSRS?

TJ001, the variant with  is quite good... And so sp_getapplock/sp_releaseapplock

3

Re: How correctly to organize caching of report SSRS?

Something at you all is somehow difficult.
At us the exactly same requirements:
Report urgency - days, i.e. within days it will show all the same data.
The report gathers 1 time in procedure, procedure puts result in the table.
This procedure, as well as cloud of others, is a part night .
fills necessary tables in the circuit staging.
Each table has a column datarif, it is that date on which the report is collected.
The last instruction in  this switching of all  tables in dbo;
In a single the user

4

Re: How correctly to organize caching of report SSRS?

TJ001;
So fill "cube" in a time off under the schedule.

5

Re: How correctly to organize caching of report SSRS?

buser wrote:

sp_getapplock/sp_releaseapplock

Yes! That's exactly what I wanted!
Thanks! smile