SSIS Performances on Data Flow

Question

SSIS Performances on Data Flow

donbuz

SSC Eights!

Points: 961
More actions
May 8, 2009 at 10:57 am

#209161

Good Day Everyone,
I am experiencing the following issue running SSIS package in MSSQ2005
I designed a package in order to fully transfer the results from a View from one database to another database, on the same server.
The different tasks at used are the following:
-Drop the table in the target database - Execute SQL Task
[font="Courier New"]Finished, 5:54:07 PM, Elapsed time: 00:00:00.297[/font]
-Re-create the table in the target database - Execute SQL Task
[font="Courier New"]Finished, 5:54:07 PM, Elapsed time: 00:00:00.031[/font]
-Insert all the results from the view - Data Flow Task
oOLE DB Source – configured to retrieve the results of the view
oOLE DB Destination (Target Database)
[font="Courier New"]Finished, 5:57:47 PM, Elapsed time: 00:03:40.968[/font]
When querying the view through a mere SELECT * FROM myView – the server returns the results after an acceptable 7s for less than 10k rows.
These poor performances are even worse when the package is run on our Production environment.
For some reasons, as I indicate previously, the execution time of the Data Flow Task takes forever – and it actually goes “by steps” the first 3.5k go quickly, then it stops until it reaches a next step at 7k rows and so on… I suspect a buffer problem but I do not know how to troubleshoot it.
Would someone have already experienced the same problem and found a way to fine-tune a Data Flow? Any help is appreciated.
Apparently here someone had the same problem at the end of the thread.
Thanks – JP.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

donbuz (5/8/2009)
Good Day Everyone,
I am experiencing the following issue running SSIS package in MSSQ2005
I designed a package in order to fully transfer the results from a View from one database to another database, on the same server.
The different tasks at used are the following:
-Drop the table in the target database - Execute SQL Task
[font="Courier New"]Finished, 5:54:07 PM, Elapsed time: 00:00:00.297[/font]
-Re-create the table in the target database - Execute SQL Task
[font="Courier New"]Finished, 5:54:07 PM, Elapsed time: 00:00:00.031[/font]
-Insert all the results from the view - Data Flow Task
oOLE DB Source – configured to retrieve the results of the view
oOLE DB Destination (Target Database)
[font="Courier New"]Finished, 5:57:47 PM, Elapsed time: 00:03:40.968[/font]
When querying the view through a mere SELECT * FROM myView – the server returns the results after an acceptable 7s for less than 10k rows.
These poor performances are even worse when the package is run on our Production environment.
For some reasons, as I indicate previously, the execution time of the Data Flow Task takes forever – and it actually goes “by steps” the first 3.5k go quickly, then it stops until it reaches a next step at 7k rows and so on… I suspect a buffer problem but I do not know how to troubleshoot it.
Would someone have already experienced the same problem and found a way to fine-tune a Data Flow? Any help is appreciated.
Apparently here someone had the same problem at the end of the thread.
Thanks – JP.

Can you elaborate more on what is the source and target database? Do you use "linked server" feature?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 2

You can change the number of records to buffer and maximum commit size in the ole db destination dataflow component.

Other thing to try is to drop all indexes and foreign keys on the destination table and rebuild them afterwards.

donbuz SSC Eights! Points: 961 More actions · Answer 3

[font="Courier New"]SELECT @@VERSION

--------------------------------

Both source and target databases are running on this server. Inside the target database, the table was created directly from within Visual Studio from the OLE Destination properties windows.

Thanks for your time !

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

donbuz (5/11/2009)
[font="Courier New"]SELECT @@VERSION
--------------------------------
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) [/font]
Both source and target databases are running on this server. Inside the target database, the table was created directly from within Visual Studio from the OLE Destination properties windows.
Thanks for your time !

I'm wondering if your case needs SSIS at all. You don't do any transformations whatsoever. Have you tried transferring the data from source to target database without using SSIS?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

donbuz SSC Eights! Points: 961 More actions · Answer 5

Not at all. Would you recommend to "INSERT INTO FROM (SELECT)" approach?

The concept would in the end to have this ETL component (without the T...like you mentionned) part of a more global solution.

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

donbuz (5/11/2009)
Not at all. Would you recommend to "INSERT INTO FROM (SELECT)" approach?
The concept would in the end to have this ETL component (without the T...like you mentionned) part of a more global solution.

Can you try straight transfer to see if the performance problem exists without SSIS in the equation?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

donbuz SSC Eights! Points: 961 More actions · Answer 7

[font="Courier New"]INSERT INTO [TargetDB].dbo.[tbl_target]

SELECT * FROM [SourceDB].dbo.[vw_source][/font]

The query gave me an execution time of... 11-12s

This is great - Thank you CozyRoc! This is indeed a good solution for me, even though I would really like to better understand the SSIS execution engine and why my Data Flow task is so slow,

Take care, @+ JP