The purpose of this PR is to improve the performance of the queries that load satellites by reducing the number of records scanned.
In the previous version (before this PR) of the MERGE statements that load satellites, we were doing multiple accesses to the satellite table (in satellite_staging_affected_queries CTE). On top of that, we were also doing a window function to calculate the record end timestamp, when it is possible to achieve the same result without the window function.
In this new version of the code, I'm:
Pre-filter the satellite in a new CTE (filtered_satellite)
Apply an LEFT JOIN with the filtered satellite earlier (directly on the filtered_staging CTE instead of the staging_satellite_affected_records)
Use DATEADD(nanosecond, -1, staging.r_timestamp) as the expression to calculate the r_timestamp_end, instead of the previous expression using a window function (LEAD)
dlouseiro
commented
1 year ago
The purpose of this PR is to improve the performance of the queries that load satellites by reducing the number of records scanned.
In the previous version (before this PR) of the
MERGEstatements that load satellites, we were doing multiple accesses to the satellite table (insatellite_staging_affected_queriesCTE). On top of that, we were also doing a window function to calculate the record end timestamp, when it is possible to achieve the same result without the window function.In this new version of the code, I'm:
filtered_satellite)LEFT JOINwith the filtered satellite earlier (directly on thefiltered_stagingCTE instead of thestaging_satellite_affected_records)DATEADD(nanosecond, -1, staging.r_timestamp)as the expression to calculate ther_timestamp_end, instead of the previous expression using a window function (LEAD)