SQL Server Performance

INSERT INTO ....SELECT performance

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Jack Vamvas, Jun 6, 2007.

  1. Jack Vamvas Member

    Currently, I've got 2 tables . We are dealing with about 400,000 rows, of 6 colums all data types are INT .
    Table1 - no indexes ( as we want as quick as possible BULK INSERT from txt files), there is then a bit of processing once processing has finished , I'm doing a INSERT INTO table1..SELECT col1,min(col2) FROM table2 where <some filters>.

    This is taking quite awhile approx 22 seconds , looking at my wait types , there is quite a lot of LOGBUFFER action. Any tips on this problem?

    But even if I isolate the SELECT part of the equation, it's taking 17 seconds, looking at the Execution Plan it's a Table Scan. Any tips on selecting a Table Scan quicker? Thanks

    Need an IT job? -http://www.ITjobfeed.com

  2. Adriaan New Member

    What indexes do you have on table2, and what do those filter criteria look like? Also, how many rows does Table2 have in total?

    Also a bit weird that you mention inserting into table1 from txt files, but your insert query selects from table2.
  3. Jack Vamvas Member

    Yes, data is coming from text files into Table1 and then transfers into Table 2.
    I've sorted out the SELECT problems , as there were some unnecessary indexes.

    The more important issue, if you can help, is how to deal with persistently high LOGBUFFER


    Need an IT job? -http://www.ITjobfeed.com

  4. Adriaan New Member

    Your example was this:

    INSERT INTO Table1 ...... SELECT ...... FROM Table2

    ... which takes data from Table2, and copies it into Table1. There is no data going into Table2. Please sort out the details.
  5. Jack Vamvas Member

  6. Adriaan New Member

    No apologies necessary - just post the correct details so we can take a look.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  7. Jack Vamvas Member

    I've sorted the problem, thank you. Maybe you could assist with persistent LOGBUFFER size, I cannot find much documentation about what causes this and if there are configuration changes we can make

    Need an IT job? -http://www.ITjobfeed.com

  8. Adriaan New Member

    I am no expert on LOGBUFFER. What is the size and growth factor for the log file(s) belonging to your database?

Share This Page