Post reply

INSERT performance

  • September 24, 2009 at 8:38 am

    #103829

    Hello All,

    I have been working with a table and trying to research the various ways to insert data into it. It's on SQL Server 2000 and has 38million+ records. The new data from the table is on another SQL Server 2005 database. The tables, basically, need to be in sync, except for records that are marked as archived (tracked in another table by the ID).

    I have tried:

    - A few ways of making this happen by tracking new IDs in one table but not the other

    - Querying into a temp table on the destination server and inserting

    - Using transactional replication, but it slowed down the entire server.

    None of this is really working for me.

    I have not tried dropping indexes during insert because these inserts can happen during production time as well.

    What suggestions would you have for me?

    Thank you,

    Randy

    Randy

  • September 24, 2009 at 8:42 am

    #1058065

    Have you tried BCP?

    -Roy

  • September 24, 2009 at 8:49 am

    #1058071

    I have considered it, but it seems to be the wrong answer for during-production inserts because of having to drop indexes to really see the benefit.

    Randy

  • September 24, 2009 at 9:08 am

    #1058092

    I am not sure why you need to drop the indexes. You can try it without dropping indexes. maybe I am missing something here.

    -Roy

  • September 24, 2009 at 9:15 am

    #1058100

    BCP or BULK INSERT are a couple orders of magnitude faster than getting the data from a linked server; is that how you are getting the data from the 2005 Server now?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 24, 2009 at 9:26 am

    #1058107

    Yes it is. I will give bcp and bulk insert a try. I appreciate your input.

    Thanks!

    Randy

    Randy

  • September 28, 2009 at 3:36 am

    #1058933

    Hi there,

    I had issue similar to bulk inserts and followed one of the method in the following article.

    Check this out and it might shed you some light.

    http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

    thanks, M.

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

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