Reporting Services report

Just wondering if anyone would know why a report would work fine for around
a week, running within 6-7 seconds, then suddenly take 25 minutes to run?
Doing an update stats appears to cure the problem. The source database is
loaded several times each day. Running the SQL statement directly in SQL
Management studio, even before the update stats is done, retrieves the data
in seconds, so it wouldn't appear to be a SQL problem. I have seen articles
hinting at reporting services having its own statistics collection? but only
where cubes are involved, there is no cube in the data this report needs. We
are running SQL server 2005 service pack 2.
thanks for any help in advance
Mikey :o)Is this against a stored procedure. RS does not have its own statistics
collection against SQL Server (I have no experience with cubes but I don't
it there as well). There is an issue for some stored procedures (I haven't
seen this but it might occur more in your type of environment). If a stored
procedure you might try using the Recompile option on the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mikey :0)" <Mikey0@.discussions.microsoft.com> wrote in message
news:F8215493-7817-47F5-9DD8-6E3C1026A692@.microsoft.com...
> Just wondering if anyone would know why a report would work fine for
> around
> a week, running within 6-7 seconds, then suddenly take 25 minutes to run?
> Doing an update stats appears to cure the problem. The source database is
> loaded several times each day. Running the SQL statement directly in SQL
> Management studio, even before the update stats is done, retrieves the
> data
> in seconds, so it wouldn't appear to be a SQL problem. I have seen
> articles
> hinting at reporting services having its own statistics collection? but
> only
> where cubes are involved, there is no cube in the data this report needs.
> We
> are running SQL server 2005 service pack 2.
> thanks for any help in advance
> Mikey :o)|||Bruce, thanks for responding, the report currently doesn't use a stored
procedure, it was one of the options I was considering as a work-around as it
appears the it might be something that Reporting services is doing with the
SQL command? I was thinking that by taking the SQL out of the report and
instead calling a Stored procedure, it would not be able to change the sql.
This is backed up by the fact that when this report runs slowly, the SQL can
be executed in query analyser with no problems.
"Bruce L-C [MVP]" wrote:
> Is this against a stored procedure. RS does not have its own statistics
> collection against SQL Server (I have no experience with cubes but I don't
> it there as well). There is an issue for some stored procedures (I haven't
> seen this but it might occur more in your type of environment). If a stored
> procedure you might try using the Recompile option on the stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mikey :0)" <Mikey0@.discussions.microsoft.com> wrote in message
> news:F8215493-7817-47F5-9DD8-6E3C1026A692@.microsoft.com...
> > Just wondering if anyone would know why a report would work fine for
> > around
> > a week, running within 6-7 seconds, then suddenly take 25 minutes to run?
> > Doing an update stats appears to cure the problem. The source database is
> > loaded several times each day. Running the SQL statement directly in SQL
> > Management studio, even before the update stats is done, retrieves the
> > data
> > in seconds, so it wouldn't appear to be a SQL problem. I have seen
> > articles
> > hinting at reporting services having its own statistics collection? but
> > only
> > where cubes are involved, there is no cube in the data this report needs.
> > We
> > are running SQL server 2005 service pack 2.
> >
> > thanks for any help in advance
> >
> > Mikey :o)
>
>