[T-SQL] Insert dummy and test data in tables

  • Article

Lavorando con applicazioni database mi rendo conto che c’è una fase importantissima che troppo spesso viene trascurata: il test delle performance sui presunti carichi che si dovranno sostenere.

Mi spiego con una domanda: qual 'è il senso di testare le nostre applicazioni con due, cinque, dieci righe nelle tabelle?

(per poi sentirsi dire… ma in sviluppo andava tutto veloce …)

 

Forse, uno dei problemi, è quello di riuscire ad inserire righe in un numero simile a quelle che avremo sui nostri sistemi di produzione.

Certamente possiamo evitare di fare questa attività a mano.

 

Con qualche riga di codice possiamo costruire una procedura che, preso in ingresso il nome della tabella (schema e nome) ed un numero di righe, riempia di valori fittizzi le nostre colonne consentendoci di avere strutture, in test, di dimensioni almeno simili all’ambiente finale.

 

Questo il codice:

 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'up_insertDummyData')
DROP PROCEDURE dbo.up_insertDummyData
GO


CREATE PROCEDURE dbo.up_insertDummyData
(
      @schemaName nvarchar( 250 ) , 
     @tableName nvarchar( 250 ) , 
      @nr int 
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i int = 0, @tsql varchar( max ) = '';
    WHILE (@i < @nr)
        BEGIN
            SELECT @tsql = @tsql + CASE
                                 WHEN c.column_id = 1 THEN 'INSERT ' + @schemaName + '.[' + t.name + '] VALUES ( '
                                     ELSE ''
                                 END + 
                          CASE
                          WHEN c.is_identity = 0 THEN CASE
                            WHEN y.name IN ( 'bit' , 'bigint' , 'int' , 'smallint' , 'tinyint' , 'float' , 'decimal' , 
                             'numeric' , 'money' , 'smallmoney' , 'real' )
                          THEN SUBSTRING( CAST(1000*RAND() AS VARCHAR(50)) , 1 , c.max_length)
                            WHEN y.name IN ( 'binary' , 'varbinary' ) 
                          THEN SUBSTRING( '0x546869732069732044756D6D792044617461' , 1, c.max_length )
                            WHEN y.name IN ( 'varchar' , 'char' , 'text', 'nchar' , 'nvarchar' , 'ntext' ) 
                          THEN '''' + SUBSTRING( 'some data some data some data' , 1 , c.max_length /2 ) + ''''
                            WHEN y.name IN ( 'date' , 'time' , 'datetime' , 'datetime2' , 'smalldatetime' , 'datetimeoffset' ) 
                          THEN '''' + CONVERT( varchar( 25 ) , GETDATE( ) , 121 ) + ''''
                            WHEN y.name IN ( 'uniqueidentifier' ) 
                          THEN '''' + CAST( NEWID( ) AS varchar( 36 )) + ''''
                       ELSE '' END + CASE
                                WHEN c.column_id = (SELECT MAX( column_id ) FROM sys.columns WHERE OBJECT_ID = c.OBJECT_ID )THEN ');'
                                    ELSE ','
                                END
                              ELSE ''
                          END
              FROM sys.tables AS t 
             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
             INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
             INNER JOIN sys.types AS y ON c.user_type_id = y.user_type_id
              WHERE 
             t.name = @tableName AND 
             s.name = @schemaName
              
          SET @i+=1;
          EXEC ( @tsql );
          SET @tsql = '';
        END
END;
GO

Un esempio di utilizzo:

 --> Test Procedure:
EXEC dbo.up_insertDummyData @schemaName = 'dbo', @tableName = 'lineItem' , @nr = 100000;

I nostri dati di test:

 --> View data
SELECT count(*) from lineitem

SELECT record_count, avg_record_size_in_bytes, page_count 
  FROM sys.dm_db_index_physical_stats
    ( DB_ID( ) , OBJECT_ID( 'lineItem' ) , -1 , NULL , 'detailed' );

EXEC sp_spaceused 'lineItem'

SELECT TOP 2 * FROM lineitem 
GO

image