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.