Input Operation Test with INSERT Statements

Q

How to run an input operation test with INSERT statements?

✍: FYIcenter.com

A

You can put an INSERT statement in a WHILE loop to run an input operation test as shown in this tutorial.

Create the following SQL script, fyi_int_insert.sql:

-- fyi_int_insert.sql
-- Copyright (c) FYIcenter.com 

DECLARE @Count INT
SET @Count = 100

DECLARE @I INT
DECLARE @STime DATETIME
DECLARE @ETime DATETIME
DECLARE @Duration INT

TRUNCATE TABLE FYI_INT
SET @STime = GETDATE()

SET @I = 0
WHILE @I < @Count BEGIN
   INSERT INTO FYI_INT VALUES (@I)
   SET @I = @I+1
END

SET @ETime = GETDATE()
TRUNCATE TABLE FYI_INT

SET @Duration = DATEDIFF(millisecond,@STime,@ETime)
SELECT @Count AS Count, @Duration AS Duration, 
  1000*@Count/@Duration AS Throughput, @Duration/@Count AS Latency

This script, fyi_int_insert.sql, will generate 4 fields in the output:

  • Count - The number of records inserted into the table.
  • Duration - The total execution time in seconds
  • Throughput - The number of INSERT statements executed per seconds.
  • Latency - The average execution time of each INSERT statement in milliseconds.

Now run the script with different @Count values on the Azure SQL Database. You will get something like:

Count   Duration   Throughput   Latency
  100        410          243         4
 1000       4140          241         4
10000      45453          220         4
50000     215640          231         4

As you can see, the script is working. This Azure SQL Database gives me a throughput of about 240 INSERT statements per second. It's not really fast. But it is very stable, no ups and downs.

 

Verify Azure SQL Database Storage Usage

Table for IOPS Tests on INSERT Statements

IOPS Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials

2019-07-30, 270👍, 0💬