Query very slow when executed using Reporting Services

Hello,
I have a dataset that uses a stored procedure as the data source. In
the query analyzer, it takes just over 2 seconds to execute. However,
in the execute query mode in the Visual Studio reports designer, the
query takes several minutes.
The report, when run from the server, also takes a very long time to
complete.
There are only 4 parameters, two varchar(2) and two datetime fields.
The particular query I am running returns 12 rows. There are no filters
applied to the report, not that would affect the query in the VS
reports designer.
Does anybody have any ideas on how to improve the performance?
Thank you,
Brian TakitaFirst the good news. You really did a good job with the information you
provided here. The bad news, this is very strange. The most common reasons
for slowness is lots of data returned (but you only have 12 rows), second is
having a filter. The issue with a filter isn't a filter per se but the fact
that all the data is brought over prior to the filter being applied. If you
execute the stored procedure in query analyzer and it returns 12 rows there
then something else is going on.
Do you have a large amount of fields being returned?
How is your data source credential setup? I am wondering if something is
going on with regards to your credentials that is causing the problem.
Do you have another report that uses the same data source? If so, how is its
performance. If not, then try some simple report that uses the same data
source so we can eliminate that as a possible problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112301864.710648.22590@.z14g2000cwz.googlegroups.com...
> Hello,
> I have a dataset that uses a stored procedure as the data source. In
> the query analyzer, it takes just over 2 seconds to execute. However,
> in the execute query mode in the Visual Studio reports designer, the
> query takes several minutes.
> The report, when run from the server, also takes a very long time to
> complete.
> There are only 4 parameters, two varchar(2) and two datetime fields.
> The particular query I am running returns 12 rows. There are no filters
> applied to the report, not that would affect the query in the VS
> reports designer.
> Does anybody have any ideas on how to improve the performance?
> Thank you,
> Brian Takita
>|||Thank you for the prompt reply.
>Do you have a large amount of fields being returned?
Yes. There are 40 fields returned.
>How is your data source credential setup? I am wondering if something
is
>going on with regards to your credentials that is causing the problem.
I'm using integrated security.
>Do you have another report that uses the same data source? If so, how
is its
>performance. If not, then try some simple report that uses the same
data
>source so we can eliminate that as a possible problem.
I tried creating a report using the stored procedure and it is also
slow.|||You can try to pin point what's taking so long. Look in the ReportServer
database; the ExecutionLog
table. It shows what report ran, when, the amount of time for data
retrieval, processing time, and rendering time.
It also shows what render format, render status (fail or success), query
row count and byte size of
the rendered file.
Adrian M.
MCP
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
>>Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
>>How is your data source credential setup? I am wondering if something
> is
>>going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
>>Do you have another report that uses the same data source? If so, how
> is its
>>performance. If not, then try some simple report that uses the same
> data
>>source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Here are the result fields:
TimeDataRetrieval: 255840
TimeProcessing: 45
TimeRendering: 34
Status: rsSuccess
ByteCount: 54070
RowCount: 0
What is strange is when the report is executed on the server, the
RowCount is 0, but when executed from the VS report designer, I get the
correct number of rows, but it is also slow.|||When I try pulling a very similar report with the same parameters as a
pdf file I get the following results:
TimeDataRetrieval: 228716
TimeProcessing: 224082
TimeRendering: 45
Status: rsSuccess
ByteCount: 1872
RountCount: 0|||Is it possible to you to do a test where the stored procedure is returning
fewer fields (for the test just have it do 5 fields for instance) and then
create a report off of that. Let's see if the problem is the number of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
> >Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
> >How is your data source credential setup? I am wondering if something
> is
> >going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
> >Do you have another report that uses the same data source? If so, how
> is its
> >performance. If not, then try some simple report that uses the same
> data
> >source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Thank you for your help Bruce. Your heuristics to solve the problem
were invaluable.
I solved the issue but did not find the ultimate source of the problem.
To solve the issue I optimized the stored procedure. I removed an
insert statement that scanned a fairly large table.
My stored procedure is fairly complicated as it uses several table
variables.
It strange that the sp would work fine using the query analyzer but not
using Sql Server RS.
I also tried recreating the sp, making all of the functions called in
the sp inline. There was a test case in the sp that took a long time to
complete.
Maybe the sp caused the server to run into memory constraints?
Thanks again,
Brian Takita|||Problem solved. The ExecutionLog table is something I'll need remember.
Thank you Adrian.|||You might want to turn on SQL profiler and look at the statements that are
generated by Query Analyzer and Reporting Services (through sqlclient).
Sometimes, the acutal execution syntax will be slightly different even when
the results are the same. This is primarily in the way that sqlclient
prepares parameters and executes the stored procedure. This difference may
cause SQL Server not to use a compiled query plan.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112313413.140441.295110@.f14g2000cwb.googlegroups.com...
> Thank you for your help Bruce. Your heuristics to solve the problem
> were invaluable.
> I solved the issue but did not find the ultimate source of the problem.
> To solve the issue I optimized the stored procedure. I removed an
> insert statement that scanned a fairly large table.
> My stored procedure is fairly complicated as it uses several table
> variables.
> It strange that the sp would work fine using the query analyzer but not
> using Sql Server RS.
> I also tried recreating the sp, making all of the functions called in
> the sp inline. There was a test case in the sp that took a long time to
> complete.
> Maybe the sp caused the server to run into memory constraints?
> Thanks again,
> Brian Takita
>