My laptop is faster than SQL Azure, latency part 3

Some time back a former colleague of mine posted a blog about the poor performance of SQL Azure databases.

At the time the blog post created a big splash, and quite a few people were debating it and were unhappy about it.

The reality is that things have not changed much since then (the original blog post is quite old if we count its age in technology years – it was written on January 18th, 2012). There are still quite some performance problems with SQL Azure and it is quite debatable that they will get solved anytime soon.

Let’s build a test case:

what we are looking into here is the insert performance into a SQL Azure table, by using a stored procedure which is local to the SQL Azure database.

The idea is to test the insert of rows into a table in SQL Azure and test the latencies and the resources used.

The scripts for creating the database objects will be posted at the end of this blog post, but for now let’s get going with the test results.

 

Test Case 1: insert of 10 rows with 1 concurrent connection

I am executing the following code with the help of SQL Latency Meter tool:

?Download download.txt
1
exec [dbo].[insert_test] @rows = 10

And I am monitoring the results by looking at the [dbo].[results] table to see the time it took for the insert:

?Download download.txt
1
2
select * from [dbo].[results]
order by starttime desc

 

So here are the results:

Connection Latency (ms) = 800

Query Processing Latency (ms) = 133 (time it took to insert the rows)

Data Receiving Latency (ms)  = 158

Total Latency (ms) = 1091 (a second to insert 10 rows!)

Rows per millisecond = 0.07518796992

 

Test Case 2: insert of 1000 rows with 1 concurrent connection

I am executing the following code with the help of SQL Latency Meter tool:

?Download download.txt
1
exec [dbo].[insert_test] @rows = 1000

And I am monitoring the results by looking at the [dbo].[results] table to see the time it took for the insert:

?Download download.txt
1
2
select * from [dbo].[results]
order by starttime desc

 

So here are the results:

Connection Latency (ms) = 757

Query Processing Latency (ms) = 1254 (time it took to insert the rows)

Data Receiving Latency (ms)  = 214

Total Latency (ms) = 2225 (over 2 seconds to insert 1000 rows!)

Rows per millisecond0.79744816586

 

Test Case 3: insert of 10000 rows with 1 concurrent connection

I am executing the following code with the help of SQL Latency Meter tool:

?Download download.txt
1
exec [dbo].[insert_test] @rows = 10000

And I am monitoring the results by looking at the [dbo].[results] table to see the time it took for the insert:

?Download download.txt
1
2
select * from [dbo].[results]
order by starttime desc

 

So here are the results:

Connection Latency (ms) = 806

Query Processing Latency (ms) = 14080 (time it took to insert the rows)

Data Receiving Latency (ms)  = 187

Total Latency (ms) = 15073 (over 15 seconds to insert 10000 rows!)

Rows per millisecond = 0.71022727272

 

Test Case 4: insert of 100000 rows with 1 concurrent connection

I am executing the following code with the help of SQL Latency Meter tool:

?Download download.txt
1
exec [dbo].[insert_test] @rows = 100000

And I am monitoring the results by looking at the [dbo].[results] table to see the time it took for the insert:

?Download download.txt
1
2
select * from [dbo].[results]
order by starttime desc

 

So here are the results:

Connection Latency (ms) = 774

Query Processing Latency (ms) = 170023 (time it took to insert the rows)

Data Receiving Latency (ms)  = 247

Total Latency (ms) = 171044 (over 2 minutes and a half to insert 100000 rows!)

Rows per millisecond = 0.58815572010

 

So far it seems scary. It seems like Azure still cannot handle inserts fast enough. No idea how the logs are setup for the Azure databases – does anyone know, really? – and since Azure has triple failure protection (i.e. data is replicated to 3 different storage units as soon as it arrives) I guess this creates such tremendous latencies.

But this is not all.

Let’s see what happens when the multiple users come in and try to insert data simultaneously via different connections.

For the shortness of this blog post I will perform only 2 of the above tests:

Test Case 5: insert of 1000 rows with 100 concurrent connections

I am executing the following code with the help of SQL Latency Meter tool and am creating 100 concurrent connections to perform 1000 inserts each:

?Download download.txt
1
exec [dbo].[insert_test] @rows = 1000

And I am monitoring the results by looking at the [dbo].[results] table to see the time it took for the insert:

?Download download.txt
1
2
select * from [dbo].[results]
order by starttime desc

 

So here are the results:

Connection Latency (ms) = 789

Query Processing Latency (ms) = 8988 (time it took to insert the rows)

Data Receiving Latency (ms)  = 285

Total Latency (ms) = 10062 (10 seconds to insert 150000 rows!)

Rows per millisecond = 9.938382031405287

 

So, this is much faster than inserting 100000 rows in one connection – that took over 2 minutes and a half.

Now, I am so curious what happens when I have 500 connections inserting 10000 rows each.

 

Test Case 6: insert of 10000 rows with 500 concurrent connections

I am executing the following code with the help of SQL Latency Meter tool and am creating 500 concurrent connections to perform 1000 inserts each:

?Download download.txt
1
exec [dbo].[insert_test] @rows = 10000

And I am monitoring the results by looking at the [dbo].[results] table to see the time it took for the insert:

?Download download.txt
1
2
select * from [dbo].[results]
order by starttime desc

 

Ha! I guess I broke it. It ran for about 10 minutes after which I got the following errors

 

Following error occured:
Database ‘Test’ on server ‘sj2k9baccf’ is not currently available.  Please retry the connection later.  If the problem persists, contact customer support, and provide them the session tracing ID of ‘615b2d9e-31f3-4b79-93d9-f77fd72276ef’.
Login failed for user ‘Bob’.
Following error occured:
Database ‘Test’ on server ‘sj2k9baccf’ is not currently available.  Please retry the connection later.  If the problem persists, contact customer support, and provide them the session tracing ID of ‘fcf64dc8-99c6-4901-a17b-8e7a2e1a8cb0’.
Login failed for user ‘Bob’.
Following error occured:
Database ‘Test’ on server ‘sj2k9baccf’ is not currently available.  Please retry the connection later.  If the problem persists, contact customer support, and provide them the session tracing ID of ’23e96595-f5f5-4c64-8ce2-dcabab718182′.
Login failed for user ‘Bob’.
Following error occured:
Database ‘Test’ on server ‘sj2k9baccf’ is not currently available.  Please retry the connection later.  If the problem persists, contact customer support, and provide them the session tracing ID of ‘ad5d7ac6-7a5e-4651-a3a3-6e1cedcdc508’.
Login failed for user ‘Bob’.
Following error occured:
Database ‘Test’ on server ‘sj2k9baccf’ is not currently available.  Please retry the connection later.  If the problem persists, contact customer support, and provide them the session tracing ID of ‘b8684564-da50-4466-929b-95ff29738cbc’.
Login failed for user ‘Bob’.

Let’s try again…

Nope. It broke again.

Let’s try if I can have 400 concurrent requests for 10000 rows inserted each.

Hm, it broke again, this time I got an even prettier error:

Following error occured:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
Following error occured:
The service is currently busy. Retry the request after 10 seconds. Incident ID: {EC8C0DED-ADF4-4F89-BA9E-50E4BBB5ACD9}. Code: 4194307
A severe error occurred on the current command.  The results, if any, should be discarded.
Following error occured:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
Following error occured:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
Following error occured:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
Following error occured:
The service is currently busy. Retry the request after 10 seconds. Incident ID: {EC8C0DED-ADF4-4F89-BA9E-50E4BBB5ACD9}. Code: 4194307
A severe error occurred on the current command.  The results, if any, should be discarded.
Following error occured:
The service is currently busy. Retry the request after 10 seconds. Incident ID: {EC8C0DED-ADF4-4F89-BA9E-50E4BBB5ACD9}. Code: 4194307
A severe error occurred on the current command.  The results, if any, should be discarded.
Following error occured:
The service is currently busy. Retry the request after 10 seconds. Incident ID: {EC8C0DED-ADF4-4F89-BA9E-50E4BBB5ACD9}. Code: 4194307
A severe error occurred on the current command.  The results, if any, should be discarded.
Following error occured:
The service is currently busy. Retry the request after 10 seconds. Incident ID: {EC8C0DED-ADF4-4F89-BA9E-50E4BBB5ACD9}. Code: 4194307
A severe error occurred on the current command.  The results, if any, should be discarded.

