Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Question Unanswered: why is DTS faster than a query to load a remote table?

    Hello

    DTS is notoriously faster than making the following statement:

    insert into synonym_MyRemoteTable
    select * from myLocalTable

    Why is it so?

    Thanks a lot.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    The main reason is because of the transaction log.

    DTS is using a bulk insert, which is logged as a single transaction (or perhaps a few if you specify a batchsize)

    the insert-select is different. each row inserted is logged separately, which is considerably more overhead.

    I would go for a third method when doing a bulk load: bcp.exe. It's as fast as DTS but you don't need to mess with a nasty GUI.

    regulars: please correct me if I'm wrong here.

  3. #3
    Join Date
    Mar 2003
    Posts
    144
    Thanks for your reply.

    It makes me wonder: is there a way to tell the server "the following SQL statement is to be treated as a single record in the transaction log"?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not for a remote SQL Server, at least as far as I know. As an alternative to bcp, you could look into the BULK INSERT command, but that requires server level permissions. I really hope that Microsoft exposes some sort of bulk load/unload utility to ADO or ADO.NET, so people don't have to drop to a command line and pray they catch any errors.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by MCrowley
    I really hope that Microsoft exposes some sort of bulk load/unload utility to ADO or ADO.NET
    You might like to check out the SqlBulkCopy class, in the System.Data.SqlClient namespace.
    Last edited by jezemine; 04-24-07 at 15:44.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by MCrowley
    Not for a remote SQL Server, at least as far as I know. As an alternative to bcp, you could look into the BULK INSERT command, but that requires server level permissions. I really hope that Microsoft exposes some sort of bulk load/unload utility to ADO or ADO.NET, so people don't have to drop to a command line and pray they catch any errors.
    Eh, they did...

    Have a look at system.data.sqlclient.sqlbulkcopy

    Edit: That's what I get for not finishing the thread.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Heh. Guess I have been outed as an admin, and not a programmer, eh? ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •