The query use in the GetMessagesOrderedAsync uses an in with string concatenation and its send to the database with all ids in the where statement. This is causing MS SQL to use considerable resources (specially memory). These queries seems to be normally the same with a lot of literal values.
Use a table value parameter to optimize the query and replace the literal values by a parameter.
The query use in the GetMessagesOrderedAsync uses an in with string concatenation and its send to the database with all ids in the where statement. This is causing MS SQL to use considerable resources (specially memory). These queries seems to be normally the same with a lot of literal values.
Use a table value parameter to optimize the query and replace the literal values by a parameter.
https://github.com/Farfetch/kafkaflow-retry-extensions/blob/main/src/KafkaFlow.Retry.SqlServer/Repositories/RetryQueueItemMessageRepository.cs#L48
https://www.sqlshack.com/table-valued-parameters-in-sql-server/