INSERT INTO ….SELECT performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

INSERT INTO ….SELECT performance

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? –
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.
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 Thanks ___________________________________
Need an IT job? –
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.
Apologies, you are right. ___________________________________
Need an IT job? –
No apologies necessary – just post the correct details so we can take a look.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
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? –
I am no expert on LOGBUFFER. What is the size and growth factor for the log file(s) belonging to your database?