SQL Server Performance Forum – Threads Archive

Fastest DTS method

Hi all, I need to create a DTS package that will on a daily basis transfer +- 150000 rows from around 3 tables holding around 18 million (and growing) rows of data. I’d like to get this as fast as possible so besides coding an insert …. select … statement to transfer the data, does anyone know of some sexy tips to speed this process up besides dropping the indexes on the table receiving the rows? Thanks
Shaun World Domination Through Superior Software

– Create Index on the Source Tables
– Use WITH FASTLOAD Option – This creates less log but you have to take destinantion database backup after operstion.
– Txfr the data in batches.
– Pre-Grow the destination database and log to appropriate size.
– If TempDB is being used, pre-grow database and log file.
– Keep source and destination on different tables. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

How about using BCP or BULK INSERT which are far better than DTS in terms of the performance, I believe you know about BOL for reference. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>