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

问题描述

我正在 sql server 上执行一些测试，我想获得最佳的插入速度.我使用的语句是这样的:

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?

附言在这种形式中，我的平均速度为 16.000 行/秒.

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

推荐答案

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

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

• 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 可以为给定的查询建议索引 数据库引擎优化顾问.您应该这样做以确保您没有删除 SQL Server 用来加速 INSERT 的索引.

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屋！