Topic: How correctly to organize caching of report SSRS?
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.
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
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