SQL SQL加快插入的性能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7090243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL speed up performance of insert?
提问by Corovei Andrei
I am performing some test on sql server and I want to get the best insert speed possible. The statement I use is something like this:
我正在 sql server 上执行一些测试，我想获得最好的插入速度。我使用的语句是这样的：
INSERT INTO db_Test_databse..tbl_test with(rowlock)
( joinid, date_key,
col1, col2,col3,col4, col5, col6, col7, col8, col9, col10, ...
)
SELECT tabid as joinid,
date_key,
rec_type,
col2,
dbo.udf_convert_hex_to_dec(col3),
col4, col5, col6, col7, col8,
dbo.udf_convert_hex_to_dec(col9),
dbo.udf_convert_hex_to_dec(col10),
...
from source_table f
There are 25 columns; most of them are of type bigint or int.
有25列；它们中的大多数是 bigint 或 int 类型。
I dropped all indexes from the destination table except the primary key which is an identity field.
除了作为身份字段的主键之外，我从目标表中删除了所有索引。
Any tips on how to improve the performance more?
关于如何进一步提高性能的任何提示？
P.s. In this form I have an average speed of 16.000 rows / sec.
Ps 在这种形式中，我的平均速度为 16.000 行/秒。
回答by Justin
To get the best possibleperformance you should:
要获得最佳性能，您应该：
- Remove all triggers and constraints on the table
- Remove all indexes, except for those needed by the insert
- Ensure your clustered index is such that new records will always be inserted at the end of the table (an identity column will do just fine). This prevents page splits (where SQL Server must move data around because an existing page is full)
- Set the fill factorto 0 or 100 (they are equivalent) so that no space in the table is left empty, reducing the number of pages that the data is spread across.
- Change the recovery modelof the database to Simple, reducing the overhead for the transaction log.
- 删除表上的所有触发器和约束
- 删除所有索引，除了插入所需的索引
- 确保您的聚集索引总是在表的末尾插入新记录（标识列就可以了）。这可以防止页面拆分（其中 SQL Server 必须移动数据，因为现有页面已满）
- 将填充因子设置为 0 或 100（它们是等效的），以便表中没有空白，从而减少数据分布的页数。
- 将数据库的恢复模式改为Simple，减少事务日志的开销。
Are multiple clients inserting records in parallel? If so then you should also consdier the locking implications.
多个客户端是否并行插入记录？如果是这样，那么您还应该考虑锁定的含义。
Note that SQL Server can suggest indexes for a given query either by executing the query in SQL Server Management Studioor via the Database Engine Tuning Advisor. You should do this to make sure you haven't removed an index which SQL Server was using to speed up the INSERT.
请注意，SQL Server 可以通过在 SQL Server Management Studio 中执行查询或通过数据库引擎优化顾问为给定查询建议索引。您应该这样做以确保您没有删除 SQL Server 用来加速INSERT.
If this still isn't fast enough then you should consider grouping up inserts an using BULK INSERTinstead (or something like the bcp utilityor SqlBulkCopy, both of which use BULK INSERTunder the covers). This will give the highest throughput when inserting rows.
如果这仍然不够快，那么您应该考虑将插入分组BULK INSERT而不是使用（或类似bcp 实用程序或SqlBulkCopy，两者都在幕后使用BULK INSERT）。这将在插入行时提供最高的吞吐量。
Also see Optimizing Bulk Import Performance- much of the advice in that article also applies to "normal" inserts.
另请参阅优化批量导入性能- 该文章中的大部分建议也适用于“正常”插入。
回答by GilShalit
Have you considered using SqlBulkCopy? You need to build a DataTable and pass it to the WriteToServer routine.
您是否考虑过使用 SqlBulkCopy？您需要构建一个 DataTable 并将其传递给 WriteToServer 例程。
It's FAST!
它很快！
回答by atik sarker
you can use in your store procedure before end
您可以在结束之前在您的存储过程中使用
OPTION(RECOMPILE)
