-
Notifications
You must be signed in to change notification settings - Fork 0
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Refactor Refresh Emissions Views Database Procedures #5793
Refactor Refresh Emissions Views Database Procedures #5793
Comments
CVP will not work on this ticket. DPC will complete reamaining work |
Jason, is it possible to load the Emission View data from Workspace into Official when emission reports are submitted instead of reproducing the values? This approach would need to account for the following:
The second issue would eventually require improving the efficiency of the Official process, but the need might not be as urgent. |
|
need to discuss with @ergjustin |
Related Tickets: |
Address monitor_hrly_value slowness: #6174 |
Example Potential Alternate Solution (View Emissions SO2 CEMS)As far as I can tell the pivot function method would not use indexes to join the DHV and MHV data. This might not cause an execution hit, but worries me. The following method achieves a similar result as the pivot function method, but should join using indexes. View Emissions SO2 CEMS Query
|
All involved emissions tables have been analyzed on dev and the refresh procedures are running much faster. Code has been checked for all updated procedures. |
@djw4erg to provide scripts which can be used to first clear data from affected views prior to testing. |
Testing Results
|
Handling Testing Results
|
Handling Testing Results Suggested Hourly NOxM CEMS Improved Query
Correcting the Original Problem
Suggested Replacement Query for the Hourly NOxM CEMS View Refresh
|
Related to #6324. |
The emissions view refresh procedures ported from the MS SQL client and used in the workspace do not perform well when used in the official schema due to the vast amount of data. Need to refactor the refresh procedures to be more performant overall.
There are 3 functions that pivot the derived_hrly_value, monitor_hrly_value, & hrly_param_fuel_flow tables so that the values needed for each parameter are a single row for each hour reported.
NOTE: The above functions are dynamic functions that return a dynamic list of columns based on the parameter codes passed into the function from the caller. If you add a column to be returned then anything that uses it must be updated to expected that column.
Instead of joining multiple times on derived_hrly_value, monitor_hrly_value, and/or hrly_param_fuel_flow which has 100+ million records based on different parameters. Replace all these joins with one join to the appropriate pivot functions above passing in the parameters that data is needed for.
The following have been refactored to use the pivot approach and can be used as examples...
The following had previously been refactored to use an approach that does not always work well for every case and needs to be refactored to use the pivot approach...
The following are the original ported procedures that have not been refactored at all and needs to be refactored to use the pivot approach...
The following cannot use the pivot approach but need to be looked at from a performance perspective...
NOTE: Refactor the camdecmps schema (official) procedures and test in the TEST or CDC database which has all of the production data and then copy them to the camdecmpswks schema (workspace) replacing the referenced schema from camdecmps to camdecmpswks
The following procedures are not persisted views and instead uses an actual view for the data so these refresh procedures are just refreshing the counts and should not need refactoring...
The text was updated successfully, but these errors were encountered: