Sybase - Inserting N Rows In a Loop Committing After Each Mth Row

It can be useful to test the performance of Sybase Adaptive Server Enterprise (ASE) by inserting a large number of rows with dummy data into a test table. Let's create a Transact-SQL script to do that.

First let's create a test table:

  CREATE TABLE data1
  (
     c1 INT NOT NULL,
     c2 VARCHAR(20) NULL
  )
  GO

Then you can use the following Transact-SQL script to insert 1,000,000 rows into a test table committing after each 10,000th row:

  -- Run a loop to insert 1,000,000 rows
   DECLARE @i INT,
               @start DATETIME
 
  SET @i = 1
  SET @start = GETDATE()
 
  WHILE @i <= 1000000 BEGIN
 
    -- Start a transaction
    IF @i % 10000 = 1 BEGIN
       BEGIN TRANSACTION
       PRINT 'Transaction started'
    END 
 
     INSERT INTO data1 VALUES (@i, 'Sample data...')
     SET @i = @i + 1
 
    -- Commit after each 10,000 row
    IF @i % 10000 = 0 BEGIN
      COMMIT
      PRINT 'Committed'
    END
 
  END
 
  -- Output the execution time in seconds 
  SELECT DATEDIFF(ss, @start, GETDATE())
  GO

After execution the script outputs the number of seconds required to inserts all rows.

• Sybase Adaptive Server Enterprise Reference and Migration

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - October 2012