SQL Server DTS Optimization Tips


Here are fourteen little known tips to help ensure you are using SQL Server Data Transformation Services (DTS) in the most efficient manner possible.

1. Avoid using DTS if it is possible to use a 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.

2. Try to use a 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.

3. 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.

4. 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.

5. If you cannot avoid using ActiveX scripts in your data pump
operations, try to use VBScript instead of JScript or PerlScript.

Microsoft recommends using VBScript over JScript or PerlScript as 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.

6. 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 fewer than 20 columns, use columns name
to refer to columns in ActiveX transformations to make the script
easier to read.

7. 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 the Transform Data task to use many-to-many column
mappings in SQL Server 2000 DTS Designer, you can do the following:

  1. Choose the appropriate Transform Data task in the DTS Designer.
  2. Right-click this task and select Properties.
  3. On the Transformations tab, click the ‘Select All’ button.
  4. Click the ‘Delete All’ button.
  5. Click the ‘New’ button and choose ‘Copy Column’ in the Create New
    Transformation window.
  6. Specify the transformation options and click the ‘OK’ button.

8. 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 the appropriate package.
  4. Right-click the 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 positions, and the default setting is at the second setting).

9. 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 the 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 positions, and the default setting is at the third setting).

10. 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 the native OLE DB provider is faster than the ODBC provider, you
should use the OLE DB provider whenever possible.

11. Try to avoid DTS lookups.

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

12. 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 the 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.

13. 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_LOCALMACHINESOFTWAREMicrosoftRepositoryEngineODBCQueryTimeout

The ODBC query time-out values are measured in seconds.

14. When using the SQL Server 2000 Transform Data task, try to use
‘Copy Column’ method to transfer data.

Using the Transform Data task is the fastest way to transfer data and thus should be used whenever possible.


»


See All Articles by Columnist
Alexander Chigrik

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles