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.
[quoted text, click to view] "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)
>
>
