Coffee: Analytics Database and Queries

coffee_analysis_db_create.sql

create database ae_analysis;
GO
USE ae_analysis;
GO
DROP TABLE IF EXISTS dbo.summary;
create table dbo.summary (
	Test varchar(10) not null
  , Cycle tinyint not null
  , WorkloadID uniqueidentifier not null
  , Hostname sysname not null
  , [Workload] varchar(6) not null
  , TimeStart datetime
  , TimeEnd datetime
  , ElapsedTime_in_ms bigint
  , CountTasksRequested int
  , WorkerThreads tinyint
);
DROP TABLE IF EXISTS dbo.detail;
create table dbo.detail (
	WorkloadID uniqueidentifier not null
  , [Workload] varchar(6) not null
  , Thread int not null
  , ProcessID int not null
  , DatetimeStart datetime not null
  , Duration decimal(25,5) not null
  , ErrorCount int not null
);

coffee_analysis_distribution_metrics.sql

USE ae_analysis
GO
---Histogram by Test (Combined Cycle)
DECLARE @min datetime;
DECLARE @max datetime;
DECLARE @test varchar(10) = 'ae.hsm';

SELECT @min = MIN(DateTimeStart)
     , @max = MAX(DateTimeStart)
  FROM dbo.summary smry INNER JOIN dbo.detail detl
        ON smry.WorkloadID = detl.WorkloadID
 WHERE smry.Test = @test;

;WITH cte AS (
SELECT @min AS [start_datetime]
     , Dateadd(minute, 30, @min) AS [end_datetime]
 UNION ALL
SELECT [end_datetime]
     , DATEADD(minute, 30, [end_datetime]) -- recursive member
  FROM cte
 WHERE [start_datetime] < @max -- terminator
)
SELECT dtrng.[start_datetime]
     , dtrng.[end_datetime]
     , detl.[Workload]
     , MAX(detl.Duration) AS [duration_max]
	 , MIN(detl.Duration) AS [duration_min]
	 , AVG(detl.Duration) AS [duration_avg]
	 , STDEV(detl.Duration) AS [duration_deviation]
	 , CONVERT(VARCHAR,DATEADD(ms, SUM(detl.Duration), 0),114) AS [duration_sum]
	 , COUNT(*) AS [transaction_count]
  FROM dbo.summary smry INNER JOIN dbo.detail detl
        ON smry.WorkloadID = detl.WorkloadID
	   INNER JOIN cte dtrng
        ON detl.DateTimeStart BETWEEN dtrng.[start_datetime] AND dtrng.[end_datetime]
 WHERE smry.Test = @test
   AND 
 GROUP BY [start_datetime], [end_datetime], detl.[Workload]
 ORDER BY [start_datetime] ASC;
GO

coffee_analysis_general_metrics.sql

USE ae_analysis
GO
---General Metrics
SELECT smry.Test
     , detl.[Workload]
     , MAX(detl.Duration) AS [duration_max]
	 , MIN(detl.Duration) AS [duration_min]
	 , AVG(detl.Duration) AS [duration_avg]
	 , STDEV(detl.Duration) AS [duration_deviation]
	 , CONVERT(VARCHAR,DATEADD(ms, SUM(detl.Duration), 0),114) AS [duration_sum]
	 , COUNT(*) AS [transaction_count]
  FROM dbo.summary smry INNER JOIN dbo.detail detl
        ON smry.WorkloadID = detl.WorkloadID
 WHERE smry.Hostname != 'dev1-qc-app01'
 GROUP BY smry.Test, detl.[Workload]
 ORDER BY smry.Test, detl.[Workload]
GO

coffee_clean_results_detail.ps1

$file_mask = 'test_detail_*.csv'

$files = Get-ChildItem -Path $file_mask -Recurse
$datetime_style = [System.Globalization.DateTimeStyles]::RoundtripKind

foreach ($file in $files) {
	Write-Host "Processing ${file}..."
	$transactions = Import-Csv -Path $file.Fullname -Delimiter "`t"

	foreach ($transaction in $transactions) {
		$dur = [System.datetime]$transaction.Duration
		$ts = New-Object 'System.Timespan' -ArgumentList 0, $dur.Hour, $dur.Minute, $dur.Second, $dur.Millisecond
		$transaction.Duration = $ts.TotalMilliseconds

		$transaction.TimeStart = `
			[System.DateTime]::Parse($transaction.TimeStart, $null, $datetime_style);
		
	}
	
	$transactions | Export-Csv -Path 'C:\Users\afortu639\Desktop\ae_analysis\Jul-04\detail.csv' `
		-NoTypeInformation `
		-Encoding ASCII `
		-Delimiter "`t" `
		-Append
}

coffee_load_results_detail.ps1

## Process Detail Records
$files_detail = Get-ChildItem -Path . -Recurse -Filter test_detail_*.csv
foreach ($file in $files_detail) {
	$content = Get-Content $file.Fullname -ReadCount 0
	$content = $content -replace '"', ''
	Add-Content .\details.csv -Value $($content[1..$($content.length -1)])
}
bcp.exe "ae_analysis.dbo.detail" in "details.csv" -S"(local)" -T -k -c -q -t"\t" -h "tablock" -e details.err -b 250000
Remove-Item -Path .\details.csv, .\details.err

coffee_load_results_summary.ps1

## Process Summary Records
$files_detail = Get-ChildItem -Path . -Recurse -Filter test_summary_*.csv
foreach ($file in $files_detail) {
	$test, $cycle = $($file.directoryname | Split-Path -Leaf).Split('-')
	$updates = @()
	$content = Get-Content $file.Fullname -ReadCount 0
	$content = $content -replace '"', ''
	foreach ($line in $content[1..$($content.length -1)]) {
		$updates += "{0}`t{1}`t{2}" -f $test, $cycle, $line
	}
	Add-Content .\summary.csv -Value $updates
}
bcp.exe "ae_analysis.dbo.summary" in "summary.csv" -S"(local)" -T -k -c -q -t"\t" -h "tablock" -e summary.err -b 20
Remove-Item -Path .\summary.csv, .\summary.err