SQL Server插入或者批量导入大量数据的性能表现
我经常看到客户测试SQL Server 的插入(INSERT)或者批量导入大量数据的性能表现。其中有测试大量INSERT的TSQL脚本如下: use myDB go create table t1 ( id int primary key not null identity ( 1 , 1 ), name varchar ( 200 ), memo char ( 500 )); go trun
我经常看到客户测试SQL Server 的插入(INSERT)或者批量导入大量数据的性能表现。其中有测试大量INSERT的TSQL脚本如下:
use myDB
go
createtable t1(id intprimarykeynotnullidentity (1,1),name varchar(200),memo char(500));
go
truncatetable t1
go
declare @i int
set @i=0
declare @beginTime datetime,@endTime datetime
set @beginTime=GETDATE()
while(@i200000)
begin
insertinto t1(name,memo)values('test'+str(RAND()*100000000),'memo'+str(RAND()*100000000))
set @i=@i+1
end
set @endTime=GETDATE()
select @endTime-@beginTime
上面这个脚本测试插入200000行数据需要的时间。这个脚本有问题么?
语法没有问题,但是写法却不够优化。如果你使用performance monitor
来观察,就会发现在数据插入期间log flushes/sec的次数非常高。在我的机器上达到5000。Log flushes发生一次,意味着SQL server 需要写入事务日志(transaction log)一次。每秒5000次的日志写动作导致磁盘大量的写操作。正是这个磁盘写操作影响了上面的batch的性能。我上面脚本在我的机器上使用了大概40秒左右的时间。
如何改进这个脚本的性能呢?如何减少log flushes/sec从而减少磁盘的写操作? 答案是使用Batch Size如下面脚本所示。
truncatetable t1
go
declare @i int
set @i=0
declare @beginTime datetime,@endTime datetime
set @beginTime=GETDATE()
declare @batchSize int
set @batchSize=1000
while(@i00000)
begin
if (@i%@batchSize=0)
begin
if (@@TRANCOUNT>0)COMMITTRAN
BEGINTRAN
end
insertinto t1(name,memo)values('test'+str(RAND()*100000000),'memo'+str(RAND()*100000000))
set @i=@i+1
end
if (@@TRANCOUNT>0)COMMITTRAN
set @endTime=GETDATE()
select @endTime-@beginTime
黄色高亮是我改进的地方。我在同样的机器上跑了一下,奇迹发生了,它只使用了7秒多一点。快了5倍多!如果在看下performance

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Usage of MINUS in SQL and specific code examples In SQL, MINUS is an operator used to perform a difference operation between two result sets. It is used to delete the same rows from the first result set as in the second result set. The result set returned by the MINUS operator will contain rows that exist only in the first result set. The following uses specific code examples to demonstrate the usage of MINUS: Assume there are two tables - "table1" and "table2", their structures are as follows: Table name: table1 field

SolutionYes,Wecaninsertnullvaluestoalisteasilyusingitsadd()method.IncaseofListimplementationdoesnotsupportnullthenitwillthrowNullPointerException.Syntaxbooleanadd(Ee) Appends the specified element to the end of this list. Type parameter E − The runtime type of the element. Parameter e − element to be appended to this list

Microsoft SQL Server is a relational database management system launched by Microsoft. It is a comprehensive database platform that uses integrated business intelligence (BI) tools to provide enterprise-level data management. It is easy to use, has good scalability, and has a high degree of integration with related software. High advantages. The SQL Server database engine provides more secure and reliable storage functions for relational data and structured data, allowing users to build and manage highly available and high-performance data applications for business.

1. Open the wps software and enter the wps text operation interface. 2. Find the insert option in this interface. 3. Click the Insert option and find the Shape option in its editing area. 4. Click the shape option and find the recommended option in its sub-menu. 5. Find the China map option in the recommended options. 6. Click on the China map option and drag it with the left mouse button in the editing input area to get the China map we need.

How to use MongoDB to implement batch import and export of data. MongoDB is a NoSQL database. As a non-relational database, it has great flexibility and performance advantages in data storage and query. For application scenarios that require batch import and export of data, MongoDB also provides corresponding tools and interfaces. This article will introduce how to use MongoDB to implement batch import and export of data, and provide specific code examples. 1. Batch import data into MongoDB, you can

How to use PHP to implement the function of batch importing product inventory On e-commerce platforms, batch importing of product inventory is a common requirement. Through batch import, merchants can quickly update the inventory information of a large number of products and improve work efficiency. This article will introduce how to use the PHP programming language to implement this function to facilitate inventory management by merchants. First, we need to create a table file to store the inventory information of the product. This form file can be in Excel or CSV format, and the merchant can fill in the product information, including the product name.

SQLServer or MySQL? The latest research reveals the best database selection. In recent years, with the rapid development of the Internet and big data, database selection has become an important issue faced by enterprises and developers. Among many databases, SQL Server and MySQL, as the two most common and widely used relational databases, are highly controversial. So, between SQLServer and MySQL, which one should you choose? The latest research sheds light on this problem for us. First, let

With the popularity of the Internet, website and application development has become the main business of many companies and individuals. PHP and SQLServer database are two very important tools. PHP is a server-side scripting language that can be used to develop dynamic websites; SQL Server is a relational database management system developed by Microsoft and has a wide range of application scenarios. In this article, we will discuss the development of PHP and SQL Server, as well as their advantages, disadvantages and application methods. First, let's
