Retest: Check Speed of the Streaming Service Derived Hourly End Point (in perf env)

[djw4erg]

The following endpoint call takes more than 30 seconds when using Postman and through the Swagger page.

https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/derived-values?orisCode=3&locationName=1|2|CS0AAN&beginDate=2022-05-31&endDate=2022-07-01

Based on the Hourly Op Data endpoint and the attached test query, this call should take no more than a few seconds.

• Try to determine whether the problem is primarily in querying the database, building the JSON, or transporting JSON.
• Determine whether the query produced by TypeORM is part of the problem.
• Potentially use a view or table function to improve speed.
• Investigate additional indexes to improve speed.

[djw4erg]

Rechecking Results (5/14/2024)

The speed issue still exists. I received a 30 second timeout using Postman. The following query, which should approximate the results for the endpoint call, runs in seconds. Follow the suggested path from the description.

select  fac.Oris_Code,
        fac.Facility_Name,
        coalesce( unt.Unitid, stp.Stack_Name ) as Location_Name,
        hod.Begin_Date as Op_Date,
        hod.Begin_Hour as Op_Hour,
        hod.Op_Time,
        dhv.Parameter_Cd,
        dhv.Modc_Cd,
        dhv.Adjusted_Hrly_Value,
        dhv.Unadjusted_Hrly_Value,
        dhv.Applicable_Bias_Adj_Factor,
        loc.Mon_Loc_Id,
        fac.Fac_Id
  from  camdecmps.MONITOR_LOCATION loc
        left join camd.UNIT unt
          on unt.Unit_Id = loc.Unit_Id 
        left join camdecmps.STACK_PIPE stp
          on stp.Stack_Pipe_Id = loc.Stack_Pipe_Id
        join camd.PLANT fac
          on fac.Fac_Id in ( unt.Fac_Id, stp.Fac_Id )
        join camdecmps.HRLY_OP_DATA hod
          on hod.Mon_Loc_Id = loc.Mon_Loc_Id
         and hod.Begin_Date between '2022-05-31' and '2022-07-01'
        join camdecmps.DERIVED_HRLY_VALUE dhv
          on dhv.Hour_Id = hod.Hour_Id
 where  fac.Oris_Code = 3
   and  coalesce( unt.Unitid, stp.Stack_Name ) in ( 'CS0AAN', '1', '2' )

[spetros-do]

Efforts included:

Updated db.module.ts:

• Modified SSL configuration to include ca, key, and cert options.
• Reverted changes to match closer to the original codebase while maintaining current dependencies.

Modified streaming.service.ts:

• Added more robust error handling and logging in the get Stream method.
• Implemented proper database client release in case of errors.

Updated derived-hourly.service.spec.ts:

• Refactored test setup to properly mock the DerivedHourlyRepository.
• Aligned test structure with the original file, focusing on testing the service instead of the repository.

Database optimization:

• Added indexes to improve query performance.

Troubleshooting database connection:

• Verified database connection string and configuration.
• Checked security group settings for AWS RDS (as applicable).
• Ensured proper SSL/TLS configuration for database connections.

Environment configuration:

• Verified and updated environment variables related to database connection.

Caching and build optimization:

• Cleared Jest cache to resolve potential testing issues.
• Run tests with verbose output for better diagnostics.

Code refactoring:

• Simplified SSL options in db.module.ts for non-localhost connections.
• Removed custom types and interfaces that were not in the original version.

Error investigation:

• Checked database permissions and settings.

Postman Current Output:

[lgiannini1]

Verified on dev. Making/streaming-services/emissions/hourly/derived-values API endpoint calls on both the swagger page and postman took no longer than 15 seconds

[ibarra-michelle]

FYI @spetros-do , @djw4erg , and @lgiannini1:

Unable to verify performance improvements in test for ORIS 3, Location 1,2,CS0AAN and ORIS 470 Location 1.

For ORIS 3, Location 1,2,CS0AAN, the swagger page took 17-seconds and the postman took 19-seconds to respond successfully (see screenshot 1 below).

For ORIS 470 Location 1, the swagger page took 22-seconds to respond successfully and the postman timed out taking greater than 30-seconds (see screenshot 2 below).

Screenshot 1

Screenshot 2

[spetros-do]

@ibarra-michelle -- noted with thanks. I will investigate underlying cause and revert back to the team. @djw4erg @lgiannini1

[alangmaid]

blocked by #6325