Hi Sidd,

Based on my experience, there is not too much performance difference when
using the DTS and the simple query. However, the DTS will use 'bulk insert'
, when the recovery mode is simple or Bulk-Logged, it will have
high-performance bulk copy operations when the amout of data is very big.

Please refer the following article for details information

Enhancing Performance of DTS Packages
http://msdn.microsoft.com/library/de...us/dtssql/dts_
usage_7703.asp

Thanks.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

thanks for the repl
is this correct that DTS does page level logging but the query would do logging for each recor
so query would be slow
thank
sidd

Hi Sidd,

As the DTS will use bulk copy. It will benifit from the bulk operations.
Here is how the bulk operations are logged when database is bulk_logged
recovery mode. The BULK_LOGGED recovery model allows you to completely
restore a database in case of media failure and also gives you the best
performance and least log space usage for certain bulk operations. These
bulk operations include BULK INSERT, bcp, CREATE INDEX, SELECT INTO,
WRITETEXT, and UPDATETEXT. In FULL recovery mode, these operations are
fully logged, but in BULK_LOGGED recovery mode, they are only minimally
logged.

When you execute one of these bulk operations, SQL Server logs only the
fact that the operation occurred. However, the operation is fully
recoverable because SQL Server keeps track of what extents were actually
modified by the bulk operation. Every data file in a SQL Server 2000
database now has an additional allocation page called a BCM page, which is
managed much like the GAM and SGAM pages that I discussed earlier in the
chapter. Each bit on a BCM page represents an extent, and if the bit is 1
it means that this extent has been changed by a minimally logged bulk
operation since the last full database backup. A BCM page is located at the
8th page of every data file, and every 511,230 pages thereafter. All the
bits on a BCM page are reset to 0 every time a full database backup or a
log backup occurs.

Because of the ability to minimally log bulk operations, the operations
themselves can be carried out much faster than in FULL recovery mode. There
is a little overhead to setting the bits in the appropriate BCM page, but
compared to the cost of logging each individual change to a data or index
row, the cost of flipping bits is almost negligible.

You could also refer to this part in the SQL Server Books Online:
SQL Server Architecture Over view->Database->Architecture->Physical
Database Architecture->Space Allocation and Reuse->Tracking Modified Extens.

Hope this helps.

Thanks.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Hi Sidd,

I am reviewing you post and I want to know if you still have question about
it. For any question, please feel free to post message here and I am gald
to help.

Thanks.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.