/*
* Report for avg times of all scan processing phases.
* Times are in millisecond.
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
--TODO: replace avg with mean
AVG(CAST(DATEDIFF(millisecond, CONVERT(DATETIME2, ps.createdDate, 103), importing.sTime) AS BIGINT)) AS 'Queued for Import (Scan Files)',
AVG(
CASE
WHEN ps.importType = 'FILE_UPLOAD' THEN CAST(DATEDIFF(millisecond, importing.sTime, queuedForProcessing.sTime) AS BIGINT)
ELSE 0 END) AS 'Import Time (Scan Files)',
-- Avg time to pull/import a RP PendingScan
AVG(
CASE
WHEN ps.importType = 'REMOTE_PROVIDER_IMPORT_TYPE' THEN CAST(DATEDIFF(millisecond, CONVERT(DATETIME2, ps.requestTime, 103), queuedForProcessing.sTime) AS BIGINT)
ELSE 0 END
) AS 'RP Scan Import Time',
AVG(CAST(DATEDIFF(millisecond, queuedForProcessing.sTime, processing.sTime) AS BIGINT )) AS 'Queued for Processing',
AVG(CAST(DATEDIFF(millisecond, processing.sTime, queueingForIngestion.sTime) AS BIGINT)) AS 'Processing',
AVG(CAST(DATEDIFF(millisecond, queueingForIngestion.sTime, queuedForIngestion.sTime) AS BIGINT)) AS 'Queueing for Ingestion',
--This might be closer in accuracy than (queuedForReconcile (minus) queuedForIngestion)
AVG(CAST(DATEDIFF(millisecond, queueingForIngestion.sTime, queuedForReconcile.sTime) AS BIGINT)) AS 'DB Ingestion',
AVG(CAST(DATEDIFF(millisecond, queuedForReconcile.sTime, reconciling.sTime) AS BIGINT)) AS 'Queued for Reconcile',
AVG(CAST(DATEDIFF(millisecond, reconciling.sTime, completed.sTime) AS BIGINT)) AS 'Reconcile'
FROM
(SELECT TOP 500 * FROM NewPendingScan
WHERE active = 1 and pendingScanStatus = 'COMPLETED'
ORDER BY endTime DESC) ps
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'COMPLETED') completed ON completed.pendingScanId = ps.id
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'RECONCILING') reconciling ON reconciling.pendingScanId = ps.id
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_RECONCILE') queuedForReconcile ON queuedForReconcile.pendingScanId = ps.id
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_INGESTION') queuedForIngestion ON queuedForIngestion.pendingScanId = ps.id
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUING_FOR_INGESTION') queueingForIngestion ON queueingForIngestion.pendingScanId = ps.id
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'PROCESSING') processing ON processing.pendingScanId = ps.id
INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_PROCESSING') queuedForProcessing ON queuedForProcessing.pendingScanId = ps.id
LEFT JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'IMPORTING') importing ON importing.pendingScanId = ps.id
LEFT JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_IMPORT') queuedForimport ON importing.pendingScanId = ps.id;
GO |