Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2003
    Posts
    61

    Unanswered: Insert into a large table is timing out

    I've a table with 4 columns and has 9.8 million rows.
    The insert into the table is timing out after 10 mins.

    I've run DBCC SHOWCONTIG and got the below result. Do you see any issues with the indexes or fragmentation?

    Thanks in advance for any insight

    ----------------------------------------------------------------------------

    TABLE level scan performed.

    - Pages Scanned................................: 60664

    - Extents Scanned..............................: 7604

    - Extent Switches..............................: 52654

    - Avg. Pages per Extent........................: 8.0

    - Scan Density [Best Count:Actual Count].......: 14.40% [7583:52655]

    - Logical Scan Fragmentation ..................: 84.23%

    - Extent Scan Fragmentation ...................: 3.58%

    - Avg. Bytes Free per Page.....................: 3419.1

    - Avg. Page Density (full).....................: 57.76%

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    I'd be concerned about the Logical Scan Fragmentation value... that is excessively high.

    You also haven't specified how many indexes/triggers exist on that table - remember that for each row inserted it also has to insert into each index and execute each trigger which will slow down your overall insert process.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For a 10 minute insert, I expect there is some kind of blocking going on. Have you had a look at the output of sp_who2 to see what else is running at the same time, and perhaps interfering?

  4. #4
    Join Date
    Dec 2003
    Posts
    61
    Yes, I have executed sp_who2 and there are no other processes that are hitting the database at that instant.

    There is a clustered index on a Auto Incrementing ID column.

  5. #5
    Join Date
    Dec 2003
    Posts
    61
    There are no triggers on the table

  6. #6
    Join Date
    Dec 2003
    Posts
    61
    The insert sql is something like this...
    Insert into TableA
    Select *
    from TableA
    left join TableA_Stage on TableA.Field1 = TableA_Stage.Field1
    where TableA.Field1 is not null

    --Only new rows from Stage table need to be loaded into main table

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Consider doing the INSERT in 3 steps:
    Code:
    TRUNCATE TABLE TempTable
    
    --(*)
    Insert into TempTable
    Select *
    from TableA_Stage
       left join TableA on 
          TableA_Stage.Field1 = TableA.Field1
    where TableA.Field1 is null
    
    Insert into TableA
    Select *
    from TempTable
    (*) I rewrote your original INSERT statement, it seemed flawed to me.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Maybe it is because half of my job is reviewing and rewriting SQL procedures. Maybe it is because I am an anal son of a ..... gun.

    Code:
    Insert into TempTable
    Select TableA_Stage.*
    from TableA_Stage
       left join TableA on 
          TableA_Stage.Field1 = TableA.Field1
    where TableA.Field1 is null
    Sorry. That query has been giving me a twitch every time it comes up. ;-). (and yes, I know the original poster never claimed it was a working copy)

  9. #9
    Join Date
    Dec 2003
    Posts
    61
    I am also doing a distinct to avoid any duplicates. Would that make any difference or imopact the performance ?

    Insert into TempTable
    Select Distinct TableA_Stage.*
    from TableA_Stage
    left join TableA on
    TableA_Stage.Field1 = TableA.Field1
    where TableA.Field1 is null

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Distinct has an effect on how long it takes to select records, since it requires a sort to detect and remove duplicates. What do the statistics on these tables look like?

    Code:
    select object_name(object_id), name, stats_date(object_id, index_id)
    from sys.indexes
    where object_id = object_id('tablename')
    And, also, what does the execution plan look like? Maybe an index has gone missing?

  11. #11
    Join Date
    Dec 2003
    Posts
    61
    The indexes are rebuilt once every week.

    The execution plan says something like...
    Query cost (relative to the bacth): 100%
    Missing Index (Impact 85.4972): Create NonClustered Index [<Name of missing index, sysname,>] on...

  12. #12
    Join Date
    Dec 2003
    Posts
    61
    adding WITh NOLOCK would help in the select query??
    i think its a lock time-out issue

  13. #13
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Looking through the information here: You have some kind of stage table, and a table that you want to update with information from the stage table. My first question: The column (or columns) that you join on, are they guaranteed to be unique? If so, you should consider having a unique index (on this/these column(s)) on both the stage and target table, it could speed up your join drastically. Otherwise, a non-unique index could do the same, but if possible a unique index would be preferred.

    If this is some kind of incremental load, I would suggest adding a timestamp column to your stage table, and create a stored proc for the insert. This should log the Timestamp values read, and only read the rows with greater timestamp. This could very well be done in combination with indexes. I've just done something similar myself, and large updates went from 8 minutes to 3 seconds.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are the indexes that are rebuilt every week used in this process? updating a table (or inserting/deleting) requires index maintenance. If there are 50 indexes on the table, the inserts would take a long time.

  15. #15
    Join Date
    Dec 2003
    Posts
    61
    There is only one clustered index on the auto-incrementing ID column.
    The building of the indexes is not part of this insert process. It is schedule at a different time every week

Posting Permissions

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