I have a report based on a complex query that takes a long time to run and is
timing out. I've tuned indexes the best I know how, I've set the "Do not
timeout report execution" in the Home page Site Settings and in the
individual report properties, but it still times out. Any suggestions will be
most appreciated.hi Dan,
I'm also seeing the same behavior that you are. It takes about 60 minutes
for our reports to timeout. How long do your reports run before they timeout?
Thanks,
Matt
If I make any progress on this, I will let you know.
"Dan" wrote:
> I have a report based on a complex query that takes a long time to run and is
> timing out. I've tuned indexes the best I know how, I've set the "Do not
> timeout report execution" in the Home page Site Settings and in the
> individual report properties, but it still times out. Any suggestions will be
> most appreciated.
>
Showing posts with label complex. Show all posts
Showing posts with label complex. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Report taking a long time to render
I have a somewhat complex query that takes about 2 seconds to run in
Query Analyzer. Yet it takes a full 30 seconds to generate the
report. If I remove the document map then it takes ~10 seconds. It
seems that there is just tremendous overhead or some other problem
when generating reports.
Does anyone have any suggestions how I can speed this up? We need up
to the minute data so I can't really use a snapshot.
Thanks!
Matt PennerOn Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> I have a somewhat complex query that takes about 2 seconds to run in
> Query Analyzer. Yet it takes a full 30 seconds to generate the
> report. If I remove the document map then it takes ~10 seconds. It
> seems that there is just tremendous overhead or some other problem
> when generating reports.
> Does anyone have any suggestions how I can speed this up? We need up
> to the minute data so I can't really use a snapshot.
> Thanks!
> Matt Penner
In general, a deployed report has to deal w/IIS and the SSRS web
service, which adds overhead. Aside form this fact, if you haven't
already, you will want to save the query for the report as a stored
procedure, this will improve performance and response time: due to the
stored procedure having a predefined query plan. Also, you will want
to run the stored procedure/query against the Database Engine Tuning
Advisor to determine if any table indexes will further improve your
performance based on this query. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||errr... How do you use a stored procedure in SSRS? It doesn't appear to be an
option.
--
Tom Garth
"EMartinez" wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> >
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> >
> > Thanks!
> > Matt Penner
>
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||RS has full and good stored procedure report. The problem is you probably
are just using the wizard. The wizard does not have good stored procedure
report. For some SP if you put in the query and do not use the query builder
to do this.
exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
However, depending on how the stored is written it still might have
troubles.
What I sometimes do is create a simple query that returns the fields I want
and use that with the wizard. Then open up the design, go to the dataset
tab. Click on the ..., change the command type to store procedure then in
the next box put in the name of the stored procedure (just the name, do not
put in exec or put in the parameter values). RS recognizes the parameters
and create Report Parameters for each parameter of the SP if it is SQL
Server. Note that you can use SP from other systems but the mechanism is
different (I write lots of SP for Sybase).
If anyone tells you that SP do not work then this is just flat out wrong. In
some cases there are design issues but every single issue can be worked out.
Here is a list of how the SP should be written (most of which is concerning
temp tables)
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...) if you do
not get field list returned for your stored procedure.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). The only time I have had to do this is when one SP
is calling another SP and the first SP has created a temp table that the
second SP puts data into.Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I use SP extensively from both SQL Server and Sybase.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> an
> option.
> --
> Tom Garth
>
> "EMartinez" wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> >
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> >
>> > Thanks!
>> > Matt Penner
>>
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||Bruce,
This is terrific stuff! No one ever told me that SPs wouldn't work. I
surmised it as you said, from using the wizard. I considered it to be a major
drawback to using SSRS, but will have to do some rethinking on that now.
I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
it in reporting. Especially when using temp tables for a Crystal Report.
Thank you for taking the time to reply to my post.
Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com
Tom Garth
"Bruce L-C [MVP]" wrote:
> RS has full and good stored procedure report. The problem is you probably
> are just using the wizard. The wizard does not have good stored procedure
> report. For some SP if you put in the query and do not use the query builder
> to do this.
> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
> However, depending on how the stored is written it still might have
> troubles.
> What I sometimes do is create a simple query that returns the fields I want
> and use that with the wizard. Then open up the design, go to the dataset
> tab. Click on the ..., change the command type to store procedure then in
> the next box put in the name of the stored procedure (just the name, do not
> put in exec or put in the parameter values). RS recognizes the parameters
> and create Report Parameters for each parameter of the SP if it is SQL
> Server. Note that you can use SP from other systems but the mechanism is
> different (I write lots of SP for Sybase).
> If anyone tells you that SP do not work then this is just flat out wrong. In
> some cases there are design issues but every single issue can be worked out.
> Here is a list of how the SP should be written (most of which is concerning
> temp tables)
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...) if you do
> not get field list returned for your stored procedure.
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP). The only time I have had to do this is when one SP
> is calling another SP and the first SP has created a temp table that the
> second SP puts data into.Here is his explanation: "The issue with RS is that
> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
> because Temp tables don't get created if you select from the temp table the
> metadata from the rowset can't be returned. This can be worked around by
> turning FMTONLY OFF in the SP."
> I use SP extensively from both SQL Server and Sybase.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> > errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> > an
> > option.
> > --
> > Tom Garth
> >
> >
> > "EMartinez" wrote:
> >
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> >
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> >
> >> > Thanks!
> >> > Matt Penner
> >>
> >>
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >>
> >> Regards,
> >>
> >> Enrique Martinez
> >> Sr. Software Consultant
> >>
> >>
>
>|||No problem. Glad to be of help.
Most of the issues with SP tends to be because of how the SP is written. For
instance, a lot of people drop their temp tables at the end of the SP. This
messes up RS. Instead you just let SQL Server clean up the temp tables when
it goes out of scope.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:DC7C9319-3451-49F6-91F9-2A31EE9A7A9B@.microsoft.com...
> Bruce,
> This is terrific stuff! No one ever told me that SPs wouldn't work. I
> surmised it as you said, from using the wizard. I considered it to be a
> major
> drawback to using SSRS, but will have to do some rethinking on that now.
> I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
> it in reporting. Especially when using temp tables for a Crystal Report.
> Thank you for taking the time to reply to my post.
> Tom Garth
> Developer / Integration Specialist
> R. L. Nelson and Associates, Inc.
> 1400 Technology Drive
> Harrisonburg, VA 22802
> Main Office: (888) 313-0647
> www.rlninc.com
>
> --
> Tom Garth
>
> "Bruce L-C [MVP]" wrote:
>> RS has full and good stored procedure report. The problem is you probably
>> are just using the wizard. The wizard does not have good stored procedure
>> report. For some SP if you put in the query and do not use the query
>> builder
>> to do this.
>> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
>> However, depending on how the stored is written it still might have
>> troubles.
>> What I sometimes do is create a simple query that returns the fields I
>> want
>> and use that with the wizard. Then open up the design, go to the dataset
>> tab. Click on the ..., change the command type to store procedure then in
>> the next box put in the name of the stored procedure (just the name, do
>> not
>> put in exec or put in the parameter values). RS recognizes the parameters
>> and create Report Parameters for each parameter of the SP if it is SQL
>> Server. Note that you can use SP from other systems but the mechanism is
>> different (I write lots of SP for Sybase).
>> If anyone tells you that SP do not work then this is just flat out wrong.
>> In
>> some cases there are design issues but every single issue can be worked
>> out.
>> Here is a list of how the SP should be written (most of which is
>> concerning
>> temp tables)
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...) if you do
>> not get field list returned for your stored procedure.
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP). The only time I have had to do this is when one
>> SP
>> is calling another SP and the first SP has created a temp table that the
>> second SP puts data into.Here is his explanation: "The issue with RS is
>> that
>> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
>> because Temp tables don't get created if you select from the temp table
>> the
>> metadata from the rowset can't be returned. This can be worked around by
>> turning FMTONLY OFF in the SP."
>> I use SP extensively from both SQL Server and Sybase.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
>> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
>> > errr... How do you use a stored procedure in SSRS? It doesn't appear to
>> > be
>> > an
>> > option.
>> > --
>> > Tom Garth
>> >
>> >
>> > "EMartinez" wrote:
>> >
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> >
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> >
>> >> > Thanks!
>> >> > Matt Penner
>> >>
>> >>
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >>
>> >> Regards,
>> >>
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>> >>
>> >>
>>|||Well, as I said, the raw query returns results in <2 seconds. This
purely in development right now and we'll most likely move this to
stored procedures in the future but that doesn't seem to be the issue.
The waiting time seems to be on the rendering side.
If SSRS is fast and the overhead of IIS is causing the issue (seems
unlikely) are there any good sites to look at to help improve this?
All I've been able to find are the MS recommended enterprise rollout
with several servers. We don't have these kind of resources for SSRS,
and currently there are only 4 reports and 4 users. I am disappointed
that it takes this long to render simple reports when we're not even
in production capacity.
Thanks,
Matt
On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> > Thanks!
> > Matt Penner
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||RS currently renders in RAM. The amount of data is what matters. My guess is
that the 2 seconds is enough to bring over the data and the rest is
rendering. The document map must require more compute time.
A couple of questions, what are you rendering to? HTML? PDF? that makes a
difference as well. Also, you didn't mention what version you are on. How
much RAM is on your box?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> Well, as I said, the raw query returns results in <2 seconds. This
> purely in development right now and we'll most likely move this to
> stored procedures in the future but that doesn't seem to be the issue.
> The waiting time seems to be on the rendering side.
> If SSRS is fast and the overhead of IIS is causing the issue (seems
> unlikely) are there any good sites to look at to help improve this?
> All I've been able to find are the MS recommended enterprise rollout
> with several servers. We don't have these kind of resources for SSRS,
> and currently there are only 4 reports and 4 users. I am disappointed
> that it takes this long to render simple reports when we're not even
> in production capacity.
> Thanks,
> Matt
> On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> > Thanks!
>> > Matt Penner
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
the MS Report Viewer. Viewing the report during testing on my dev
machine as well as from the server after deployment seem to give the
same response times. This would make sense if the rendering is purely
on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
but I usually have several things open like Visual Studio, SQL
Management Studio, etc.
If the Report Viewer is entirely client side then there's not much I
can do about it. I didn't realize this is how it works. We have
several different computer models throughout our network so optimizing
the client side would to be difficult at best.
Thanks, I'll just live with it. :)
If there are any other comments out there that would be great but I'll
just assume this is the way it is until MS develops a better viewer.
On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> RS currently renders in RAM. The amount of data is what matters. My guess is
> that the 2 seconds is enough to bring over the data and the rest is
> rendering. The document map must require more compute time.
> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> difference as well. Also, you didn't mention what version you are on. How
> much RAM is on your box?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> > Well, as I said, the raw query returns results in <2 seconds. This
> > purely in development right now and we'll most likely move this to
> > stored procedures in the future but that doesn't seem to be the issue.
> > The waiting time seems to be on the rendering side.
> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> > unlikely) are there any good sites to look at to help improve this?
> > All I've been able to find are the MS recommended enterprise rollout
> > with several servers. We don't have these kind of resources for SSRS,
> > and currently there are only 4 reports and 4 users. I am disappointed
> > that it takes this long to render simple reports when we're not even
> > in production capacity.
> > Thanks,
> > Matt
> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> > Thanks!
> >> > Matt Penner
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant|||The rendering is at the Server. It is NOT at the client.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> the MS Report Viewer. Viewing the report during testing on my dev
> machine as well as from the server after deployment seem to give the
> same response times. This would make sense if the rendering is purely
> on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> but I usually have several things open like Visual Studio, SQL
> Management Studio, etc.
> If the Report Viewer is entirely client side then there's not much I
> can do about it. I didn't realize this is how it works. We have
> several different computer models throughout our network so optimizing
> the client side would to be difficult at best.
> Thanks, I'll just live with it. :)
> If there are any other comments out there that would be great but I'll
> just assume this is the way it is until MS develops a better viewer.
> On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> RS currently renders in RAM. The amount of data is what matters. My guess
>> is
>> that the 2 seconds is enough to bring over the data and the rest is
>> rendering. The document map must require more compute time.
>> A couple of questions, what are you rendering to? HTML? PDF? that makes a
>> difference as well. Also, you didn't mention what version you are on. How
>> much RAM is on your box?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
>> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
>> > Well, as I said, the raw query returns results in <2 seconds. This
>> > purely in development right now and we'll most likely move this to
>> > stored procedures in the future but that doesn't seem to be the issue.
>> > The waiting time seems to be on the rendering side.
>> > If SSRS is fast and the overhead of IIS is causing the issue (seems
>> > unlikely) are there any good sites to look at to help improve this?
>> > All I've been able to find are the MS recommended enterprise rollout
>> > with several servers. We don't have these kind of resources for SSRS,
>> > and currently there are only 4 reports and 4 users. I am disappointed
>> > that it takes this long to render simple reports when we're not even
>> > in production capacity.
>> > Thanks,
>> > Matt
>> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> > Thanks!
>> >> > Matt Penner
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>|||Ahh, that did seem a little strange but I haven't had the time to look
into it.
Anyway, the server is a dual Xeon 3Ghz Dual Core with 4GB of Ram,
although it is a pretty heavy lifter for one of our SQL databases.
SSRS was more of a test and afterthought on this server. It is quite
possible that SQL 2005 is hogging all the resources. What's the best
way to view the resources being used by SSRS and alter this? Should
SQL server and SSRS play nicely together or do I really need to
manually allocate memory to each?
Thanks,
Matt
On Sep 28, 9:14 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> The rendering is at the Server. It is NOT at the client.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> > This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> > the MS Report Viewer. Viewing the report during testing on my dev
> > machine as well as from the server after deployment seem to give the
> > same response times. This would make sense if the rendering is purely
> > on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> > but I usually have several things open like Visual Studio, SQL
> > Management Studio, etc.
> > If the Report Viewer is entirely client side then there's not much I
> > can do about it. I didn't realize this is how it works. We have
> > several different computer models throughout our network so optimizing
> > the client side would to be difficult at best.
> > Thanks, I'll just live with it. :)
> > If there are any other comments out there that would be great but I'll
> > just assume this is the way it is until MS develops a better viewer.
> > On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> RS currently renders in RAM. The amount of data is what matters. My guess
> >> is
> >> that the 2 seconds is enough to bring over the data and the rest is
> >> rendering. The document map must require more compute time.
> >> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> >> difference as well. Also, you didn't mention what version you are on. How
> >> much RAM is on your box?
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> >>news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> >> > Well, as I said, the raw query returns results in <2 seconds. This
> >> > purely in development right now and we'll most likely move this to
> >> > stored procedures in the future but that doesn't seem to be the issue.
> >> > The waiting time seems to be on the rendering side.
> >> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> >> > unlikely) are there any good sites to look at to help improve this?
> >> > All I've been able to find are the MS recommended enterprise rollout
> >> > with several servers. We don't have these kind of resources for SSRS,
> >> > and currently there are only 4 reports and 4 users. I am disappointed
> >> > that it takes this long to render simple reports when we're not even
> >> > in production capacity.
> >> > Thanks,
> >> > Matt
> >> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> >> > seems that there is just tremendous overhead or some other problem
> >> >> > when generating reports.
> >> >> > Does anyone have any suggestions how I can speed this up? We need
> >> >> > up
> >> >> > to the minute data so I can't really use a snapshot.
> >> >> > Thanks!
> >> >> > Matt Penner
> >> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> >> service, which adds overhead. Aside form this fact, if you haven't
> >> >> already, you will want to save the query for the report as a stored
> >> >> procedure, this will improve performance and response time: due to the
> >> >> stored procedure having a predefined query plan. Also, you will want
> >> >> to run the stored procedure/query against the Database Engine Tuning
> >> >> Advisor to determine if any table indexes will further improve your
> >> >> performance based on this query. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant|||Try to simplify the report layout as much as possible - I typically
render to PDF, and find the following tips helpful -
= Use tables in place of lists (tables with grouping vs. nested lists)
= Avoid using cell borders - used alternate row shading instead to
differentiate rows
= Set the 'Can Grow' and 'Can Shrink' properties of report objects to
False wherever possible
= minimize the number of objects (merge empty cells in a table, for
instance)
= minimize the number of fonts
I believe the view is rendering in HTML, so think in terms of setting
up a report that will produce the smallest, simplest HTML document
that will correctly present your data.|||Hi Parker,
Thanks. These are great tips. I never thought of rendering straight
to PDF. Since the MS Report viewer doesn't seem to offer much that
pdf can't do, other than toggling sections, this may be a better
option. It makes sense that simplifying the end result may reduce the
render time.
I'll experiment with all this.
Matt
On Sep 29, 6:15 am, Parker <psm...@.iquest.net> wrote:
> Try to simplify the report layout as much as possible - I typically
> render to PDF, and find the following tips helpful -
> = Use tables in place of lists (tables with grouping vs. nested lists)
> = Avoid using cell borders - used alternate row shading instead to
> differentiate rows
> = Set the 'Can Grow' and 'Can Shrink' properties of report objects to
> False wherever possible
> = minimize the number of objects (merge empty cells in a table, for
> instance)
> = minimize the number of fonts
> I believe the view is rendering in HTML, so think in terms of setting
> up a report that will produce the smallest, simplest HTML document
> that will correctly present your data.
Query Analyzer. Yet it takes a full 30 seconds to generate the
report. If I remove the document map then it takes ~10 seconds. It
seems that there is just tremendous overhead or some other problem
when generating reports.
Does anyone have any suggestions how I can speed this up? We need up
to the minute data so I can't really use a snapshot.
Thanks!
Matt PennerOn Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> I have a somewhat complex query that takes about 2 seconds to run in
> Query Analyzer. Yet it takes a full 30 seconds to generate the
> report. If I remove the document map then it takes ~10 seconds. It
> seems that there is just tremendous overhead or some other problem
> when generating reports.
> Does anyone have any suggestions how I can speed this up? We need up
> to the minute data so I can't really use a snapshot.
> Thanks!
> Matt Penner
In general, a deployed report has to deal w/IIS and the SSRS web
service, which adds overhead. Aside form this fact, if you haven't
already, you will want to save the query for the report as a stored
procedure, this will improve performance and response time: due to the
stored procedure having a predefined query plan. Also, you will want
to run the stored procedure/query against the Database Engine Tuning
Advisor to determine if any table indexes will further improve your
performance based on this query. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||errr... How do you use a stored procedure in SSRS? It doesn't appear to be an
option.
--
Tom Garth
"EMartinez" wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> >
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> >
> > Thanks!
> > Matt Penner
>
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||RS has full and good stored procedure report. The problem is you probably
are just using the wizard. The wizard does not have good stored procedure
report. For some SP if you put in the query and do not use the query builder
to do this.
exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
However, depending on how the stored is written it still might have
troubles.
What I sometimes do is create a simple query that returns the fields I want
and use that with the wizard. Then open up the design, go to the dataset
tab. Click on the ..., change the command type to store procedure then in
the next box put in the name of the stored procedure (just the name, do not
put in exec or put in the parameter values). RS recognizes the parameters
and create Report Parameters for each parameter of the SP if it is SQL
Server. Note that you can use SP from other systems but the mechanism is
different (I write lots of SP for Sybase).
If anyone tells you that SP do not work then this is just flat out wrong. In
some cases there are design issues but every single issue can be worked out.
Here is a list of how the SP should be written (most of which is concerning
temp tables)
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...) if you do
not get field list returned for your stored procedure.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). The only time I have had to do this is when one SP
is calling another SP and the first SP has created a temp table that the
second SP puts data into.Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I use SP extensively from both SQL Server and Sybase.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> an
> option.
> --
> Tom Garth
>
> "EMartinez" wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> >
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> >
>> > Thanks!
>> > Matt Penner
>>
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||Bruce,
This is terrific stuff! No one ever told me that SPs wouldn't work. I
surmised it as you said, from using the wizard. I considered it to be a major
drawback to using SSRS, but will have to do some rethinking on that now.
I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
it in reporting. Especially when using temp tables for a Crystal Report.
Thank you for taking the time to reply to my post.
Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com
Tom Garth
"Bruce L-C [MVP]" wrote:
> RS has full and good stored procedure report. The problem is you probably
> are just using the wizard. The wizard does not have good stored procedure
> report. For some SP if you put in the query and do not use the query builder
> to do this.
> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
> However, depending on how the stored is written it still might have
> troubles.
> What I sometimes do is create a simple query that returns the fields I want
> and use that with the wizard. Then open up the design, go to the dataset
> tab. Click on the ..., change the command type to store procedure then in
> the next box put in the name of the stored procedure (just the name, do not
> put in exec or put in the parameter values). RS recognizes the parameters
> and create Report Parameters for each parameter of the SP if it is SQL
> Server. Note that you can use SP from other systems but the mechanism is
> different (I write lots of SP for Sybase).
> If anyone tells you that SP do not work then this is just flat out wrong. In
> some cases there are design issues but every single issue can be worked out.
> Here is a list of how the SP should be written (most of which is concerning
> temp tables)
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...) if you do
> not get field list returned for your stored procedure.
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP). The only time I have had to do this is when one SP
> is calling another SP and the first SP has created a temp table that the
> second SP puts data into.Here is his explanation: "The issue with RS is that
> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
> because Temp tables don't get created if you select from the temp table the
> metadata from the rowset can't be returned. This can be worked around by
> turning FMTONLY OFF in the SP."
> I use SP extensively from both SQL Server and Sybase.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> > errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> > an
> > option.
> > --
> > Tom Garth
> >
> >
> > "EMartinez" wrote:
> >
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> >
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> >
> >> > Thanks!
> >> > Matt Penner
> >>
> >>
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >>
> >> Regards,
> >>
> >> Enrique Martinez
> >> Sr. Software Consultant
> >>
> >>
>
>|||No problem. Glad to be of help.
Most of the issues with SP tends to be because of how the SP is written. For
instance, a lot of people drop their temp tables at the end of the SP. This
messes up RS. Instead you just let SQL Server clean up the temp tables when
it goes out of scope.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:DC7C9319-3451-49F6-91F9-2A31EE9A7A9B@.microsoft.com...
> Bruce,
> This is terrific stuff! No one ever told me that SPs wouldn't work. I
> surmised it as you said, from using the wizard. I considered it to be a
> major
> drawback to using SSRS, but will have to do some rethinking on that now.
> I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
> it in reporting. Especially when using temp tables for a Crystal Report.
> Thank you for taking the time to reply to my post.
> Tom Garth
> Developer / Integration Specialist
> R. L. Nelson and Associates, Inc.
> 1400 Technology Drive
> Harrisonburg, VA 22802
> Main Office: (888) 313-0647
> www.rlninc.com
>
> --
> Tom Garth
>
> "Bruce L-C [MVP]" wrote:
>> RS has full and good stored procedure report. The problem is you probably
>> are just using the wizard. The wizard does not have good stored procedure
>> report. For some SP if you put in the query and do not use the query
>> builder
>> to do this.
>> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
>> However, depending on how the stored is written it still might have
>> troubles.
>> What I sometimes do is create a simple query that returns the fields I
>> want
>> and use that with the wizard. Then open up the design, go to the dataset
>> tab. Click on the ..., change the command type to store procedure then in
>> the next box put in the name of the stored procedure (just the name, do
>> not
>> put in exec or put in the parameter values). RS recognizes the parameters
>> and create Report Parameters for each parameter of the SP if it is SQL
>> Server. Note that you can use SP from other systems but the mechanism is
>> different (I write lots of SP for Sybase).
>> If anyone tells you that SP do not work then this is just flat out wrong.
>> In
>> some cases there are design issues but every single issue can be worked
>> out.
>> Here is a list of how the SP should be written (most of which is
>> concerning
>> temp tables)
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...) if you do
>> not get field list returned for your stored procedure.
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP). The only time I have had to do this is when one
>> SP
>> is calling another SP and the first SP has created a temp table that the
>> second SP puts data into.Here is his explanation: "The issue with RS is
>> that
>> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
>> because Temp tables don't get created if you select from the temp table
>> the
>> metadata from the rowset can't be returned. This can be worked around by
>> turning FMTONLY OFF in the SP."
>> I use SP extensively from both SQL Server and Sybase.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
>> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
>> > errr... How do you use a stored procedure in SSRS? It doesn't appear to
>> > be
>> > an
>> > option.
>> > --
>> > Tom Garth
>> >
>> >
>> > "EMartinez" wrote:
>> >
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> >
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> >
>> >> > Thanks!
>> >> > Matt Penner
>> >>
>> >>
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >>
>> >> Regards,
>> >>
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>> >>
>> >>
>>|||Well, as I said, the raw query returns results in <2 seconds. This
purely in development right now and we'll most likely move this to
stored procedures in the future but that doesn't seem to be the issue.
The waiting time seems to be on the rendering side.
If SSRS is fast and the overhead of IIS is causing the issue (seems
unlikely) are there any good sites to look at to help improve this?
All I've been able to find are the MS recommended enterprise rollout
with several servers. We don't have these kind of resources for SSRS,
and currently there are only 4 reports and 4 users. I am disappointed
that it takes this long to render simple reports when we're not even
in production capacity.
Thanks,
Matt
On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> > Thanks!
> > Matt Penner
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||RS currently renders in RAM. The amount of data is what matters. My guess is
that the 2 seconds is enough to bring over the data and the rest is
rendering. The document map must require more compute time.
A couple of questions, what are you rendering to? HTML? PDF? that makes a
difference as well. Also, you didn't mention what version you are on. How
much RAM is on your box?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> Well, as I said, the raw query returns results in <2 seconds. This
> purely in development right now and we'll most likely move this to
> stored procedures in the future but that doesn't seem to be the issue.
> The waiting time seems to be on the rendering side.
> If SSRS is fast and the overhead of IIS is causing the issue (seems
> unlikely) are there any good sites to look at to help improve this?
> All I've been able to find are the MS recommended enterprise rollout
> with several servers. We don't have these kind of resources for SSRS,
> and currently there are only 4 reports and 4 users. I am disappointed
> that it takes this long to render simple reports when we're not even
> in production capacity.
> Thanks,
> Matt
> On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> > Thanks!
>> > Matt Penner
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
the MS Report Viewer. Viewing the report during testing on my dev
machine as well as from the server after deployment seem to give the
same response times. This would make sense if the rendering is purely
on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
but I usually have several things open like Visual Studio, SQL
Management Studio, etc.
If the Report Viewer is entirely client side then there's not much I
can do about it. I didn't realize this is how it works. We have
several different computer models throughout our network so optimizing
the client side would to be difficult at best.
Thanks, I'll just live with it. :)
If there are any other comments out there that would be great but I'll
just assume this is the way it is until MS develops a better viewer.
On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> RS currently renders in RAM. The amount of data is what matters. My guess is
> that the 2 seconds is enough to bring over the data and the rest is
> rendering. The document map must require more compute time.
> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> difference as well. Also, you didn't mention what version you are on. How
> much RAM is on your box?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> > Well, as I said, the raw query returns results in <2 seconds. This
> > purely in development right now and we'll most likely move this to
> > stored procedures in the future but that doesn't seem to be the issue.
> > The waiting time seems to be on the rendering side.
> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> > unlikely) are there any good sites to look at to help improve this?
> > All I've been able to find are the MS recommended enterprise rollout
> > with several servers. We don't have these kind of resources for SSRS,
> > and currently there are only 4 reports and 4 users. I am disappointed
> > that it takes this long to render simple reports when we're not even
> > in production capacity.
> > Thanks,
> > Matt
> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> > Thanks!
> >> > Matt Penner
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant|||The rendering is at the Server. It is NOT at the client.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> the MS Report Viewer. Viewing the report during testing on my dev
> machine as well as from the server after deployment seem to give the
> same response times. This would make sense if the rendering is purely
> on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> but I usually have several things open like Visual Studio, SQL
> Management Studio, etc.
> If the Report Viewer is entirely client side then there's not much I
> can do about it. I didn't realize this is how it works. We have
> several different computer models throughout our network so optimizing
> the client side would to be difficult at best.
> Thanks, I'll just live with it. :)
> If there are any other comments out there that would be great but I'll
> just assume this is the way it is until MS develops a better viewer.
> On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> RS currently renders in RAM. The amount of data is what matters. My guess
>> is
>> that the 2 seconds is enough to bring over the data and the rest is
>> rendering. The document map must require more compute time.
>> A couple of questions, what are you rendering to? HTML? PDF? that makes a
>> difference as well. Also, you didn't mention what version you are on. How
>> much RAM is on your box?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
>> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
>> > Well, as I said, the raw query returns results in <2 seconds. This
>> > purely in development right now and we'll most likely move this to
>> > stored procedures in the future but that doesn't seem to be the issue.
>> > The waiting time seems to be on the rendering side.
>> > If SSRS is fast and the overhead of IIS is causing the issue (seems
>> > unlikely) are there any good sites to look at to help improve this?
>> > All I've been able to find are the MS recommended enterprise rollout
>> > with several servers. We don't have these kind of resources for SSRS,
>> > and currently there are only 4 reports and 4 users. I am disappointed
>> > that it takes this long to render simple reports when we're not even
>> > in production capacity.
>> > Thanks,
>> > Matt
>> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> > Thanks!
>> >> > Matt Penner
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>|||Ahh, that did seem a little strange but I haven't had the time to look
into it.
Anyway, the server is a dual Xeon 3Ghz Dual Core with 4GB of Ram,
although it is a pretty heavy lifter for one of our SQL databases.
SSRS was more of a test and afterthought on this server. It is quite
possible that SQL 2005 is hogging all the resources. What's the best
way to view the resources being used by SSRS and alter this? Should
SQL server and SSRS play nicely together or do I really need to
manually allocate memory to each?
Thanks,
Matt
On Sep 28, 9:14 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> The rendering is at the Server. It is NOT at the client.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> > This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> > the MS Report Viewer. Viewing the report during testing on my dev
> > machine as well as from the server after deployment seem to give the
> > same response times. This would make sense if the rendering is purely
> > on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> > but I usually have several things open like Visual Studio, SQL
> > Management Studio, etc.
> > If the Report Viewer is entirely client side then there's not much I
> > can do about it. I didn't realize this is how it works. We have
> > several different computer models throughout our network so optimizing
> > the client side would to be difficult at best.
> > Thanks, I'll just live with it. :)
> > If there are any other comments out there that would be great but I'll
> > just assume this is the way it is until MS develops a better viewer.
> > On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> RS currently renders in RAM. The amount of data is what matters. My guess
> >> is
> >> that the 2 seconds is enough to bring over the data and the rest is
> >> rendering. The document map must require more compute time.
> >> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> >> difference as well. Also, you didn't mention what version you are on. How
> >> much RAM is on your box?
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> >>news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> >> > Well, as I said, the raw query returns results in <2 seconds. This
> >> > purely in development right now and we'll most likely move this to
> >> > stored procedures in the future but that doesn't seem to be the issue.
> >> > The waiting time seems to be on the rendering side.
> >> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> >> > unlikely) are there any good sites to look at to help improve this?
> >> > All I've been able to find are the MS recommended enterprise rollout
> >> > with several servers. We don't have these kind of resources for SSRS,
> >> > and currently there are only 4 reports and 4 users. I am disappointed
> >> > that it takes this long to render simple reports when we're not even
> >> > in production capacity.
> >> > Thanks,
> >> > Matt
> >> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> >> > seems that there is just tremendous overhead or some other problem
> >> >> > when generating reports.
> >> >> > Does anyone have any suggestions how I can speed this up? We need
> >> >> > up
> >> >> > to the minute data so I can't really use a snapshot.
> >> >> > Thanks!
> >> >> > Matt Penner
> >> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> >> service, which adds overhead. Aside form this fact, if you haven't
> >> >> already, you will want to save the query for the report as a stored
> >> >> procedure, this will improve performance and response time: due to the
> >> >> stored procedure having a predefined query plan. Also, you will want
> >> >> to run the stored procedure/query against the Database Engine Tuning
> >> >> Advisor to determine if any table indexes will further improve your
> >> >> performance based on this query. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant|||Try to simplify the report layout as much as possible - I typically
render to PDF, and find the following tips helpful -
= Use tables in place of lists (tables with grouping vs. nested lists)
= Avoid using cell borders - used alternate row shading instead to
differentiate rows
= Set the 'Can Grow' and 'Can Shrink' properties of report objects to
False wherever possible
= minimize the number of objects (merge empty cells in a table, for
instance)
= minimize the number of fonts
I believe the view is rendering in HTML, so think in terms of setting
up a report that will produce the smallest, simplest HTML document
that will correctly present your data.|||Hi Parker,
Thanks. These are great tips. I never thought of rendering straight
to PDF. Since the MS Report viewer doesn't seem to offer much that
pdf can't do, other than toggling sections, this may be a better
option. It makes sense that simplifying the end result may reduce the
render time.
I'll experiment with all this.
Matt
On Sep 29, 6:15 am, Parker <psm...@.iquest.net> wrote:
> Try to simplify the report layout as much as possible - I typically
> render to PDF, and find the following tips helpful -
> = Use tables in place of lists (tables with grouping vs. nested lists)
> = Avoid using cell borders - used alternate row shading instead to
> differentiate rows
> = Set the 'Can Grow' and 'Can Shrink' properties of report objects to
> False wherever possible
> = minimize the number of objects (merge empty cells in a table, for
> instance)
> = minimize the number of fonts
> I believe the view is rendering in HTML, so think in terms of setting
> up a report that will produce the smallest, simplest HTML document
> that will correctly present your data.
Monday, February 20, 2012
Report render to PDF hangs
I've got a complex report with many subreports that is running on a reporting services 2005 machine with sp2 installed. The report was working well, producing a 2800 page report in under 10 minutes. I made a change to an expression in a table's column header and one change to the detail and the report stopped working. It would just hang for hours with no messages in the logs.
I tried to simplify what the report server had to do by removing the sum's to the database side's stored proc but that didn't work. I finally managed to get the report to work by removing a totals section to a subreport. I've also had the same hanging condition occur when I merged some columns in the table. I also found a workaround for this problem.
Is anyone else finding problems like this? They don't seem to be code related.
Is there a limit to how complex a report can be in reporting services or the PDF rendering part of the process?
Thanks for any info
There is no easy to define limit to how "much" of a report can be rendered to PDF. It depends on size and complexity of the report and muscle of the server(s). Can you describe the hardware a bit? If you capture a perfmon while the hang is occuring, how does CPU, memory, disk, and network look? Also, is this reproducible? If you revert the change, does the report export?
Thank you.
|||
The machines running are 4 processor 3.2Ghz zeon's with 3GB Ram.
We didn't run perfmon, but normally get paged if there are performance issues.
I am able to reproduce this problem. I narrowed the recordset down to a particular agency, a subset of the full recordset The subset recordset contains 59 rows for the main report and 20 rows for the subreport, so it's not a large amount of data. We have agencies that are many times more complicated that print fine.
Strangely enough, I got it to work, when I pushed off the totals section to a subreport. That section had 2 running sums in it and some expressions based on data fields. I passed those over to the subreport as parameters and it worked fine.
This is not the only time, we've had this hang and had to look for a workaround. I also had a problem like this when I merged some columns in a table. The problem was happening to a completely different agency than before. Now when I develop in this report, I do one thing at a time, then deploy and run to be sure I didn't introduce another hang situation.
Is this behavior common to reporting services?
|||If the dataset is large, the report is complex, the hardware is underpowered, or some combination of the three, exporting to PDF can get bogged down, even in SQL Server 2005 SP2. One way to figure out what's going on is to look at the ExecutionLog table in the ReportServer database.
Execute the report and then look at the last 5 columns. If RowCount is large, consider pushing off some data filtering into the TSQL - this should also decrease TimeDataRetrieval. If TimeProcessing is large, see if there is anything you're doing in the report that can be better handled by SQL server - like, sorting or aggregation. Also, try and reduce the complexity of any expressions you have in the report. If TimeRendering is large, it could be you're running into inefficiencies in the rendering engine. We've made good progress and improved perf in RS 2005 but we're still not where I'd like us to be so have more work to do. If ByteCount is large, consider breaking the report up so users don't have to deal with 50M PDF files. If archival is your goal, you might consider a different format like HTML that is much easier to grep later than PDF. Tudor has some suggestions as well on http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx.
"Hanging" is not common, though slow rendering when there's a lot to do or not enough hardware to do it on is more common.
I don't understand why merging columns would make that much of an impact, however. If you have a repro, please open a bug so I can take a look. If you can't get to http://connect.microsoft.com, you can email me the details directly (click on my name to the left and you'll find my email address).
Thank you.
|||Hi Donovan,
Thanks for your help.
I've got a question still, I'm not sure that this is bug ticket worthy so I figured I'll run it by you here first.
This is from the ExecutionLog table.
This report was successful. This record is the full recordset and produced. I moved a totals section to a sub report to get it to work.
RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
-- -- -- -- -- - --
0 PDF 2007-03-09 10:14:42.723 2007-03-09 10:22:30.500 19070 269112 179356 1 rsSuccess 8969543 8569
This recordset here, is one that didn't work. This code is prior to me moving the totals section out to a subreport. Also, this recordset is a subset, just one agency and it's associated records. After a half hour I cancelled the job.
The TimeRendering is very large and the TimeDataRetrieval also seems large for that recordset.
RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
-- - -- -- -- -- - - --
0 PDF 2007-03-08 10:47:09.303 2007-03-08 11:15:53.803 20642 2690 1700930 1 rsJobWasCanceled 0 131
The difference between the 2 versions of code, is that the one that's failing has 3 footer rows for the same grouping, 4 of the text boxes contain =FormatCurrency(Fields!XYZ.Value) type values and one contains an =IIF(Fields!CompType.Value = "SA", "XYZ Based Compensation", "ABC Based Compensation") + " Totals For This Period:"
I replaced those 3 footers, with one footer that calls a subreport, ironically, the sub reports parameters contains the same formatcurrency items and the iif.
I'm not sure what to think, it looks like a bug, but wouldn't TimeDataRetrieval indicate database side inefficiency?
What do you think?
Scott
|||That doesn't sound right - time rendering seems too high. Would you please email me a copy of the rdls so I can see what's happening in the code?
The TIFF and PDF renderer share a lot of the same code. Is TIFF also that slow in exporting that report?
Regarding the TimeDataRetrieval column, if you take the queries out of the reports and run them against the server manually, how do the times compare?
Thank you.
Report render to PDF hangs
I've got a complex report with many subreports that is running on a reporting services 2005 machine with sp2 installed. The report was working well, producing a 2800 page report in under 10 minutes. I made a change to an expression in a table's column header and one change to the detail and the report stopped working. It would just hang for hours with no messages in the logs.
I tried to simplify what the report server had to do by removing the sum's to the database side's stored proc but that didn't work. I finally managed to get the report to work by removing a totals section to a subreport. I've also had the same hanging condition occur when I merged some columns in the table. I also found a workaround for this problem.
Is anyone else finding problems like this? They don't seem to be code related.
Is there a limit to how complex a report can be in reporting services or the PDF rendering part of the process?
Thanks for any info
There is no easy to define limit to how "much" of a report can be rendered to PDF. It depends on size and complexity of the report and muscle of the server(s). Can you describe the hardware a bit? If you capture a perfmon while the hang is occuring, how does CPU, memory, disk, and network look? Also, is this reproducible? If you revert the change, does the report export?
Thank you.
|||The machines running are 4 processor 3.2Ghz zeon's with 3GB Ram.
We didn't run perfmon, but normally get paged if there are performance issues.
I am able to reproduce this problem. I narrowed the recordset down to a particular agency, a subset of the full recordset The subset recordset contains 59 rows for the main report and 20 rows for the subreport, so it's not a large amount of data. We have agencies that are many times more complicated that print fine.
Strangely enough, I got it to work, when I pushed off the totals section to a subreport. That section had 2 running sums in it and some expressions based on data fields. I passed those over to the subreport as parameters and it worked fine.
This is not the only time, we've had this hang and had to look for a workaround. I also had a problem like this when I merged some columns in a table. The problem was happening to a completely different agency than before. Now when I develop in this report, I do one thing at a time, then deploy and run to be sure I didn't introduce another hang situation.
Is this behavior common to reporting services?
|||If the dataset is large, the report is complex, the hardware is underpowered, or some combination of the three, exporting to PDF can get bogged down, even in SQL Server 2005 SP2. One way to figure out what's going on is to look at the ExecutionLog table in the ReportServer database.
Execute the report and then look at the last 5 columns. If RowCount is large, consider pushing off some data filtering into the TSQL - this should also decrease TimeDataRetrieval. If TimeProcessing is large, see if there is anything you're doing in the report that can be better handled by SQL server - like, sorting or aggregation. Also, try and reduce the complexity of any expressions you have in the report. If TimeRendering is large, it could be you're running into inefficiencies in the rendering engine. We've made good progress and improved perf in RS 2005 but we're still not where I'd like us to be so have more work to do. If ByteCount is large, consider breaking the report up so users don't have to deal with 50M PDF files. If archival is your goal, you might consider a different format like HTML that is much easier to grep later than PDF. Tudor has some suggestions as well on http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx.
"Hanging" is not common, though slow rendering when there's a lot to do or not enough hardware to do it on is more common.
I don't understand why merging columns would make that much of an impact, however. If you have a repro, please open a bug so I can take a look. If you can't get to http://connect.microsoft.com, you can email me the details directly (click on my name to the left and you'll find my email address).
Thank you.
|||Hi Donovan,
Thanks for your help.
I've got a question still, I'm not sure that this is bug ticket worthy so I figured I'll run it by you here first.
This is from the ExecutionLog table.
This report was successful. This record is the full recordset and produced. I moved a totals section to a sub report to get it to work.
RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount -- -- -- -- -- - -- 0 PDF 2007-03-09 10:14:42.723 2007-03-09 10:22:30.500 19070 269112 179356 1 rsSuccess 8969543 8569This recordset here, is one that didn't work. This code is prior to me moving the totals section out to a subreport. Also, this recordset is a subset, just one agency and it's associated records. After a half hour I cancelled the job.
The TimeRendering is very large and the TimeDataRetrieval also seems large for that recordset.
RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount -- - -- -- -- -- - - -- 0 PDF 2007-03-08 10:47:09.303 2007-03-08 11:15:53.803 20642 2690 1700930 1 rsJobWasCanceled 0 131The difference between the 2 versions of code, is that the one that's failing has 3 footer rows for the same grouping, 4 of the text boxes contain =FormatCurrency(Fields!XYZ.Value) type values and one contains an =IIF(Fields!CompType.Value = "SA", "XYZ Based Compensation", "ABC Based Compensation") + " Totals For This Period:"
I replaced those 3 footers, with one footer that calls a subreport, ironically, the sub reports parameters contains the same formatcurrency items and the iif.
I'm not sure what to think, it looks like a bug, but wouldn't TimeDataRetrieval indicate database side inefficiency?
What do you think?
Scott
|||That doesn't sound right - time rendering seems too high. Would you please email me a copy of the rdls so I can see what's happening in the code?
The TIFF and PDF renderer share a lot of the same code. Is TIFF also that slow in exporting that report?
Regarding the TimeDataRetrieval column, if you take the queries out of the reports and run them against the server manually, how do the times compare?
Thank you.
Subscribe to:
Posts (Atom)