FIX: [PostgreSQL] Improve query performance of FeedPackageVersions_RecordDownloadBulk
Created:
8/22/2025 5:31:50 PM by Rich H
Status:
Open
Last pulled:
8/25/2025 8:26:26 PM
Description:
```
CREATE OR REPLACE PROCEDURE public."FeedPackageVersions_RecordDownloadBulk"(IN "@Downloads_Table" jsonb)
LANGUAGE 'sql'
BEGIN ATOMIC
with src as (
SELECT "Feed_Id", "PackageVersion_Id", "PackageName_Id", "IncrementBy_Count", "LastDownloaded_Date"
FROM jsonb_to_recordset("FeedPackageVersions_RecordDownloadBulk"."@Downloads_Table") AS v("Feed_Id" integer, "PackageVersion_Id" integer, "PackageName_Id" integer, "IncrementBy_Count" integer, "LastDownloaded_Date" timestamp with time zone)
), upd as (
UPDATE "FeedPackageVersionStats" fpvs SET "LastDownloaded_Date" = d."LastDownloaded_Date", "Download_Count" = (fpvs."Download_Count" + d."IncrementBy_Count")
FROM src AS d
WHERE fpvs."Feed_Id" = d."Feed_Id" AND fpvs."PackageVersion_Id" = d."PackageVersion_Id"
) UPDATE "FeedPackages" fp SET "Total_Download_Count" = (fp."Total_Download_Count" + d."IncrementBy_Count")
FROM src d
WHERE fp."Feed_Id" = d."Feed_Id" AND fp."PackageName_Id" = d."PackageName_Id";
END;
```