When generating the unvalidated Progress Note report, you may encounter the following error:
User ID:
Error Time: 16 June 2023, 14:11:03
Error No: 500-100 Internal Server Error
Category: (0x8004E002)
Description:
File: /rio/Reports/RiOReports.asp
This error occurs because the report is taking too long to run.
📌Note: Reports are local configuration and are the responsibility of your organisation to maintain.
Resolution
Please raise this issue with the team who maintain your reports. Notes for your report team.
Pulling Unvalidated Progress Notes from a table containing tens of thousands of notes is the longest part of the report.
To improve performance, use an optimised query that retrieves unvalidated notes efficiently.
Example query
The following query was tested on a loaded customer system and retrieved 3 months of unvalidated notes for a user in 2–3 seconds. Performance may vary depending on:
Size of the Progress Note tables.
Reporting period
User
Declare @StartDate DateTime = 'FromDate', @EndDate DateTime = 'ToDate', @User CodeType= 'UserID'; WITH CTE_DateRangeNotes (NoteNum, UserId, VerifyUserID) AS ( SELECT NoteNum, UserId,VerifyUserID FROM PrgProgressNote pn (NOLOCK) WHERE PN.DateAndTime >= @startdate AND PN.DateAndTime <= @endDate ), CTE_UnvalidatedNotes(NoteNum) AS ( SELECT NoteNum FROM CTE_DateRangeNotes WHERE VerifyUserID IS NULL and UserID = @User EXCEPT SELECT NoteNum FROM CTE_DateRangeNotes WHERE VerifyUserID IS NOT NULL and UserID = @User ) SELECT * FROM CTE_UnvalidatedNotes UN INNER JOIN dbo.PrgProgressNote PN ON UN.NoteNum = PN.NoteNum
Progress Note table Indexes
Index_name | index_keys |
IX_PrgProgressNote_AdmissionID | AdmissionID |
IX_PrgProgressNote_ClientNotenumSubnum | ClientID, NoteNum, SubNum |
IX_PrgProgressNote_DateAndTime | DateAndTime |
IX_PrgProgressNote_EnterDateTime_AutoSaved | EnterDatetime, AutoSaved |
IX_PrgProgressNote_NoteNum NoteNum | SubNum |
IX_PrgProgressNote_NotenumEnteredInError | NoteNum |
IX_PrgProgressNote_ReferralID | ReferralID |
PK_PrgProgressNote | SequenceID |