Post reply

DTS is extreamly slow to transfer data for just 1 small table. Please help!

  • October 23, 2007 at 5:47 am

    #176415

    I'm using DTS to transfer data from an sql server 2000 located in an internal office to another sql server 2000 located in a website hosting center (hostmysite.com). The data all moves very fast and not problems. However, there is one table in the database that takes almost an hour to move. The table has 22 columns and never has over 200 records in it. I can't for the life of me figure out why this one table takes so long. It does have one field in it "Comments" that has a datatype of nvarchar with 2000 character limit. Would that have anything to do with it? Could anyone offer advice on how to speed it up?

    Many thanks and hope someone can offer something for this.

  • October 23, 2007 at 5:59 am

    #744451

    this can happen due to some users try to hit the server / database for reteriving the information. So, at this point the chances for DTS might be slow.

    Else, the network traffic for sending / receving the packets through the different protocols might also cause the same result set.

    Kindly check with your sys.admin person for any issues.

  • October 27, 2007 at 11:18 pm

    #746028

    [font="Courier New"]Check whether any process is running on the db at the time of the DTS is running. The samething has happend to us and we found DBCC is running in background.

    Check whether autoshrink is enabled in the database[/font]

    Regards..Vidhya Sagar
    SQL-Articles

  • October 27, 2007 at 11:31 pm

    #746030

    Check for triggers, as well.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 29, 2007 at 11:39 am

    #746347

    Check to see how much your Destination table is growing during the inserts for the problem table. It's probably growing quite rapidly when you are inserting the data.

  • October 29, 2007 at 1:37 pm

    #746379

    If it never has more than 200 records in it how many adds and deletes are going on? If you are continuously adding and deleteing records you are probably getting hit by table/record locks. Check to see if there is any blocking happening on your processes.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply