November 15, 2012 at 9:29 am
Greetings,
I am having performance issues migrating a particular DTS package to SSIS. A particular step is taking a LOT longer time to complete in SSIS than it does in the DTS version. The step uses a Data Pump Task in DTS and Data Flow Task in SSIS to copy records from a SQL 2000 database on one server to a SQL 2005 database on another. The SQL 2005 server is where both the DTS and SSIS packages live and run from.
Specifics:
Number of records: 300K +
Transfer Time: DTS - 11 minutes for all 300K+, SSIS - 9 minutes for 'Top 100'.
Source: a view on a SQL Server 2000 database - DTS Data Pump just selects the view from the Table/View list, SSIS Data Flow Source is a SQL Command - 'Select <column names> From <view name>' statement.
Connection: DTS - Microsoft OLE DB Provider for SQL Server, SSIS - Native OLE DB\SQL Native Client, both are set to SQL Authentication with the same login name and password.
What am I missing?
Should I change the connection manager in SSIS to use something other than SQL Native Client?
Should I use an ADO or a DSN connection?
Any and all help is appreciated.
November 15, 2012 at 10:23 am
Craigmeister (11/15/2012)
Greetings,I am having performance issues migrating a particular DTS package to SSIS. A particular step is taking a LOT longer time to complete in SSIS than it does in the DTS version. The step uses a Data Pump Task in DTS and Data Flow Task in SSIS to copy records from a SQL 2000 database on one server to a SQL 2005 database on another. The SQL 2005 server is where both the DTS and SSIS packages live and run from.
Specifics:
Number of records: 300K +
Transfer Time: DTS - 11 minutes for all 300K+, SSIS - 9 minutes for 'Top 100'.
Source: a view on a SQL Server 2000 database - DTS Data Pump just selects the view from the Table/View list, SSIS Data Flow Source is a SQL Command - 'Select <column names> From <view name>' statement.
Connection: DTS - Microsoft OLE DB Provider for SQL Server, SSIS - Native OLE DB\SQL Native Client, both are set to SQL Authentication with the same login name and password.
What am I missing?
Should I change the connection manager in SSIS to use something other than SQL Native Client?
Should I use an ADO or a DSN connection?
Any and all help is appreciated.
SQL Native Client should be fine.
Is the data flow just doing a straight INSERT, or are updates (or some other processing) involved too?
Is Data Access Mode set to 'Table or View - Fast Load' on the OLE DB Destination?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 15, 2012 at 10:35 am
Craigmeister (11/15/2012)
Greetings,I am having performance issues migrating a particular DTS package to SSIS. A particular step is taking a LOT longer time to complete in SSIS than it does in the DTS version. The step uses a Data Pump Task in DTS and Data Flow Task in SSIS to copy records from a SQL 2000 database on one server to a SQL 2005 database on another. The SQL 2005 server is where both the DTS and SSIS packages live and run from.
Specifics:
Number of records: 300K +
Transfer Time: DTS - 11 minutes for all 300K+, SSIS - 9 minutes for 'Top 100'.
Source: a view on a SQL Server 2000 database - DTS Data Pump just selects the view from the Table/View list, SSIS Data Flow Source is a SQL Command - 'Select <column names> From <view name>' statement.
Connection: DTS - Microsoft OLE DB Provider for SQL Server, SSIS - Native OLE DB\SQL Native Client, both are set to SQL Authentication with the same login name and password.
What am I missing?
Should I change the connection manager in SSIS to use something other than SQL Native Client?
Should I use an ADO or a DSN connection?
Any and all help is appreciated.
I suspect the problem is that the entire view must materialize before you can grab the "TOP 100". How long does it take for the DTS job to grab the "TOP 100"?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2012 at 10:53 am
To answer your questions:
Phil:
Is the data flow just doing a straight INSERT, or are updates (or some other processing) involved too?
Is Data Access Mode set to 'Table or View - Fast Load' on the OLE DB Destination?
Response:
It is a straight insert. No other processing is involved and the Data Access Mode in SSIS Destination is 'Table or View - Fast Load'
Jeff:
I suspect the problem is that the entire view must materialize before you can grab the "TOP 100". How long does it take for the DTS job to grab the "TOP 100"?
Response:
Also a good question. I made an assumption and extrapolated the 9 mins for 100 out to the full 300K+ and freaked out. However, the test that I will have to perform is to pull the full 300K+ in the SSIS task and go from there. I have to perform these tests after hours, before the production DTS job runs. The nature of the DTS package is such that after it runs, the data being copied from the server changes. It may be that the actual time differential running at a similar time of day is small enough to not keep us from completing the migration to SSIS.
Thanks for your help.
November 15, 2012 at 12:31 pm
Craigmeister (11/15/2012)
To answer your questions:Phil:
Is the data flow just doing a straight INSERT, or are updates (or some other processing) involved too?
Is Data Access Mode set to 'Table or View - Fast Load' on the OLE DB Destination?
Response:
It is a straight insert. No other processing is involved and the Data Access Mode in SSIS Destination is 'Table or View - Fast Load'
Jeff:
I suspect the problem is that the entire view must materialize before you can grab the "TOP 100". How long does it take for the DTS job to grab the "TOP 100"?
Response:
Also a good question. I made an assumption and extrapolated the 9 mins for 100 out to the full 300K+ and freaked out. However, the test that I will have to perform is to pull the full 300K+ in the SSIS task and go from there. I have to perform these tests after hours, before the production DTS job runs. The nature of the DTS package is such that after it runs, the data being copied from the server changes. It may be that the actual time differential running at a similar time of day is small enough to not keep us from completing the migration to SSIS.
Thanks for your help.
What I believe you'll find is that it will only take a bit more than 9 minutes to pull the full 300k. As a bit of a sidebar, it still shouldn't take that long and I'd probably investigate the view for performance problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply