|
|
You're in the
sql server reporting services
group:Report performance and cache
sql server reporting services:
has mutiple tables, charts and subreports and against a large database. It has two parameters: Year and Month. It takes about 2 minutes for a user to run the report online. So the performance is a critical issue here. On Report Manager, I set the Execution Property to "Cache". In this way, only the first user takes two minutes to run the report. Other users who run the same report for the same month will get the cached report very fast. This is exactly what I wanted - to cache the previous months reports once they are generated. But here lies the potential problem. Users are supposed to run the report on the first days of each month, for example, running the May-2006 report on 6/1/2006. But if someone runs the report for the current month, for example, June-2006, on 6/15/2006, then the report is generated and cached (data up to 6/15/2006). On 7/1/2006, when users run the report for June, they will get the cached report which only contains data to 6/15/2006, not the full month. Does anyone know how to solve the problem? Or have any other solutions? Any help is much appreciated. Thanks, Keith
couldnt you set up a subscription to request the report on the date and
time you want it generated? something like this ... http://msdn2.microsoft.com/en-us/library/ms155876.aspx db ---------------------------- http://www.osix.net the geek programming challenges. [quoted text, click to view] Keith wrote:
> I've created a report (Regional Sales Report) using RS2000. The report > has mutiple tables, charts and subreports and against a large database. > It has two parameters: Year and Month. It takes about 2 minutes for a > user to run the report online. > > So the performance is a critical issue here. On Report Manager, I set > the Execution Property to "Cache". In this way, only the first user > takes two minutes to run the report. Other users who run the same > report for the same month will get the cached report very fast. This is > exactly what I wanted - to cache the previous months reports once they > are generated. > > But here lies the potential problem. Users are supposed to run the > report on the first days of each month, for example, running the > May-2006 report on 6/1/2006. But if someone runs the report for the > current month, for example, June-2006, on 6/15/2006, then the report is > generated and cached (data up to 6/15/2006). On 7/1/2006, when users > run the report for June, they will get the cached report which only > contains data to 6/15/2006, not the full month. > > Does anyone know how to solve the problem? Or have any other solutions? > > Any help is much appreciated. > > Thanks, > Keith
Keith,
I think bb has the best idea with setting up subscriptions, thereby controlling when the report gets run. Other possible solutions are to use a snapshot instead of a cache, making the parameter value static or rewriting the proc so that while still dynamic, the parameter values are set programmatically or set your cache to expire every 24 hours so the data is current even though the first user every day gets stuck with the two minute wait. [quoted text, click to view] bb wrote:
> couldnt you set up a subscription to request the report on the date and > time you want it generated? > > > something like this ... > > http://msdn2.microsoft.com/en-us/library/ms155876.aspx > > > db > > ---------------------------- > http://www.osix.net > the geek programming challenges. > > > Keith wrote: > > > I've created a report (Regional Sales Report) using RS2000. The report > > has mutiple tables, charts and subreports and against a large database. > > It has two parameters: Year and Month. It takes about 2 minutes for a > > user to run the report online. > > > > So the performance is a critical issue here. On Report Manager, I set > > the Execution Property to "Cache". In this way, only the first user > > takes two minutes to run the report. Other users who run the same > > report for the same month will get the cached report very fast. This is > > exactly what I wanted - to cache the previous months reports once they > > are generated. > > > > But here lies the potential problem. Users are supposed to run the > > report on the first days of each month, for example, running the > > May-2006 report on 6/1/2006. But if someone runs the report for the > > current month, for example, June-2006, on 6/15/2006, then the report is > > generated and cached (data up to 6/15/2006). On 7/1/2006, when users > > run the report for June, they will get the cached report which only > > contains data to 6/15/2006, not the full month. > > > > Does anyone know how to solve the problem? Or have any other solutions? > > > > Any help is much appreciated. > > > > Thanks, > > Keith
Thanks for the replies.
I have another related question. If I still want to use cache (which will be expired every 12 months), is there any way to build any logic within report or on report manager to control when report can be run and cached? Basically, we don't want users to run the report for the current month. For example, if a user chooses June as a parameter value on 6/12/06, he would get a message like "sorry, the data you choose is not yet available...." The report is not executed and thus not cached. Is it possible? Thanks, Keith [quoted text, click to view] "toolman" wrote:
> Keith, > I think bb has the best idea with setting up subscriptions, thereby > controlling when the report gets run. > Other possible solutions are to > use a snapshot instead of a cache, making the parameter value static > or rewriting the proc so that while still dynamic, the parameter values > are set programmatically > or set your cache to expire every 24 hours so the data is current even > though the first user every day gets stuck with the two minute wait. > > > bb wrote: > > couldnt you set up a subscription to request the report on the date and > > time you want it generated? > > > > > > something like this ... > > > > http://msdn2.microsoft.com/en-us/library/ms155876.aspx > > > > > > db > > > > ---------------------------- > > http://www.osix.net > > the geek programming challenges. > > > > > > Keith wrote: > > > > > I've created a report (Regional Sales Report) using RS2000. The report > > > has mutiple tables, charts and subreports and against a large database. > > > It has two parameters: Year and Month. It takes about 2 minutes for a > > > user to run the report online. > > > > > > So the performance is a critical issue here. On Report Manager, I set > > > the Execution Property to "Cache". In this way, only the first user > > > takes two minutes to run the report. Other users who run the same > > > report for the same month will get the cached report very fast. This is > > > exactly what I wanted - to cache the previous months reports once they > > > are generated. > > > > > > But here lies the potential problem. Users are supposed to run the > > > report on the first days of each month, for example, running the > > > May-2006 report on 6/1/2006. But if someone runs the report for the > > > current month, for example, June-2006, on 6/15/2006, then the report is > > > generated and cached (data up to 6/15/2006). On 7/1/2006, when users > > > run the report for June, they will get the cached report which only > > > contains data to 6/15/2006, not the full month. > > > > > > Does anyone know how to solve the problem? Or have any other solutions? > > > > > > Any help is much appreciated. > > > > > > Thanks, > > > Keith >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |