Reporting_PerformanceAnalysis

using BatchProcessing logging to get performance stats

-- review BatchProcess logs
SELECT *
FROM dbo.MFProcessBatch AS mpb
ORDER BY mpb.ProcessBatch_ID DESC;

SELECT *
FROM dbo.MFProcessBatchDetail AS mpbd
-- WHERE mpbd.ProcessBatch_ID = 1180
ORDER BY mpbd.ProcessBatch_ID DESC
        ,mpbd.ProcessBatchDetail_ID DESC;

-- summary of ProcessBatch
SELECT mpb.ProcessType
      ,mpb.LogText
      ,COUNT(*) Instances
      ,AVG(mpb.DurationSeconds)
FROM dbo.MFProcessBatch AS mpb
WHERE mpb.Status = 'Completed'
      AND mpb.ProcessType IS NOT NULL
GROUP BY mpb.ProcessType
        ,mpb.LogText;

-- ProcessBatch summary by table
SELECT mpb.ProcessType
      ,mpbd.MFTableName
      ,COUNT(*)                     Instances
      ,SUM(mpb.DurationSeconds) totalDuration
FROM dbo.MFProcessBatchDetail     AS mpbd
    INNER JOIN dbo.MFProcessBatch AS mpb
        ON mpb.ProcessBatch_ID = mpbd.ProcessBatch_ID
WHERE mpb.Status = 'Completed'
      AND mpb.ProcessType IS NOT NULL
      AND mpbd.ColumnName = 'NewOrUpdatedObjectDetails'
GROUP BY mpb.ProcessType
        ,mpbd.MFTableName;

-- records per second
SELECT mpb.ProcessType
      ,mpbd.MFTableName
      ,mpb.LogText
      ,MIN(mpb.CreatedOn)                 AS earliestDate
      ,MAX(mpb.CreatedOn)                 AS lastUpdate
      ,COUNT(*)                               Instances
      ,SUM(mpb.DurationSeconds)           AS Total_processingTime
      ,AVG(mpb.DurationSeconds)           AS Ave_time
      ,SUM(CAST(mpbd.ColumnValue AS INT)) AS recordCount
      ,CASE
           WHEN (SUM(CAST(mpbd.ColumnValue AS INT))) > 0 THEN
               CAST(ROUND(SUM(CAST(mpbd.ColumnValue AS INT)) / SUM(mpb.DurationSeconds), 2, 1) AS DECIMAL(18, 2))
           ELSE
               NULL
       END                                    AS Records_Per_Second
FROM dbo.MFProcessBatchDetail     AS mpbd
    INNER JOIN dbo.MFProcessBatch AS mpb
        ON mpb.ProcessBatch_ID = mpbd.ProcessBatch_ID
WHERE mpb.Status = 'Completed'
      AND mpb.ProcessType IS NOT NULL
      AND mpbd.ColumnName = 'NewOrUpdatedObjectDetails'
      AND mpbd.ColumnValue <> '0'
GROUP BY mpb.ProcessType
        ,mpbd.MFTableName
        ,mpb.LogText
ORDER BY mpb.ProcessType;

-- graph by table by session
SELECT mpb.ProcessBatch_ID
      ,mpb.LogText
      ,SUM(mpb.DurationSeconds)           AS Total_processingTime
      ,AVG(mpb.DurationSeconds)           AS Ave_time
      ,SUM(CAST(mpbd.ColumnValue AS INT)) AS recordCount
      ,CASE
           WHEN (SUM(CAST(mpbd.ColumnValue AS INT))) > 0 THEN
               CAST(ROUND(SUM(CAST(mpbd.ColumnValue AS INT)) / SUM(mpb.DurationSeconds), 2, 1) AS DECIMAL(18, 2))
           ELSE
               NULL
       END                                    AS Records_Per_Second
      ,mpb.ProcessType
      ,mpbd.MFTableName
      ,MIN(mpb.CreatedOn)                 AS earliestDate
      ,MAX(mpb.CreatedOn)                 AS lastUpdate
      ,COUNT(*)                               Instances
FROM dbo.MFProcessBatchDetail     AS mpbd
    INNER JOIN dbo.MFProcessBatch AS mpb
        ON mpb.ProcessBatch_ID = mpbd.ProcessBatch_ID
WHERE mpb.Status = 'Completed'
      AND mpb.ProcessType IS NOT NULL
      AND mpbd.ColumnName = 'NewOrUpdatedObjectDetails'
      AND mpbd.ColumnValue <> '0'
GROUP BY mpb.ProcessType
        ,mpb.ProcessBatch_ID
        ,mpbd.MFTableName
        ,mpb.LogText
ORDER BY mpb.ProcessType;

-- base data
SELECT mpb.ProcessType
      ,mpbd.MFTableName
      ,mpb.LogText
      ,mpb.CreatedOn
      ,mpb.DurationSeconds
      ,mpbd.ColumnValue
      ,*
FROM dbo.MFProcessBatchDetail     AS mpbd
    INNER JOIN dbo.MFProcessBatch AS mpb
        ON mpb.ProcessBatch_ID = mpbd.ProcessBatch_ID
WHERE mpb.Status = 'Completed'
      AND mpb.ProcessType IS NOT NULL
      AND mpbd.ColumnName = 'NewOrUpdatedObjectDetails'
      AND mpbd.ColumnValue <> '0';