exporting large amounts of records (50K rows) to excel - painfully slow

We're trying to do an export to excel of a large amount of data.
When we do 100 even 4000 rows it happens in a decent amount of time.
However, when we try to export say 35,000 rows it never finishes and
appears to hang after a long amount of time (15-20 minutes).
Does anyone have any tips for optimizing or troubleshooting this issue.
We're using SQL Server 2000 and Reporting Services 2000.
Thanks,
JasonI use Access Data Projects for real enterprise level reporting; all the
time-- and I push out this many records without a problem.
Maybe you should consider having a better strategy; and choosing real
tools-- like Access Data Projects... instead of SSRS; where version
2005 is STILL considered a beta.
-Aaron
jason.harris@.gmail.com wrote:
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason|||RS 2005 is faster but the following technique will work. You need to export
in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
handle. In RS 2005 you can configure RS to export as ASCII. What I do is
have a link they click on and use the following URL to export in CSV. Order
of magnitudes faster.
="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
here" &casesensitiveparamname=" & Parameters!Paramname.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
When you play with this you might decide to design a report just for
exporting.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jason.harris@.gmail.com> wrote in message
news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> RS 2005 is faster but the following technique will work. You need to
> export in CSV. However, with RS 2000 it exports it as Unicode which
> Excel can't handle. In RS 2005 you can configure RS to export as
> ASCII. What I do is have a link they click on and use the following
> URL to export in CSV. Order of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
It's also useful to remember that RS is a tool to render usable reports to
view in a browser. It also has the option to export those reports. It was
never designed to be used as a data extractor|||Bruce
I have a similar problem but how do I put this URL into the RS Folder.
Thanks
Karen
Bruce L-C [MVP] wrote:
> RS 2005 is faster but the following technique will work. You need to export
> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
> have a link they click on and use the following URL to export in CSV. Order
> of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> <jason.harris@.gmail.com> wrote in message
> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> > We're trying to do an export to excel of a large amount of data.
> >
> > When we do 100 even 4000 rows it happens in a decent amount of time.
> >
> > However, when we try to export say 35,000 rows it never finishes and
> > appears to hang after a long amount of time (15-20 minutes).
> >
> >
> > Does anyone have any tips for optimizing or troubleshooting this issue.
> >
> > We're using SQL Server 2000 and Reporting Services 2000.
> >
> > Thanks,
> > Jason
> >|||it's alst important to note that since it's EXCEL we're talking about;
you're gonna have a half dozen ninja spreadsheet FAGS that try to build
ETL tools out of Excel.
In the real world; people use Excel as a word processor and a ETL tool;
just because the idiots dont know any other tools.
is it their fault? no it is the fault of the managers and teachers that
make Excel an expectation.
I would just reccomend shooting anyone that uses Excel for anything.
and then uninstalling Excel from every machine in the house.
Asher_N wrote:
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> > RS 2005 is faster but the following technique will work. You need to
> > export in CSV. However, with RS 2000 it exports it as Unicode which
> > Excel can't handle. In RS 2005 you can configure RS to export as
> > ASCII. What I do is have a link they click on and use the following
> > URL to export in CSV. Order of magnitudes faster.
> >
> > ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> > report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> > "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >
> > When you play with this you might decide to design a report just for
> > exporting.
> >
> >
> It's also useful to remember that RS is a tool to render usable reports to
> view in a browser. It also has the option to export those reports. It was
> never designed to be used as a data extractor|||This is for using the Jump To URL. If you wanted a report that just
consisted of this then you would have a report with textboxes. Properties of
the textbox, navigation, jump to URL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KarenM" <karenmiddleol@.yahoo.com> wrote in message
news:1160044319.003258.210730@.b28g2000cwb.googlegroups.com...
> Bruce
> I have a similar problem but how do I put this URL into the RS Folder.
> Thanks
> Karen
> Bruce L-C [MVP] wrote:
>> RS 2005 is faster but the following technique will work. You need to
>> export
>> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
>> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
>> have a link they click on and use the following URL to export in CSV.
>> Order
>> of magnitudes faster.
>> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
>> report
>> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> When you play with this you might decide to design a report just for
>> exporting.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <jason.harris@.gmail.com> wrote in message
>> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
>> > We're trying to do an export to excel of a large amount of data.
>> >
>> > When we do 100 even 4000 rows it happens in a decent amount of time.
>> >
>> > However, when we try to export say 35,000 rows it never finishes and
>> > appears to hang after a long amount of time (15-20 minutes).
>> >
>> >
>> > Does anyone have any tips for optimizing or troubleshooting this issue.
>> >
>> > We're using SQL Server 2000 and Reporting Services 2000.
>> >
>> > Thanks,
>> > Jason
>> >
>

export/data dump to flat file is too slow

I am trying to export a table with ~ 10 Million rows to a flat file and it is taking for ever with SQL2005 export functionality. I have tried creating an SSIS package with a flat-file destination and the results are the same. In each case it does the operation in chunks of about 9900+ rows, and each chunk takes ~1-2 minutes which sounds unreasonable.

I tried bcp, and it fails after a few thousand rows. I tried moving the data to SQL2000 first then to flat file from SQL2K, but the move from SQL2005->SQL2000 was going at the same rate as above.

So, the bottleneck seems to be data going out of SQL2005 no matter what the destination is. I'm wondering if there is some setting that Iam missing that would make this run in a reasonable amount of time?

Never mind. I was outputting it to a share, and that is the reason. When I modified it to do a bcp on the local drive on that server, it was flying like it is supposed to. I'll deal with copying it across the network later. Thanks.

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
> >>
> >>
> >>
>
>