Query Performance from Reporting Services

Hi,
Has anyone had any problems with running queries from Reporting Services
that perform 300x slower than from other sources? Any Ideas of what may
cause this?
I have a query that uses an Indexed View and runs in 8 seconds if I run it
from QA, but RS takes 30 minutes. All indications from running Profiler
shows that RS is indeed using the indexed view.
Any thoughts of what to check would be appreciated.
ThanksCan you use a stored procedure? There have been instances where the query
used in Reporting Services did not use the same query plan as would be used
if the query was within query analyzer. One query plan used a particular
index and the other did not. Hence the differences in performance. If you
use a stored procedure you would be guaranteed that regardless of where
invoked (query analyzer or RS) they would use the same query plan.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> Hi,
> Has anyone had any problems with running queries from Reporting Services
> that perform 300x slower than from other sources? Any Ideas of what may
> cause this?
> I have a query that uses an Indexed View and runs in 8 seconds if I run it
> from QA, but RS takes 30 minutes. All indications from running Profiler
> shows that RS is indeed using the indexed view.
> Any thoughts of what to check would be appreciated.
> Thanks
>|||Yes, I tried using a stored Proc and that didnt improve things.
Interestingly with a stored proc it seems to not even bother caching the
results, so that if you repreview it takes another 30 minutes to see the 8sec
query. I did notice it was passing the stored proc to another stored proc
(sp_procedure_params_rowset) to get the rowset values, but I didn't see any
parameters passed. I'm not sure if this could be causing the huge delay or
not.
But then again, even when I was using a text query straight from RS, I gave
it a direct index hint and if I previewed the data in the Data tab it ran in
8 secs, but if I previewed the report, it took 30 minutes.
Thanks
"Bruce L-C [MVP]" wrote:
> Can you use a stored procedure? There have been instances where the query
> used in Reporting Services did not use the same query plan as would be used
> if the query was within query analyzer. One query plan used a particular
> index and the other did not. Hence the differences in performance. If you
> use a stored procedure you would be guaranteed that regardless of where
> invoked (query analyzer or RS) they would use the same query plan.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> > Hi,
> >
> > Has anyone had any problems with running queries from Reporting Services
> > that perform 300x slower than from other sources? Any Ideas of what may
> > cause this?
> >
> > I have a query that uses an Indexed View and runs in 8 seconds if I run it
> > from QA, but RS takes 30 minutes. All indications from running Profiler
> > shows that RS is indeed using the indexed view.
> >
> > Any thoughts of what to check would be appreciated.
> >
> > Thanks
> >
>
>|||How many rows are being returned? Are you using any filters?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
> Yes, I tried using a stored Proc and that didnt improve things.
> Interestingly with a stored proc it seems to not even bother caching the
> results, so that if you repreview it takes another 30 minutes to see the
> 8sec
> query. I did notice it was passing the stored proc to another stored proc
> (sp_procedure_params_rowset) to get the rowset values, but I didn't see
> any
> parameters passed. I'm not sure if this could be causing the huge delay
> or
> not.
> But then again, even when I was using a text query straight from RS, I
> gave
> it a direct index hint and if I previewed the data in the Data tab it ran
> in
> 8 secs, but if I previewed the report, it took 30 minutes.
> Thanks
>
> "Bruce L-C [MVP]" wrote:
>> Can you use a stored procedure? There have been instances where the query
>> used in Reporting Services did not use the same query plan as would be
>> used
>> if the query was within query analyzer. One query plan used a particular
>> index and the other did not. Hence the differences in performance. If you
>> use a stored procedure you would be guaranteed that regardless of where
>> invoked (query analyzer or RS) they would use the same query plan.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
>> > Hi,
>> >
>> > Has anyone had any problems with running queries from Reporting
>> > Services
>> > that perform 300x slower than from other sources? Any Ideas of what
>> > may
>> > cause this?
>> >
>> > I have a query that uses an Indexed View and runs in 8 seconds if I run
>> > it
>> > from QA, but RS takes 30 minutes. All indications from running
>> > Profiler
>> > shows that RS is indeed using the indexed view.
>> >
>> > Any thoughts of what to check would be appreciated.
>> >
>> > Thanks
>> >
>>|||The row count is 18, but it is summarizing about 100 million records using an
indexed view. I am using report parameters to pass to the query to filter
the results from within the Query statement. Basically a date range
selection, along with a history type. I did try hard coding some values
within the query statement to see if it was faster, but the results were the
same.
"Bruce L-C [MVP]" wrote:
> How many rows are being returned? Are you using any filters?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
> > Yes, I tried using a stored Proc and that didnt improve things.
> > Interestingly with a stored proc it seems to not even bother caching the
> > results, so that if you repreview it takes another 30 minutes to see the
> > 8sec
> > query. I did notice it was passing the stored proc to another stored proc
> > (sp_procedure_params_rowset) to get the rowset values, but I didn't see
> > any
> > parameters passed. I'm not sure if this could be causing the huge delay
> > or
> > not.
> >
> > But then again, even when I was using a text query straight from RS, I
> > gave
> > it a direct index hint and if I previewed the data in the Data tab it ran
> > in
> > 8 secs, but if I previewed the report, it took 30 minutes.
> >
> > Thanks
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Can you use a stored procedure? There have been instances where the query
> >> used in Reporting Services did not use the same query plan as would be
> >> used
> >> if the query was within query analyzer. One query plan used a particular
> >> index and the other did not. Hence the differences in performance. If you
> >> use a stored procedure you would be guaranteed that regardless of where
> >> invoked (query analyzer or RS) they would use the same query plan.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> >> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> >> > Hi,
> >> >
> >> > Has anyone had any problems with running queries from Reporting
> >> > Services
> >> > that perform 300x slower than from other sources? Any Ideas of what
> >> > may
> >> > cause this?
> >> >
> >> > I have a query that uses an Indexed View and runs in 8 seconds if I run
> >> > it
> >> > from QA, but RS takes 30 minutes. All indications from running
> >> > Profiler
> >> > shows that RS is indeed using the indexed view.
> >> >
> >> > Any thoughts of what to check would be appreciated.
> >> >
> >> > Thanks
> >> >
> >>
> >>
> >>
>
>|||Very odd. How many fields are being returned? Usually with RS being slow it
is because the resultset is large. This is not the case for you. In this
case I would expect the performance to be the same as with the data tab ( or
at most a second or two longer).
This is a long shot but just in case you are seeing an issue with the
development environment, try deploying it and seeing how long it takes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:D8B81128-ABF4-4C50-A639-E6C86CD16BBD@.microsoft.com...
> The row count is 18, but it is summarizing about 100 million records using
> an
> indexed view. I am using report parameters to pass to the query to filter
> the results from within the Query statement. Basically a date range
> selection, along with a history type. I did try hard coding some values
> within the query statement to see if it was faster, but the results were
> the
> same.
>
> "Bruce L-C [MVP]" wrote:
>> How many rows are being returned? Are you using any filters?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
>> > Yes, I tried using a stored Proc and that didnt improve things.
>> > Interestingly with a stored proc it seems to not even bother caching
>> > the
>> > results, so that if you repreview it takes another 30 minutes to see
>> > the
>> > 8sec
>> > query. I did notice it was passing the stored proc to another stored
>> > proc
>> > (sp_procedure_params_rowset) to get the rowset values, but I didn't see
>> > any
>> > parameters passed. I'm not sure if this could be causing the huge
>> > delay
>> > or
>> > not.
>> >
>> > But then again, even when I was using a text query straight from RS, I
>> > gave
>> > it a direct index hint and if I previewed the data in the Data tab it
>> > ran
>> > in
>> > 8 secs, but if I previewed the report, it took 30 minutes.
>> >
>> > Thanks
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Can you use a stored procedure? There have been instances where the
>> >> query
>> >> used in Reporting Services did not use the same query plan as would be
>> >> used
>> >> if the query was within query analyzer. One query plan used a
>> >> particular
>> >> index and the other did not. Hence the differences in performance. If
>> >> you
>> >> use a stored procedure you would be guaranteed that regardless of
>> >> where
>> >> invoked (query analyzer or RS) they would use the same query plan.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> >> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > Has anyone had any problems with running queries from Reporting
>> >> > Services
>> >> > that perform 300x slower than from other sources? Any Ideas of what
>> >> > may
>> >> > cause this?
>> >> >
>> >> > I have a query that uses an Indexed View and runs in 8 seconds if I
>> >> > run
>> >> > it
>> >> > from QA, but RS takes 30 minutes. All indications from running
>> >> > Profiler
>> >> > shows that RS is indeed using the indexed view.
>> >> >
>> >> > Any thoughts of what to check would be appreciated.
>> >> >
>> >> > Thanks
>> >> >
>> >>
>> >>
>> >>
>>