Query/Report processing time

I am having a frustrating problem with a report. It is pulling data from a
stored procedure, and I have tested it through query analyzer and i get
performance times of 4-5 seconds. However, when I select this same sproc
through Reporting Services during report building time, and hit the "!" to
pull the data, punch in the exact same parameters, it is taking upward of
30-40 seconds to pull the same data. What is the deal? It makes no rational
sense.How much data are we talking about? In QA, enable Client Statistics and what are the # of rows
affected and # of bytes sent?
Also, which query designer are you using? Does it have 4 panes and show tables or is it the 2-pane
textbox/grid designer? QA and the designers all use different underlying mechanisms for querying
the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses the SQL Managed provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> Well, that is just it. It's NOT during the actual report processing that I
> am having this problem, it is when I am loading the dataset under the "Data"
> tab. When I hit the "!" and enter in my params, the thing is taking forever.
> But when I run the same sproc in Query Analyzer, it is taking 4-5 seconds.
> So, this has nothing to do with actual report processing, it is something it
> is doing when it is grabbing the data that is f'ed up (if you know what I
> mean). Hence, when I deploy this thing (although the report is really
> simple) since it is taking it forever to grab data that should take a few
> seconds, it is taking like 45 seconds to render this report. It is a real
> pain in the ass.
> "Andrew Byrne" wrote:
> > My guess is that in design time you are running the query through the .NET
> > IDE so I presume there is a bigger memory footprint required.
> >
> > That lag experienced is probably due to RS not caching the report between
> > runs, something which was improved in SP1.
> >
> > Again, I would say .NET is involved if you experience lag the first time (in
> > a session) you run a report - the CLR needs to load assemblies, everything is
> > running under IIS etc.
> >
> > Don't get me wrong - I agree that RS could be a lot faster - just merely
> > trying to come up with plausible reasons for the reduction in speed !
> >
> > "Dan Fell" wrote:
> >
> > >
> > > I too suffer from similar problems. I think the problem seems to lie in the
> > > rendering part of the reporting. The queries themselves take relatively
> > > little time but the report can take ages to appear on screen.
> > >
> > > Not only that but it can be quite erratic. There a lag when the Report
> > > Manager's pages and reports are first rendered and then they seem to render
> > > much quicker afterwards. Is this normal?
> > >
> > > DF
> > >
> > > "isamu" wrote:
> > >
> > > > I am having a frustrating problem with a report. It is pulling data from a
> > > > stored procedure, and I have tested it through query analyzer and i get
> > > > performance times of 4-5 seconds. However, when I select this same sproc
> > > > through Reporting Services during report building time, and hit the "!" to
> > > > pull the data, punch in the exact same parameters, it is taking upward of
> > > > 30-40 seconds to pull the same data. What is the deal? It makes no rational
> > > > sense.|||I have experienced the same thing, even though I am designing/running
directly on the server hosting RS.
It seems to be intermittent. I have had a report take 2-3 minutes
everytime. This could go on for hours, even after a reboot. Then, out of
nowhere it takes 3 seconds. FYI: The same query always took about 1
second through query analyzer. This seems to be an RS rendering issue...
Dan
___________________________
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:36026EB9-5AD5-4355-9C15-B7573A5DAB56@.microsoft.com...
> Argh ... well, this is quite odd, after working on this for about 2 hours
> yesterday and even running it a few times today, it appears to have fixed
> itself ... I am not sure, but perhaps this was some sort of Network issue
at
> my client's site. Thanks a lot for your time, and sorry to trouble you.
I
> guess if this issue ever comes up again, have the network guys check their
> latencies.
> "Donovan R. Smith [MSFT]" wrote:
> > How much data are we talking about? In QA, enable Client Statistics and
what are the # of rows
> > affected and # of bytes sent?
> >
> > Also, which query designer are you using? Does it have 4 panes and show
tables or is it the 2-pane
> > textbox/grid designer? QA and the designers all use different
underlying mechanisms for querying
> > the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses
the SQL Managed provider.
> >
> > --
> > Thanks.
> >
> > Donovan R. Smith
> > Software Test Lead
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "isamu" <isamu@.discussions.microsoft.com> wrote in message
> > news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> > > Well, that is just it. It's NOT during the actual report processing
that I
> > > am having this problem, it is when I am loading the dataset under the
"Data"
> > > tab. When I hit the "!" and enter in my params, the thing is taking
forever.
> > > But when I run the same sproc in Query Analyzer, it is taking 4-5
seconds.
> > > So, this has nothing to do with actual report processing, it is
something it
> > > is doing when it is grabbing the data that is f'ed up (if you know
what I
> > > mean). Hence, when I deploy this thing (although the report is really
> > > simple) since it is taking it forever to grab data that should take a
few
> > > seconds, it is taking like 45 seconds to render this report. It is a
real
> > > pain in the ass.
> > >
> > > "Andrew Byrne" wrote:
> > >
> > > > My guess is that in design time you are running the query through
the .NET
> > > > IDE so I presume there is a bigger memory footprint required.
> > > >
> > > > That lag experienced is probably due to RS not caching the report
between
> > > > runs, something which was improved in SP1.
> > > >
> > > > Again, I would say .NET is involved if you experience lag the first
time (in
> > > > a session) you run a report - the CLR needs to load assemblies,
everything is
> > > > running under IIS etc.
> > > >
> > > > Don't get me wrong - I agree that RS could be a lot faster - just
merely
> > > > trying to come up with plausible reasons for the reduction in speed
!
> > > >
> > > > "Dan Fell" wrote:
> > > >
> > > > >
> > > > > I too suffer from similar problems. I think the problem seems to
lie in the
> > > > > rendering part of the reporting. The queries themselves take
relatively
> > > > > little time but the report can take ages to appear on screen.
> > > > >
> > > > > Not only that but it can be quite erratic. There a lag when the
Report
> > > > > Manager's pages and reports are first rendered and then they seem
to render
> > > > > much quicker afterwards. Is this normal?
> > > > >
> > > > > DF
> > > > >
> > > > > "isamu" wrote:
> > > > >
> > > > > > I am having a frustrating problem with a report. It is pulling
data from a
> > > > > > stored procedure, and I have tested it through query analyzer
and i get
> > > > > > performance times of 4-5 seconds. However, when I select this
same sproc
> > > > > > through Reporting Services during report building time, and hit
the "!" to
> > > > > > pull the data, punch in the exact same parameters, it is taking
upward of
> > > > > > 30-40 seconds to pull the same data. What is the deal? It
makes no rational
> > > > > > sense.
> >
> >
> >