sql server dts: Hi ,
I Want to move some huge data from a SourceTable to DestinationTable.
i had two ways to do it.
i)execute a simple query like.

insert into Destination table
select * from SourceTable.

using ado.net from a .net client application.

ii)Use DTS for the same.
======================================================================

1)I was wondering will there be any performance difference between the
two.

i would think executing query using ado.net will be equally fast as
DTS
execpt for the fact that ado.net approach will have to make one extra
call to
sql server and then query also needs to be compiled but this should
not take much time.Could some one confirm this.

2)apart from this issue, why is DTS genrally considered faster than
doing bulk
insertions using a client application(for example ADo.net & sql server
openxml function) assuming there will not be many round trips between

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/default.asp?url=/library/en-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

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.

Hello Sidd,

The logging totally depends on the DB Recovery Model. For Full Recovery
Model, both the DTS and query does
row level logging while in Bulk_logged Model its just the modified extents
which gets logged. Performance wise there is not difference
as long as there are No Transformations applied on the source data before
transfering over to the destination.

As far as your question 2) in the original post, I am not sure about what
scenario you talking about...Do you have the Data in XML file to be loaded
into the
SQL Table ? in that case the fastest way could be to use XMLBulkLoad
component directly in you .NET Client App to load large amount of data.
OPENXML could become a peformance bottleneck since its a memory intensive
operation which does in-memory shredding of documents.

In DTS, you might be able to use the ActiveX VB scripts that use
XMLBulkLoad Objects to pull the XML data into SQL Tables. However, if you
need to control
this thru your App, i would suggest to use the XMLBulkLoad component
directly in your App.

Does that answer your question ?

Thanks for using MSDN Newsgroup.

Vikrant Dalwale

Microsoft SQL Server Support Professional


This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.

--------------------
| Thread-Topic: DTS vs Query performance
| thread-index: AcQZEvJZgkciXf8VQZ2XsZMqp76sbg==
| X-Tomcat-NG: microsoft.public.sqlserver.dts
| From: "=?Utf-8?B?c2lkZA==?=" <anonymous@discussions.microsoft.com>
| References: <af96a2d7.0403310644.1845bf1c@posting.google.com>
<SrycRiFGEHA.612@cpmsftngxa06.phx.gbl>
| Subject: RE: DTS vs Query performance
| Date: Fri, 2 Apr 2004 16:31:03 -0800
| Lines: 5
| Message-ID: <83B39D23-E4D9-4742-AEC6-C1A5E68B66B5@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.dts
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:46308
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| thanks for the reply
is this correct that DTS does page level logging but the query would do
logging for each record
so query would be slow?
thanks
sidd
|