Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 916785 - Last Review: April 11, 2006 - Revision: 1.1
The INSERT operation is very slow when you try to insert many rows into a table that contains a full-text index
Bug #: 412873 (SQLBUDT)
Consider the following scenario. In Microsoft SQL Server 2005, you try to insert many rows into an empty table. The empty table contains a full-text index that is built on a column. The index is maintained by using change tracking. In this scenario, you
may notice the following:
- The INSERT operation is very slow.
- The speed at which the rows are inserted severely decreases. This behavior occurs when you increase the number of rows in the operation.
This problem occurs because an incorrect execution plan is
generated for the operation. Because the table is empty, the Clustered
Index Scan execution plan is used instead of the Clustered
Index Seek execution plan. The SQL Server query
optimizer does not consider the increase in the table size during the
execution. Therefore, the
overall performance becomes very slow when many rows are inserted.
To work around this problem, you can use one of the following
methods:
- Use fewer rows in the INSERT operation. You can
separate the rows into more batches.
- Set the Change Tracking property of the
full-text index to Manual. To apply the changes that occur to
the underlying data to the full-text index, you must start or schedule this
process manually.
- Build the full-text index after you complete the INSERT
operation.
Microsoft
has confirmed that this is a problem in the Microsoft products that are listed
in the "Applies to" section.
For more information, see the following topics in SQL Server
2005 Books Online:
- Displaying graphical execution plans (SQL Server Management
Studio)
- Graphical execution plan icons (SQL Server Management
Studio)
- Full-text indexes
- Select Change Tracking (Full-Text Indexing Wizard)
- Full-text catalog properties (Tables/Views page)
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Workgroup Edition
| kbtshoot kbexpertiseadvanced kbsql2005fts kbprb KB916785 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate