CREATE TABLE entries( uuid BINARY(16) PRIMARY KEY, status VARCHAR(20), client_id VARCHAR(32), creation_date DATETIME, );
status can be NEW, IN_PROGRESS, DONE
I need one entry with status != DONE for each client_id. I want maximum 500 of such entries and with oldest creation date.
I ended with query:
SELECT TOP (500) * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY creation_date) rn FROM entries WHERE status <> 'DONE') v WHERE rn = 1
We have around 1000 clients and around 1 milion entries. Currently query executes ~90 seconds on our Azure SQL database.
Azure SQL Server suggested creating following index:
CREATE NONCLUSTERED INDEX [nci_wi_entries_0A6FBEBB67742F72F54C5F86FD2C5023] ON [dbo].[entries] ([status]) INCLUDE ([client_id], [creation_date]) WITH (ONLINE = ON)
After that, query executes around 20 seconds, but that is still too slow. I must have results in less than second.
Do you have any ideas how to optimize it (in any way, either rebuilding table or optimizing query)?