Performance Monitoring

[eanders]

Let's post some statistics about how the sample code is performing.

[eanders]

[MollyMcEvilley]

With the sample data, averages for the last 8 times I've run the LSA for 10/1/2023-9/30/2024 (6601 active clients):

Section 5_1 to 5_11 - 11 seconds
Section 6 - 37 seconds
Section 7 - 37 seconds
Section 8 - 120 seconds
Section 9 - 165 seconds

It's interesting to me that:

• Sections 6 and 7 take about the same amount of time to run for me, but section 7 takes 11 times longer than section 6 for you.
• Similarly, 8 and 9 are in the same ballpark for me and section 9 takes 9 times longer than 8 for you.
• Sections 5_1 to 5_11 runs in less than a third of the time for 6 and 7 for me but takes 4-5x longer than 6 and 7 for you.

[jbloescw]

These are the times that stick out to me for one of our data heavy customers:
7.6: 9 minutes
8.4-8.7: 14 minutes
9.3: 50 minutes
9.5.2 and 9.5.4: 3 and a half hours

I have since refactored the 9.5.2 and 9.5.4 to make them non-issues as they are super speedy now.

[MollyMcEvilley]

Linking to issue #1370 for future reference...

[MollyMcEvilley]

Not sure if this is useful to anyone else, but this is a powershell script that combines all of the LSA SQL files into a single file and adds time tracking between sections using a table called x_lsa_ReportHistory.

The create statement for the table is:

CREATE TABLE [dbo].[x_lsa_ReportHistory](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[StartTime] [datetime] NULL,
	[EndTime] [datetime] NULL,
	[Scope] [nvarchar](6) NULL,
	[ActiveClients] [int] NULL,
	[RunMinutes] [int] NULL,
	[RunSeconds] [int] NULL,
	[ReportID] [int] NOT NULL,
	[ReportDate] [datetime] NULL,
	[ReportStart] [date] NULL,
	[ReportEnd] [date] NULL,
	[notes] [nvarchar](max) NULL,
	[sec3] [int] NULL,
	[sec51] [int] NULL,
	[sec52] [int] NULL,
	[sec6] [int] NULL,
	[sec7] [int] NULL,
	[sec8] [int] NULL,
	[sec9] [int] NULL
)

And the powershell script is:

$SourcePath = "C:\(insert path to LSA scripts)\LSASampleCode\"
$FullScript = "C:\(insert path to combined file)\ExecuteLSAwithTimeDetails.sql"

# This will create the ExecuteLSA file in the event that it does not exist
Add-Content -Path $FullScript -Value "" 

# Clear contents of file
Clear-Content -Path $FullScript

$SQL = $SourcePath + "01 Temp Reporting and Reference Tables.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent 

$SQL = $SourcePath + "02 LSA Output Tables.sql" 
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent 

$SQL = $SourcePath + "03_01a LSA Parameters and Metadata.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent 

Add-Content -Path $FullScript -Value "declare @s datetime, @m int"

Add-Content -Path $FullScript -Value "insert into x_lsa_ReportHistory (Scope, StartTime, ReportID) select case when LSAScope = 3 then 'HIC' else 'LSA' end, getdate(), ReportID from lsa_Report" 
Add-Content -Path $FullScript -Value "select @m = @@identity" 

Add-Content -Path $FullScript -Value "select @s = getdate()" 
$SQL = $SourcePath + "03_02 to 03_06 HMIS Households and Enrollments.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent 

Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set sec3 = datediff(ss, @s, getdate()) where id = @m"

$SQL = $SourcePath + "04_01 Get Project Records.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  


$SQL = $SourcePath + "04_02 to 04_08 Get Other PDDEs.sql"
$sourceFileContent = Get-Content -Path $SQL
Add-Content -Path $FullScript -Value $sourceFileContent  

Add-Content -Path $FullScript -Value "select @s = getdate()" 
$SQL = $SourcePath + "05_01 to 05_11 LSAPerson Records and Demographics.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  
Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set sec51 = datediff(ss, @s, getdate()) where id = @m"

Add-Content -Path $FullScript -Value "select @s = getdate()" 
$SQL = $SourcePath + "05_12 to 05_15 LSAPerson Project Group and Population Household Types.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  
Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set sec52 = datediff(ss, @s, getdate()) where id = @m"

Add-Content -Path $FullScript -Value "select @s = getdate()" 
$SQL = $SourcePath + "06 LSAHousehold.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  
Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set sec6 = datediff(ss, @s, getdate()) where id = @m"

Add-Content -Path $FullScript -Value "select @s = getdate()" 
$SQL = $SourcePath + "07 LSAExit.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  
Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set sec7 = datediff(ss, @s, getdate()) where id = @m"

Add-Content -Path $FullScript -Value "select @s = getdate()" 
$SQL = $SourcePath + "08 LSACalculated Averages for LSAHousehold and LSAExit.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  
Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set sec8 = datediff(ss, @s, getdate()) where id = @m"

Add-Content -Path $FullScript -Value "select @s = getdate()" 
$SQL =  $SourcePath + "09 LSACalculated AHAR Counts.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  
Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set sec9 = datediff(ss, @s, getdate()) where id = @m"

$SQL =  $SourcePath + "10 LSACalculated Data Quality.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  

$SQL =  $SourcePath + "11 LSAReport DQ and ReportDate.sql"
$sourceFileContent = Get-Content -Path $SQL 
Add-Content -Path $FullScript -Value $sourceFileContent  

Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set EndTime = getdate() where id = @m"
Add-Content -Path $FullScript -Value "update x_lsa_ReportHistory set RunMinutes = datediff(mi, StartTime, EndTime), RunSeconds = datediff(ss, StartTime, EndTime)%60 where id = @m"

Add-Content -Path $FullScript -Value "update x"
Add-Content -Path $FullScript -Value "set x.ActiveClients = rpt.UnduplicatedClient, x.ReportDate = rpt.ReportDate, x.ReportStart = rpt.ReportStart, x.ReportEnd = rpt.ReportEnd"
Add-Content -Path $FullScript -Value "from x_lsa_ReportHistory x" 
Add-Content -Path $FullScript -Value "inner join lsa_Report rpt on rpt.ReportID = x.ReportID" 
Add-Content -Path $FullScript -Value "where x.id = @m"