SQL Tips - DTS Optimization Tips

*****

Avoid using DTS if it is possible to use Transact-SQL distributed query such as a SELECT INTO statement to transfer data.
Using Transact-SQL is the fastest method to move data between SQL Server tables, so try to use Transact-SQL statements to move data whenever possible.

*****

Try to use BULK INSERT statement instead of bcp or DTS to load data into SQL Server from the text file.
The BULK INSERT statement is much faster than bcp or the data pump to perform text file import operations, however, the BULK INSERT statement cannot export data from SQL Server to a data file.

*****

Use the bcp utility instead of DTS when you need to export data from the SQL Server table into a text file.
The bcp utility is much faster than DTS, so try to use it whenever possible.

*****

Try to avoid using data pump operations that use ActiveX transformation scripts.
Because data pump operations that use ActiveX transformation scripts can be up to two to four times slower than copy operations, you should avoid using ActiveX transformation scripts, whenever possible.

*****

If you cannot avoid using ActiveX scripts in your data pump operations, try to use VBScript instead of JScript or PerlScript.
Microsoft recommends to use VBScript because scripts written in VBScript run approximately 10 percent faster than scripts written in JScript, which in turn run approximately 10 percent faster than scripts written in PerlScript.

*****

When the transformation contains many columns (more than 20) and the data pump operations use ActiveX transformation scripts, use ordinal values to refer to columns in ActiveX transformations instead of referring to columns by name.
Use this tip only when the transformation contains many columns. If the transformation contains less than 20 columns, use columns name to refer to columns in ActiveX transformations to make the script easier to read.

*****

Try to use many-to-many copy column transformations instead of one-to-one copy column transformations.
The DTS Import/Export Wizard uses many-to-many column mappings. By default, the DTS Designer assigns one-to-one column mappings to transformations in order to improve readability. Because a single many-to-many copy column transformation is faster then many one-to-one copy column transformations, you can manually set your Transform Data task or Data Driven Query task to use many-to-many column mappings. For example, to set Transform Data task to use many-to-many column mappings in SQL Server 2000 DTS Designer, you can do the following:
1. Choose appropriate Transform Data task in the DTS Designer.
2. Right-click this task and select Properties.
3. On the Transformations tab click 'Select All' button.
4. Click 'Delete All' button.
5. Click 'New' button and choose 'Copy Column' in the Create New Transformation window.
6. Specify the transformation options and click 'OK' button.

*****

You can increase the DTS package priority.
To increase the DTS package priority in SQL Server 2000, try the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Data Transformation Services and choose appropriate package.
4. Right-click package and select 'Design Package'.
5. From the top menu of the DTS Designer click Package and select Properties.
6. On the General tab increase the package priority by moving the slider bar to the right one or two steps (the slider bar has three position, and the default setting is at the second setting).

*****

You can increase the Transform Data task or Data Driven Query task priority.
To increase the Transform Data task priority in SQL Server 2000 DTS Designer, you can do the following:
1. Choose appropriate Transform Data task in the DTS Designer.
2. Right-click this task and select Workflow Properties.
3. On the Options tab increase the task priority by moving the slider bar to the right one or two steps (the slider bar has five position, and the default setting is at the third setting).

*****

Try to use 'Microsoft OLE DB Provider for SQL Server' instead of 'Microsoft ODBC Driver for SQL Server' and 'Microsoft OLE DB Provider for Oracle' instead of 'Microsoft ODBC Driver for Oracle'.
Because native OLE DB provider is faster than ODBC provider, you should use OLE DB provider whenever possible.

*****

Try to avoid DTS lookups.
Because DTS lookups slow down performance, try to use the Transact-SQL statements to perform the same functions.

*****

Check the 'Use fast load' advanced option of the Transform Data task.
When you enable this option, the high-speed bulk-copy processing will be used. To enable this option in SQL Server 2000 DTS Designer, you can do the following:
1. Choose appropriate Transform Data task in the DTS Designer.
2. Right-click this task and select Properties.
3. On the Options tab check the 'Use fast load' option.

*****

Increase the ODBC query time-out value to improve query performance on large SQL Server 2000 DTS packages stored in a repository.
Change this registry setting from the default value (10 seconds): HKEY_LOCALMACHINE\SOFTWARE\Microsoft\Repository\Engine\ODBCQueryTimeout The ODBC query time-out values are measured in seconds.

*****

When using SQL Server 2000 Transform Data task, try to use 'Copy Column' method to transfer data.
This is the fastest way to transfer data using Transform Data task, so it should be used whenever possible.