question

Bam avatar image
Bam asked

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.
csvcreatedata-export
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
@SQLShark avatar image
@SQLShark answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Like 1 ·
"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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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