Home Database Mysql Tutorial SQL Server插入或者批量导入大量数据的性能表现

SQL Server插入或者批量导入大量数据的性能表现

Jun 07, 2016 pm 05:54 PM
sql server Batch Import insert

我经常看到客户测试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

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Using the MINUS operator in SQL Using the MINUS operator in SQL Feb 18, 2024 pm 04:53 PM

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

Can we insert null value in Java list? Can we insert null value in Java list? Aug 20, 2023 pm 07:01 PM

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

What software is microsoft sql server? What software is microsoft sql server? Feb 28, 2023 pm 03:00 PM

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.

How to insert graphics and text of China map into wps document How to insert graphics and text of China map into wps document Mar 27, 2024 pm 02:01 PM

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 functions of data How to use MongoDB to implement batch import and export functions of data Sep 20, 2023 am 10:00 AM

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 import product inventory in batches How to use PHP to import product inventory in batches Aug 17, 2023 pm 02:21 PM

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.

SQL Server or MySQL? New research reveals the best database choices. SQL Server or MySQL? New research reveals the best database choices. Sep 08, 2023 pm 04:34 PM

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

PHP and SQL Server database development PHP and SQL Server database development Jun 20, 2023 pm 10:38 PM

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

See all articles