SQL加快插入性能? [英] SQL speed up performance of insert?

问题描述

我正在sql服务器上进行一些测试，我想获得尽可能最佳的插入速度. 我使用的语句是这样的:

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:

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

共有25栏；它们大多数是bigint或int类型的.

There are 25 columns; most of them are of type bigint or 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.在这种形式下，我的平均速度为16.000行/秒.

P.s. In this form I have an average speed of 16.000 rows / sec.

推荐答案

要获得最佳性能，您应该:

• 删除表上的所有触发器和约束
• 删除所有索引，但插入所需的索引除外
• 请确保您的聚集索引是新记录，总是将其插入到表的末尾(标识列就可以了).这样可以防止页面拆分(由于现有页面已满，SQL Server必须在其中移动数据)
• 将填充系数设置为0或100(它们等效)，以使表中没有任何空间为空，从而减少了数据分布的页面数.
• 将数据库的恢复模型更改为简单"，从而减少了事务日志的开销.

• 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 factor to 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 model of the database to Simple, reducing the overhead for the transaction log.

是否有多个客户端并行插入记录?如果是这样，那么您还应该考虑锁定的含义.

Are multiple clients inserting records in parallel? If so then you should also consdier the locking implications.

请注意，SQL Server可以为给定查询建议索引

Note that SQL Server can suggest indexes for a given query either by executing the query in SQL Server Management Studio or 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.

如果这还不够快，那么您应该考虑使用 BULK INSERT 代替(或类似 bcp实用工具或 SqlBulkCopy 使用BULK INSERT进行隐藏).插入行时，这将提供最高的吞吐量.

If this still isn't fast enough then you should consider grouping up inserts an using BULK INSERT instead (or something like the bcp utility or SqlBulkCopy, both of which use BULK INSERT under the covers). This will give the highest throughput when inserting rows.

另请参见优化批量导入性能-其中的许多建议该文章也适用于常规"插入.

Also see Optimizing Bulk Import Performance - much of the advice in that article also applies to "normal" inserts.

这篇关于SQL加快插入性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！