I have a query for de duplicating messages based on unique hash and then sorting and paging it, i was wondering how I can optimize it better.
Hash and Name column in the Message table are non clustered indexes defined with storeid, folderid, Hash or Name(either)
The only thing i see problematic is an index scan but that is same even if i remove the deduplication cte and do the main query on the Message table, i think that is more to do with select *
Any help will be greatly appreciated.
With DeduplicatedMessages as
select * from Message
SELECT MAX(MessageId) as UniqueMessageId
where StoreId=210 and FolderId=24541
GROUP BY Hash
) as UniqueMessages ON
Message.Messageid = UniqueMessages.UniqueMessageId
select * from (select ROW_NUMBER() over (order by Name ASC) as RI, * from DeduplicatedMessages) as SUB where sub.RI >= 0 and sub.RI <=1000