Export Slow from SQL Reporting Service

We have users experiencing very slow to export SQL Reporting Service 2005
rendered data to EXCEL. Is there any way to improve the performance?
BrianOn Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> We have users experiencing very slow to export SQL Reporting Service 2005
> rendered data to EXCEL. Is there any way to improve the performance?
> Brian
Usually this is due to either high activity on the web server that the
reports are on -or- that the query or stored procedure sourcing the
report needs to be revised to improve performance. I would suggest
using the Database Engine Tuning Adviser against the stored procedure
or query to implement indexes, etc to improve the performance. Hope
this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hello,
What about exporting to other formats like PDF or MHTML. Is it very slow
too?
How complicated are these reports and how much data do they contain?
Sometimes the reason of slow exporting to Excel is complex structure of
reports (a lot of cells, nested objects, etc.).
If reports contain a lot of hidden rows all of them are saving to Excel file
(even if they are unseen during exporting).
Maybe this is the reason.
Regards,
Radoslaw Lebkowski
U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> We have users experiencing very slow to export SQL Reporting Service 2005
> rendered data to EXCEL. Is there any way to improve the performance?
> Brian|||Thank you for the quick response.
The SQL Reporting 2005 report is a simple report which has no complex
structure in it. It is a single line type of report. However, it has the
company logo image on the title of the report. The report rendered within
one minute with 123 pages. When I export to EXCEL, it was ten times slower
then the web page rendered.
Does export require SQL to query the data again or it gathered the rendered
data to EXCEL?
Would increase SQL memory from dynamic to static with 3GB help? How about
caching the report?
Thanks,
Brian
"Radoslaw Lebkowski" wrote:
> Hello,
> What about exporting to other formats like PDF or MHTML. Is it very slow
> too?
> How complicated are these reports and how much data do they contain?
> Sometimes the reason of slow exporting to Excel is complex structure of
> reports (a lot of cells, nested objects, etc.).
> If reports contain a lot of hidden rows all of them are saving to Excel file
> (even if they are unseen during exporting).
> Maybe this is the reason.
>
> Regards,
> Radoslaw Lebkowski
>
> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> > We have users experiencing very slow to export SQL Reporting Service 2005
> > rendered data to EXCEL. Is there any way to improve the performance?
> >
> > Brian
>
>|||By changing the export from EXCEL to PDF, It does not make any difference.
When I ran the SQL Web Reporting, it rendered very fast. Only when I need to
export the data to EXCEL and it is much slower.
Brian
"EMartinez" wrote:
> On Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> > We have users experiencing very slow to export SQL Reporting Service 2005
> > rendered data to EXCEL. Is there any way to improve the performance?
> >
> > Brian
> Usually this is due to either high activity on the web server that the
> reports are on -or- that the query or stored procedure sourcing the
> report needs to be revised to improve performance. I would suggest
> using the Database Engine Tuning Adviser against the stored procedure
> or query to implement indexes, etc to improve the performance. Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 10, 11:25 am, Brian <B...@.discussions.microsoft.com> wrote:
> By changing the export from EXCEL to PDF, It does not make any difference.
> When I ran the SQL Web Reporting, it rendered very fast. Only when I need to
> export the data to EXCEL and it is much slower.
> Brian
> "EMartinez" wrote:
> > On Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> > > We have users experiencing very slow to export SQL Reporting Service 2005
> > > rendered data to EXCEL. Is there any way to improve the performance?
> > > Brian
> > Usually this is due to either high activity on the web server that the
> > reports are on -or- that the query or stored procedure sourcing the
> > report needs to be revised to improve performance. I would suggest
> > using the Database Engine Tuning Adviser against the stored procedure
> > or query to implement indexes, etc to improve the performance. Hope
> > this helps.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
If the image is large or there is a lot of report data to export, this
may be the reason. Have you tried the Database Engine Tuning Advisor
yet? I think that caching the report might improve the performance and
response time.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||If you render previous executed reports to other output format, it doesn't
require regathering data from datasources.
Rendering process uses intermediate format of these reports stored in RS
cache.
Rendering to output format uses SQL Server only for Report Processing
Extensions (not for executing SQL queries from datasources).
Rendering reports to PDF or Excel are the slowest operations whereas
exporting to HTML and CSV are the fastests methods.
I've heard a lot of complaints for slow rendering to PDF and Excel. It's
very common situation.
To improve speed of rendering reports try to find server's bottlenecks.
Maybe it's a CPU or server memory.
Try to use SQL Server Profiler to measure CPU usage during rendering to
different formats.
I hope it will be helpful.
Radoslaw Lebkowski
U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> Thank you for the quick response.
> The SQL Reporting 2005 report is a simple report which has no complex
> structure in it. It is a single line type of report. However, it has the
> company logo image on the title of the report. The report rendered within
> one minute with 123 pages. When I export to EXCEL, it was ten times
> slower
> then the web page rendered.
> Does export require SQL to query the data again or it gathered the
> rendered
> data to EXCEL?
> Would increase SQL memory from dynamic to static with 3GB help? How about
> caching the report?
> Thanks,
> Brian
>
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> What about exporting to other formats like PDF or MHTML. Is it very slow
>> too?
>> How complicated are these reports and how much data do they contain?
>> Sometimes the reason of slow exporting to Excel is complex structure of
>> reports (a lot of cells, nested objects, etc.).
>> If reports contain a lot of hidden rows all of them are saving to Excel
>> file
>> (even if they are unseen during exporting).
>> Maybe this is the reason.
>>
>> Regards,
>> Radoslaw Lebkowski
>>
>> U?ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
>> > We have users experiencing very slow to export SQL Reporting Service
>> > 2005
>> > rendered data to EXCEL. Is there any way to improve the performance?
>> >
>> > Brian
>>|||If it is much data at all then what you should do is have your users export
via CSV. It will still open it up in Excel. One other point, however. The
default CSV format is Unicode which Excel doesn't know how to handle (at
least Excel 2003 and earlier can't handle, I don't know about 2007). In RS
2005 you can change a configuration setting that causing CSV exports to be
in ASCII. CSV and HTML rendering is about the same performance wise.
As a test just try it. Excel will (I believe) put all the data in a single
column and then you have to use the menu in Excel to split the data out into
multiple columns.
I am gone next week to the MVP Summit in Seattle. All the newsgroups will
see very little MVP involvement for the next week.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> Thank you for the quick response.
> The SQL Reporting 2005 report is a simple report which has no complex
> structure in it. It is a single line type of report. However, it has the
> company logo image on the title of the report. The report rendered within
> one minute with 123 pages. When I export to EXCEL, it was ten times
> slower
> then the web page rendered.
> Does export require SQL to query the data again or it gathered the
> rendered
> data to EXCEL?
> Would increase SQL memory from dynamic to static with 3GB help? How about
> caching the report?
> Thanks,
> Brian
>
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> What about exporting to other formats like PDF or MHTML. Is it very slow
>> too?
>> How complicated are these reports and how much data do they contain?
>> Sometimes the reason of slow exporting to Excel is complex structure of
>> reports (a lot of cells, nested objects, etc.).
>> If reports contain a lot of hidden rows all of them are saving to Excel
>> file
>> (even if they are unseen during exporting).
>> Maybe this is the reason.
>>
>> Regards,
>> Radoslaw Lebkowski
>>
>> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
>> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
>> > We have users experiencing very slow to export SQL Reporting Service
>> > 2005
>> > rendered data to EXCEL. Is there any way to improve the performance?
>> >
>> > Brian
>>|||Exporting to CSV helps and improve the export time. Let's hope Microsoft
will improve the export process to EXCEL in the future.
Thanks,
Brian
"Bruce L-C [MVP]" wrote:
> If it is much data at all then what you should do is have your users export
> via CSV. It will still open it up in Excel. One other point, however. The
> default CSV format is Unicode which Excel doesn't know how to handle (at
> least Excel 2003 and earlier can't handle, I don't know about 2007). In RS
> 2005 you can change a configuration setting that causing CSV exports to be
> in ASCII. CSV and HTML rendering is about the same performance wise.
> As a test just try it. Excel will (I believe) put all the data in a single
> column and then you have to use the menu in Excel to split the data out into
> multiple columns.
> I am gone next week to the MVP Summit in Seattle. All the newsgroups will
> see very little MVP involvement for the next week.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> > Thank you for the quick response.
> >
> > The SQL Reporting 2005 report is a simple report which has no complex
> > structure in it. It is a single line type of report. However, it has the
> > company logo image on the title of the report. The report rendered within
> > one minute with 123 pages. When I export to EXCEL, it was ten times
> > slower
> > then the web page rendered.
> >
> > Does export require SQL to query the data again or it gathered the
> > rendered
> > data to EXCEL?
> >
> > Would increase SQL memory from dynamic to static with 3GB help? How about
> > caching the report?
> >
> > Thanks,
> > Brian
> >
> >
> >
> > "Radoslaw Lebkowski" wrote:
> >
> >> Hello,
> >> What about exporting to other formats like PDF or MHTML. Is it very slow
> >> too?
> >> How complicated are these reports and how much data do they contain?
> >> Sometimes the reason of slow exporting to Excel is complex structure of
> >> reports (a lot of cells, nested objects, etc.).
> >> If reports contain a lot of hidden rows all of them are saving to Excel
> >> file
> >> (even if they are unseen during exporting).
> >> Maybe this is the reason.
> >>
> >>
> >> Regards,
> >> Radoslaw Lebkowski
> >>
> >>
> >>
> >> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> >> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> >> > We have users experiencing very slow to export SQL Reporting Service
> >> > 2005
> >> > rendered data to EXCEL. Is there any way to improve the performance?
> >> >
> >> > Brian
> >>
> >>
> >>
>
>