Question

How to see average batch processing time in SQL for FlexiCapture?

Answer

In order to see average batch processing time, execute the following script in SQL:

DECLARE @start datetime

DECLARE @end datetime

 

SET @start = '2020-02-18 00:30:00.000'

SET @end = '2020-02-18 04:30:00.000'

 

SELECT AVG(dt_diff) AS avg_sec, MIN(dt_diff) AS min_sec, MAX(dt_diff) AS max_sec, COUNT(dt_diff) AS count_batches

FROM (

    SELECT [EL].BatchId,

        datediff(

            second,

            MIN(CASE WHEN [EL].EventType = 7 THEN [EL].Date end),

            MAX(CASE WHEN [EL].EventType = 30 THEN [EL].Date end)

        ) AS dt_diff

    FROM [EventLog] AS [EL]

    WHERE [EL].[EventType] IN (7, 30)

    and EL.Date > @start

    and EL.Date < @end

    GROUP BY [EL].BatchId

) AS t

WHERE dt_diff IS NOT NULL