Created On:  02 May 2012

Problem:

Is it possible to create a report(s) that will calculate the Average and Total execution times for an Execution Plan?

Resolution:

The following SQL can be used to create an "Advanced" SilkCentral report to calculate the average and total execution times for an Execution Plan:

Total Execution time:
SELECT convert(varchar(10), SUM(Duration)/86400000) ' Days  '
   convert(varchar(10), (SUM(Duration)�400000)/3600000) ' Hours  '
   convert(varchar(10), (SUM(Duration)600000)/60000) ' Mins  '
   convert(varchar(10), (SUM(Duration)`000)/1000) ' sec '
   convert(varchar(10), SUM(Duration)00) ' ms  ' AS [DD:HH:MM:SS:MS]
FROM TM_ExecDefinitionRuns WHERE ExecDefID_fk=${ExecDefID|0} AND Duration <> -1

When executed would generate the following data:



Average Execution time:
SELECT CONVERT(VARCHAR,DATEADD(ms,(SUM(Duration)/COUNT(*)),0),114) AS 'Average_Time'
FROM TM_ExecDefinitionRuns WHERE ExecDefID_fk=${ExecDefID|0} AND Duration <> -1

When executed would generate the following data in HH:MM:SS:MS