Riiight. An existing connection was forcibly closed by the remote host

Anyhow, I think my Azure experience is getting soaked in sorrow.

My free Azure account is getting closed tomorrow, and I could not even push more than few hundred concurrent requests at a time…

Enough playing for one day, though.

If anyone feels like they would like to let me test Azure performance on their account, let me know.

 

Scripts to create the database tables:

 

?Download download.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE TABLE [dbo].[TableTest]
    (
      [Int_Col] [int] IDENTITY(1, 1)
                      NOT NULL ,
      [Varchar_Col] [varchar](50) NOT NULL ,
      [Datetime_Col] [datetime] NOT NULL ,
      [GUID_Col] [uniqueidentifier] NOT NULL
    )
ALTER TABLE [dbo].[TableTest] 
ADD  CONSTRAINT [DF_TableTest_Varchar_Col]  DEFAULT ('Varchar_Col') FOR [Varchar_Col]
ALTER TABLE [dbo].[TableTest] 
ADD  CONSTRAINT [DF_TableTest_Datetime_Col]  DEFAULT (GETDATE()) FOR [Datetime_Col]
ALTER TABLE [dbo].[TableTest] 
ADD  CONSTRAINT [DF_TableTest_GUID_Col]  DEFAULT (NEWID()) FOR [GUID_Col]
 GO
CREATE CLUSTERED INDEX [ix_tabletest] ON [dbo].[TableTest] ( [GUID_Col] ASC )WITH 
(PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
 GO
CREATE TABLE [dbo].[results]
    (
      [GUID] [uniqueidentifier] NOT NULL ,
      [test] [varchar](50) NULL ,
      [starttime] [datetime2](7) NULL ,
      [endtime] [datetime2](7) NULL ,
      [rownr] [bigint] NULL ,
      [Runtime_MS] AS ( DATEDIFF(millisecond, [starttime], [endtime]) ) ,
      [Rows_pr_ms] AS ( ISNULL(CONVERT([decimal], [rownr], 0)
                               / DATEDIFF(millisecond, [starttime], [endtime]),
                               ( 0 )) )
    )
 GO
CREATE CLUSTERED INDEX [ix_results] ON [dbo].[results] ( [GUID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON
, ALLOW_PAGE_LOCKS  = ON)
 GO
CREATE TABLE [dbo].[guidtbl]
    (
      [GUID] [uniqueidentifier] NOT NULL
    )
CREATE CLUSTERED INDEX [ix_guidtbl] ON [dbo].[guidtbl] ( [GUID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON
, ALLOW_PAGE_LOCKS  = ON) 
GO

 

And here is the script to create the stored procedure:

?Download download.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROC [dbo].[insert_test] @rows INT
AS 
    SET NOCOUNT ON
    SET STATISTICS IO OFF
    SET STATISTICS TIME OFF
 
    INSERT  guidtbl
            ( GUID )
    VALUES  ( NEWID() )
 
    DECLARE @GUID UNIQUEIDENTIFIER
    SELECT  @GUID = guid
    FROM    guidtbl
    INSERT  results
            ( [GUID], test, starttime )
    VALUES  ( @GUID, 'INSERT_Test', GETDATE() )
    DECLARE @i INT
    SELECT  @i = 0
    WHILE @i < @rows 
        BEGIN
            INSERT  INTO [dbo].[TableTest]
                    ( [Varchar_Col] ,
                      [Datetime_Col] ,
                      [GUID_Col]
                    )
            VALUES  ( DEFAULT ,
                      DEFAULT ,
                      DEFAULT
                    )
            SELECT  @i = @i + 1
        END
    UPDATE  results
    SET     endtime = GETDATE()
    WHERE   test = 'Insert_Test'
            AND endtime IS NULL
    DECLARE @rownr INT
    SELECT  @rownr = @rows
    UPDATE  results
    SET     rownr = @rownr
    WHERE   test = 'Insert_Test'
            AND rownr IS NULL 
 
GO

 

 

 

1 comment to My laptop is faster than SQL Azure, latency part 3