Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry Unanswered: I need to speed this process up

    I need to speed this process up.

    Insert into dbo.temp_table
    (record_number, etc....)
    select record_number, etc....
    from
    dbo.incoming_temp
    set Indentity _insert temp_table on

    It's about 10 million records and usally runs 2-3 hours. Any ideas???????????

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Break it up int to 10 separate transaction?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Speed up

    You mean like 2 million record at a time.
    Select yadA, YADA YADA,
    YADA, YADA, YADO
    FROM YADA
    WHERE RECORD_id < 2000000

    SOMETHING LIKE THAT

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Something like?

    Code:
    DECLARE @x int, @y int
    
    SELECT @x=0, @y = SELECT COUNT(*) FROM myTable99
    
    WHILE @X < @y
      BEGIN
        BEGIN TRAN
    	INSERT...
        COMMIT TRAN
        SELECT @x = @x + 2000000
      END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Smile Speed up

    thanks, this gave me some insight on how to structure other thing with my group,.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Let us know if it helps...

    Also Don't forget the last set of records...

    You may fall out of the loop before everything is done...

    Do

    WHERE Record_ID > @x
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    Look for nonlogged operations in BOL. Try "select into" to avoid logging.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Breaking the insert will not speed up the completion of the entire batch. Either SELECT * INTO... or bulk copy utility (BCP/BULK INSERT)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    The fastest possible speed you can get is:

    1. Use BCP.
    2. Set your recovery mode to Bulk-logged or simple while you do the operation.

    Also, how long is your SELECT taking? If the length is in the select, these ideas aren't going to do you a lot of good.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Bulk-logged will defeat the purpose of using BCP/BULK INSERT because it will log those operations, as opposed to Simple which will not.

    Even if SELECT is slow, non-logged operations would still buy you some advantage over INSERT, which is logged (unless you set recovery mode to Bulk-Logged, in which case it would be a wash )
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    The logging on bcp, bulk insert, INSERT INTO, CREATE INDEX, text and image manipulations are kept to a minimum when using bulk-logged and it performs faster than full recovery mode, while still providing a level of protection to your other transactions. It is actually quite a bit faster on really large bulk operations.

    I agree with the second part, except for the part in ().
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Really?

    Quote Originally Posted by rdjabarov
    Breaking the insert will not speed up the completion of the entire batch. Either SELECT * INTO... or bulk copy utility (BCP/BULK INSERT)
    I mean bcp is the way to go...but if you break up the work into chunks you will notice a difference...

    What'dya think the log will look like?

    What'dya think it'd be like if he had to roll the whole thing back...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Bulk-logged "permits high-performance bulk copy operations", but does alow for other DDL/DML to be logged. If data recovery is important while performing the process at hand, then Bulk-logged is the one to use. Else, - Simple will be just fine.

    Brett, I don't think you can roll back a bulk copy operation. Do you know the way? On either BCP or BULK INSERT you can specify the number of records to be viewed as transaction. This way there is not need to break anything up.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Someone tell me why BCP is faster than a non-logged insert. Is it just because there is minimal data checking and verification? It doesn't make sense to me that spitting everything out to disk and then reading it in again would be particularly fast.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Sep 2003
    Posts
    522
    derrickleggett is right, and using bulk copy utility is not the same as selecting or inserting. it is a much more efficient operation for large volumes of data.

Posting Permissions

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