slow export to CSV slow

Question

question

Bam asked Oct 31, '14

slow export to CSV slow

Hey guys I have a query that returns over 100000 rows of data. The query completes in 7 seconds. But when I use the export wizard to create a ssis job to export the query to CSV it takes hours. When I watch the progress it's slow in returning rows. The CSV file is being created on local disk. Can I please get advice on how to speed this up? Thanks.

csv create data-export

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2014-10-31T09:49:13Z

@SQLShark answered Oct 31, '14

What type of data is being exported? Do you have lots of VARCHAR(MAX) or large VARCHAR columns? If you have a varchar(6000) with only 15 chars SSIS will see this as 6000 chars when loading data in to a buffer. There is a good tuning guide for SSIS from Koen here, which might help: [ http://www.mssqltips.com/sqlservertip/3217/improve-ssis-data-flow-buffer-performance/][1] SSIS can only export data as quickly as SQL Server can give it to SSIS, what does your execution plan look like? [1]: http://www.mssqltips.com/sqlservertip/3217/improve-ssis-data-flow-buffer-performance/

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bam commented · Oct 31, 2014 at 10:26 AM

Thanks I'll take a look at the column widths and buffer sizes. It makes sense as this is a very wide query returning many columns.

1 ·

VishalhSingh commented · Oct 31, 2014 at 11:27 AM

"SSIS can only export data as quickly as SQL Server can give it to SSIS" - I have a different opinion here as sometimes (an in majority of cases) SSIS ask SQL Severer to return bulk data but SSIS itself unable to process (export/write etc) these amount data and this is where the entire time goes. check any blocking during the SSIS execution and also check your SQL wait types for any PREEMPTIVE category.

0 ·

question