在Azure数据库中插入1000行需要13秒? [英] Inserting 1000 rows into Azure Database takes 13 seconds?

问题描述

谁能告诉我为什么要将1000行插入Azure托管的SQL数据库中可能要花12秒钟以上的时间?我刚刚开始使用Azure，这(显然)是荒谬的...

Can anyone please tell me why it might be taking 12+ seconds to insert 1000 rows into a SQL database hosted on Azure? I'm just getting started with Azure, and this is (obviously) absurd...

Create Table xyz (ID int primary key identity(1,1), FirstName varchar(20))
GO

create procedure InsertSomeRows as
set nocount on
Declare @StartTime datetime = getdate()
Declare @x int = 0;
While @X < 1000
Begin
    insert into xyz (FirstName) select 'john' 
    Set @X = @X+1;
End

Select  count(*) as Rows, DateDiff(SECOND, @StartTime, GetDate()) as SecondsPassed 
from    xyz
GO

Exec InsertSomeRows
Exec InsertSomeRows
Exec InsertSomeRows

GO
Drop Table xyz
Drop Procedure InsertSomeRows

输出:

Rows        SecondsPassed
----------- -------------
1000        11

Rows        SecondsPassed
----------- -------------
2000        13

Rows        SecondsPassed
----------- -------------
3000        14

推荐答案

两个因素造成的影响最大.首先，我将所有插入内容包装到一个事务中.这使我从每秒100次插入增加到大约2500次.然后，我将服务器升级到PREMIUM P4层，现在我每秒可以插入25,000次(在事务中.)

Two factors made the biggest difference. First, I wrapped all the inserts into a single transaction. That got me from 100 inserts per second to about 2500. Then I upgraded the server to a PREMIUM P4 tier and now I can insert 25,000 per second (inside a transaction.)

这将需要一些习惯来使用Azure服务器，以及哪些最佳实践可以为我提供所需的结果.

It's going to take some getting used to using an Azure server and what best practices give me the results I need.

这篇关于在Azure数据库中插入1000行需要13秒?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！