Calculating and Displaying Throughput Metrics (RPM) - Perceptive Content Database IN_DB_UTIL Package 5.0 for SQL Server - Foundation 24.1 - Foundation 24.1 - Ready - Perceptive Content - external

Perceptive Content Database IN_DB_UTIL Package 5.0 for SQL Server

Platform
Perceptive Content
Product
Perceptive Content Database IN_DB_UTIL Package 5.0 for SQL Server
Release
Foundation 24.1
License

The following example combines the IN_DB_UTIL_FN_GET_DURATION function and the IN_DB_UTIL_SP_GET_RPM stored procedure to demonstrate how you could use them to record and or display throughput metrics of various operations.

Throughput is measured as Rows Per Minute (RPM)

DECLARE  
@START_TIMESTAMP  DATETIME2, 
@TOTAL_ROWS       BIGINT, 
@TOTAL_MSEC       BIGINT, 
@ROWS_PER_MINUTE  INTEGER, 
@PRINT_INFO       NVARCHAR(MAX);  

BEGIN   
  -- Record the start time   
  SET @START_TIMESTAMP = CURRENT_TIMESTAMP;      

  -- Execute any operation that affects rows   
  SELECT * FROM inuser.IN_DOC; 

  -- Record the number of rows   
  SET @TOTAL_ROWS = @@ROWCOUNT;      

  -- Calculate Duration in Milliseconds   
  SET @TOTAL_MSEC = DATEDIFF_BIG(MS, @START_TIMESTAMP, CURRENT_TIMESTAMP);      

  -- Get Rows Per Minute   
  EXEC IN_GET_RPM @TOTAL_ROWS, @TOTAL_MSEC, @RPM = @ROWS_PER_MINUTE OUTPUT;      

  -- Build Throughput Message to Display   
  SET @PRINT_INFO  = NCHAR(10)+N'Total Rows Selected was '                               
                              +CONVERT(VARCHAR,FORMAT(@TOTAL_ROWS,N'###,###,###'));   
  SET @PRINT_INFO += NCHAR(10)+N'Total Duration was '                               
                              +inuser.IN_GET_DURATION(@TOTAL_MSEC);   
  SET @PRINT_INFO += NCHAR(10)+N'Total Rows Per Minute was '                               
                              +CONVERT(VARCHAR,FORMAT(@ROWS_PER_MINUTE,N'###,###,###'));      

  -- Display the Throughput Metrics   
  RAISERROR (@PRINT_INFO,0,1) WITH NOWAIT; 
END;   

Displayed Throughput in the Messages Tab  

Total Rows Selected was 1,504 
Total Duration was 70 Milliseconds 
Total Rows Per Minute was 1,289,142