慢速插入大量记录 [英] inserting huge volume of records in to table-slow

问题描述

嗨 我需要从温度表向主表中插入100000条记录.
我在临时表中有100000条记录.在这里，我正在为每条记录进行验证，并移至主表.

问题是...
前10000条记录插入速度非常快(1分钟).
接下来的10000条记录的速度比开始的10000条(2分钟)再慢4分钟........
像这样持续下去.
我不知道发生了什么.
请任何人帮助我..

感谢和问候
Arun.

Hi I need to insert 100000 records into the master table from temperory table.
i have 100000 records in the temporary table.here i am doing validation for each record and i am moving to master table.

problem is...
first 10000 records are inserting very fast(1 min).
next 10000 records are bit slow compare than first 10000(2 min) then 4 min ......
like this it is going on.
i dont know what is happening.
Please anyone help me..

thanks and regards
Arun.

推荐答案

由于登录(到事务日志文件)等原因，可能有很多原因.
首先看看有关散装刀片的内容

http://www.simple-talk .com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/ [ http://msdn.microsoft.com/en-us/library/dd425070 (SQL.100，printer).aspx [ http://f5debug.ne​​t/2011/10/12/free-ebook-download-sql-server-integration-services-ssis-step-by-step-tutorials-version-2-0/ [ ^ ]

There are many reasons that this could happen because of logging (to the transaction log file) etc.

First take a look at this regarding bulk inserts

http://www.simple-talk.com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/[^]

If this does not help then I would consider adding an exclusive lock on the table while inserting records. This would improve the performance a bit as no other writes or reads could be done.

http://msdn.microsoft.com/en-us/library/dd425070(SQL.100,printer).aspx[^]

If you can, and you know a little bit about SSIS, I would really recommend using that for all mass / bulk inserts. SSIS is optimised for things like this.

http://f5debug.net/2011/10/12/free-ebook-download-sql-server-integration-services-ssis-step-by-step-tutorials-version-2-0/[^]

它也可能与表上的索引有关.如果您有许多索引，则在插入时必须重新组织"已插入的新数据的索引.随着将更多数据插入表中，索引必须在更大的记录集上进行重组，这就是为什么您看到这种退化行为的原因，即插入变得越来越慢.

特别要看一下主表上的聚集索引.确保您要插入的数据与集群中的inex期望的顺序相同..

我以前有过这样的行为，有时删除集群之外的所有索引实际上更快，以正确的顺序插入数据(在集群索引的末尾"，这样就不会引起集群重建)然后在插入所有数据后重新创建索引.我已经为通宵重建\ ETL实施了这样的事情.

您可以发布您的主表结构吗?在SSMS中，在表格上的鼠标右键->脚本表为->创建-然后将结果发布到此处.

It could also be related to indexes on the table. If you have a number of indexes, then as you insert it has to ''reorganise'' the indexes for the new data you have inserted. As more data is inserted into the table, the indexes have to reorganise over a larger set of records which is why you see this kind of degenerative behaviour with inserts becoming slower and slower.

In particular, have a look at the clustered index on the master table. Make sure that the data you are inserting is in the order expected by the clustered inex..

I''ve had behaviour like this before and sometimes it is actually quicker to drop all indexes except the clustered, insert data in the correct order (at the ''end'' of the clustered index, so not to cause a clustered rebuild) then recreate the indexes after all data inserted. I''ve implemented things like this for overnight rebuilds \ ETL.

Can you post your master table structure? In SSMS, right mouse on table -> Script Table As -> Create - then post the results here.

这篇关于慢速插入大量记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！