Description
Hello,
I am using CAP in a .NET Core project, and I would like to raise a concern regarding the SQL queries generated in the IDataStorage implementations for PostgreSQL and MSSQL. Currently, we have queries that use the OR operator, which looks like this:
PostgreSQL:
SELECT "Id", "Content", "Retries", "Added", "ExpiresAt"
FROM {_pubName}
WHERE "Version" = @Version
AND (
("ExpiresAt" < @TwoMinutesLater AND "StatusName" = '{StatusName.Delayed}')
OR
("ExpiresAt" < @OneMinutesAgo AND "StatusName" = '{StatusName.Queued}')
)
FOR UPDATE SKIP LOCKED;
MSSQL:
SELECT Id, Content, Retries, Added, ExpiresAt
FROM {_pubName} WITH (UPDLOCK, READPAST)
WHERE Version = @Version
AND (
(ExpiresAt < @TwoMinutesLater AND StatusName = '{StatusName.Delayed}')
OR
(ExpiresAt < @OneMinutesAgo AND StatusName = '{StatusName.Queued}')
);
Our DBA team has suggested replacing the OR operator with UNION in these queries to improve performance, especially when dealing with large datasets. This is due to the fact that the OR operator can sometimes lead to suboptimal index usage and degrade query performance.
Here’s how the updated queries in the IDataStorage implementations would look with UNION:
PostgreSQL:
SELECT "Id", "Content", "Retries", "Added", "ExpiresAt"
FROM {_pubName}
WHERE "Version" = @Version
AND "ExpiresAt" < @TwoMinutesLater
AND "StatusName" = '{StatusName.Delayed}'
FOR UPDATE SKIP LOCKED
UNION ALL
SELECT "Id", "Content", "Retries", "Added", "ExpiresAt"
FROM {_pubName}
WHERE "Version" = @Version
AND "ExpiresAt" < @OneMinutesAgo
AND "StatusName" = '{StatusName.Queued}'
FOR UPDATE SKIP LOCKED;
MSSQL:
SELECT Id, Content, Retries, Added, ExpiresAt
FROM {_pubName} WITH (UPDLOCK, READPAST)
WHERE Version = @Version
AND ExpiresAt < @TwoMinutesLater
AND StatusName = '{StatusName.Delayed}'
UNION ALL
SELECT Id, Content, Retries, Added, ExpiresAt
FROM {_pubName} WITH (UPDLOCK, READPAST)
WHERE Version = @Version
AND ExpiresAt < @OneMinutesAgo
AND StatusName = '{StatusName.Queued}';
This change could potentially improve the performance of the queries in the IDataStorage implementations, and I'd appreciate it if you could consider this suggestion. It would be great to see this improvement in future releases of the CAP library.
Thank you!
Best regards,
Onur