SQL Server 2000：删除和插入大量数据
我们有一个 SQL Server 2000，其中包含大约 6,000,000 条记录。其中一列存储有一个 pdf 文件。
问题是每个月我们都会删除大约 250,000 条记录，并在特定的一天插入大约 250,000 条记录。之后就没有更新，只有读取。
问题是：最好是删除500条记录，插入500条记录，然后删除，然后插入，然后删除，插入等等。
还是一次删除250,000条，分500批插入250,000条？
哪个选项是最佳的？并拥有最好的内存管理？
+1 给任何给我指点 MSDN 文章或其他内容的人。
We have a SQL Server 2000 in place which has about 6,000,000 records. And one column has a pdf stored in it.
The question is every month we delete about 250,000 records and insert about 250,000 on a specific day. After that there are no updates, only reads.
Question is: Is it optimal to delete 500 records and insert 500 records then delete then insert then delete and insert and so on..
Or delete 250,000 at a time and insert 250,000 in 500 batch?
Which option is optimal? and have the best memory management?
+1 for Anyone who points me to a MSDN article or something..
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
http://msdn.microsoft.com/en -us/library/aa178096%28v=sql.80%29.aspx
http://msdn.microsoft.com/en-us/library/aa178096%28v=sql.80%29.aspx
正如您没有提到的那样，值得强调在 RDBMS 上插入/删除/更新任何大量数据的标准做法，即在应用更改之前删除所有索引，然后重新应用它们。
这有两个原因。
系统一次性重建索引比按记录重建索引更快（磁盘上的磁头移动更少）。
如果您从头开始重建索引，则使用它的后续访问可能会更快，因为索引树将更有可能更好地平衡。
As you don't mention it it's worth underlining the standard practice for inserting/deleting/updating any very large volume of data on a rdbms of dropping all indexes before applying the changes, and reapplying them afterwards.
Two reasons for this.
It's faster for the system to rebuild the indexes in one go rather than on a by record basis (less head movement on the disk).
If you rebuild the index from scratch your subsequent accesses using it are likely to be faster as the index tree will be more likely to be better balanced.
您可能需要考虑分区。如果您将其组织起来，一次只需删除一个分区，删除将需要几毫秒的时间。请参阅 http://msdn.microsoft.com/en -us/library/aa902650(v=sql.80).aspx
You might want to consider partitioning. If you organize it so you can simply drop a partition at a time, deleting will take milliseconds. See http://msdn.microsoft.com/en-us/library/aa902650(v=sql.80).aspx