|
|
You're in the
sql server data warehouse
group:BULK INSERT performance
sql server data warehouse:
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/27/performance-impact-the-most-optimal-insert-script-can-t-beat-bulkcopy.aspx [quoted text, click to view] "Scott Nichol" <snicholnews@scottnichol.com> wrote in message news:uDASsWIHIHA.1548@TK2MSFTNGP05.phx.gbl... > Question/sanity check about BULK INSERT performance. > > I load a 300,000 row text file into my largest table every day. The file > loads into an empty table with the same structure and defaults but no > indexes in about 10 seconds, for a rate of 30,000 rows/sec. The same file > takes more like 3000 seconds to load into the "real" table, which has a > clustered index, one non-clustered index and 547,640,055 rows of data (as > of today). Is the discrepency in rate (30,000 rows/sec vs. 100 rows/sec) > reasonable? Is it because the indexes on the table are so deep that > updating them requires lots of page-at-a-time I/O? > > Some useful info: > > 1. The file being loaded is sorted in clustered index order > 2. The BULK INSERT has the ORDER clause > 3. The TABLOCK option on BULK INSERT makes no difference in performance > (lock is quickly escalated to table when the option is not specified) > 4. Database recovery mode is Bulk-Logged > 5. Doing row-by-row INSERTs takes 5700 seconds or so, i.e. BULK INSERT is > not even twice as fast > 6. SQL Server 2000 SP3 on Windows 2003 > 7. SQL Server uses approximately 2 GB RAM (max for our OS + SS versions) > 8. Transaction log is on RAID 1, data on 12-spindle RAID 10, non-clustered > index on 6-spindle RAID 10. All drives SCSI/320, 15k > 9. Perfmon does not indicate any resource being pinned (or even > challenged) > > -- > Scott Nichol >
Tom
[quoted text, click to view] > drop indexes. The OP says that the system is SQL 2000 SP3a. Correct, just missed it. [quoted text, click to view] > Looking at the fact that the data file is sorted in clustered index order > and there is one nonclustered index, I'd be inclined to drop the > nonclustered index, do the bulk insert and then recreate the nonclustered > index. Agreed as well [quoted text, click to view] "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%238rvAkIHIHA.1548@TK2MSFTNGP05.phx.gbl... > That was an excellent blog post but it was for SQL 2005. In SQL 2005, > improvements were made in bulk copy performance, alleviating the need to > drop indexes. The OP says that the system is SQL 2000 SP3a. > > Looking at the fact that the data file is sorted in clustered index order > and there is one nonclustered index, I'd be inclined to drop the > nonclustered index, do the bulk insert and then recreate the nonclustered > index. > > I'm also curious as to whether or not the data to be inserted are spread > throughout the table or are they added to the "end" of the table, i.e. are > the clustered index keys to be inserted greater than all existing > clustered > index keys. If the data are spread throughout the table, I'd want to > reduce > the fill factor. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Uri Dimant" <urid@iscar.co.il> wrote in message > news:u6AXXaIHIHA.700@TK2MSFTNGP05.phx.gbl... > Scott > http://sqlblog.com/blogs/linchi_shea/archive/2007/08/27/performance-impact-the-most-optimal-insert-script-can-t-beat-bulkcopy.aspx > > > > "Scott Nichol" <snicholnews@scottnichol.com> wrote in message > news:uDASsWIHIHA.1548@TK2MSFTNGP05.phx.gbl... >> Question/sanity check about BULK INSERT performance. >> >> I load a 300,000 row text file into my largest table every day. The file >> loads into an empty table with the same structure and defaults but no >> indexes in about 10 seconds, for a rate of 30,000 rows/sec. The same >> file >> takes more like 3000 seconds to load into the "real" table, which has a >> clustered index, one non-clustered index and 547,640,055 rows of data (as >> of today). Is the discrepency in rate (30,000 rows/sec vs. 100 rows/sec) >> reasonable? Is it because the indexes on the table are so deep that >> updating them requires lots of page-at-a-time I/O? >> >> Some useful info: >> >> 1. The file being loaded is sorted in clustered index order >> 2. The BULK INSERT has the ORDER clause >> 3. The TABLOCK option on BULK INSERT makes no difference in performance >> (lock is quickly escalated to table when the option is not specified) >> 4. Database recovery mode is Bulk-Logged >> 5. Doing row-by-row INSERTs takes 5700 seconds or so, i.e. BULK INSERT is >> not even twice as fast >> 6. SQL Server 2000 SP3 on Windows 2003 >> 7. SQL Server uses approximately 2 GB RAM (max for our OS + SS versions) >> 8. Transaction log is on RAID 1, data on 12-spindle RAID 10, >> non-clustered >> index on 6-spindle RAID 10. All drives SCSI/320, 15k >> 9. Perfmon does not indicate any resource being pinned (or even >> challenged) >> >> -- >> Scott Nichol >> > >
Question/sanity check about BULK INSERT performance.
I load a 300,000 row text file into my largest table every day. The file loads into an empty table with the same structure and defaults but no indexes in about 10 seconds, for a rate of 30,000 rows/sec. The same file takes more like 3000 seconds to load into the "real" table, which has a clustered index, one non-clustered index and 547,640,055 rows of data (as of today). Is the discrepency in rate (30,000 rows/sec vs. 100 rows/sec) reasonable? Is it because the indexes on the table are so deep that updating them requires lots of page-at-a-time I/O? Some useful info: 1. The file being loaded is sorted in clustered index order 2. The BULK INSERT has the ORDER clause 3. The TABLOCK option on BULK INSERT makes no difference in performance (lock is quickly escalated to table when the option is not specified) 4. Database recovery mode is Bulk-Logged 5. Doing row-by-row INSERTs takes 5700 seconds or so, i.e. BULK INSERT is not even twice as fast 6. SQL Server 2000 SP3 on Windows 2003 7. SQL Server uses approximately 2 GB RAM (max for our OS + SS versions) 8. Transaction log is on RAID 1, data on 12-spindle RAID 10, non-clustered index on 6-spindle RAID 10. All drives SCSI/320, 15k 9. Perfmon does not indicate any resource being pinned (or even challenged) -- Scott Nichol
That was an excellent blog post but it was for SQL 2005. In SQL 2005,
improvements were made in bulk copy performance, alleviating the need to drop indexes. The OP says that the system is SQL 2000 SP3a. Looking at the fact that the data file is sorted in clustered index order and there is one nonclustered index, I'd be inclined to drop the nonclustered index, do the bulk insert and then recreate the nonclustered index. I'm also curious as to whether or not the data to be inserted are spread throughout the table or are they added to the "end" of the table, i.e. are the clustered index keys to be inserted greater than all existing clustered index keys. If the data are spread throughout the table, I'd want to reduce the fill factor. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau [quoted text, click to view] "Uri Dimant" <urid@iscar.co.il> wrote in message Scottnews:u6AXXaIHIHA.700@TK2MSFTNGP05.phx.gbl... http://sqlblog.com/blogs/linchi_shea/archive/2007/08/27/performance-impact-the-most-optimal-insert-script-can-t-beat-bulkcopy.aspx [quoted text, click to view] "Scott Nichol" <snicholnews@scottnichol.com> wrote in message news:uDASsWIHIHA.1548@TK2MSFTNGP05.phx.gbl... > Question/sanity check about BULK INSERT performance. > > I load a 300,000 row text file into my largest table every day. The file > loads into an empty table with the same structure and defaults but no > indexes in about 10 seconds, for a rate of 30,000 rows/sec. The same file > takes more like 3000 seconds to load into the "real" table, which has a > clustered index, one non-clustered index and 547,640,055 rows of data (as > of today). Is the discrepency in rate (30,000 rows/sec vs. 100 rows/sec) > reasonable? Is it because the indexes on the table are so deep that > updating them requires lots of page-at-a-time I/O? > > Some useful info: > > 1. The file being loaded is sorted in clustered index order > 2. The BULK INSERT has the ORDER clause > 3. The TABLOCK option on BULK INSERT makes no difference in performance > (lock is quickly escalated to table when the option is not specified) > 4. Database recovery mode is Bulk-Logged > 5. Doing row-by-row INSERTs takes 5700 seconds or so, i.e. BULK INSERT is > not even twice as fast > 6. SQL Server 2000 SP3 on Windows 2003 > 7. SQL Server uses approximately 2 GB RAM (max for our OS + SS versions) > 8. Transaction log is on RAID 1, data on 12-spindle RAID 10, non-clustered > index on 6-spindle RAID 10. All drives SCSI/320, 15k > 9. Perfmon does not indicate any resource being pinned (or even > challenged) > > -- > Scott Nichol >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |