I already received an answer on similar question, but it's not applicable in the case below.
In last time, I just rewrited my query on two distinct, first was fetching first N rows from one table and then it was joined (if and only if it contains sufficient amount of data). But here there is enough of data, but query still be very slow. Here is simplified example of slow query:
DECLARE @Top int = 1000
SELECT TOP (@Top) *
FROM (
SELECT [t0].[DateTimeUtc] AS [value], [t2].[SystemName], [t2].[Name], [t0].[Id], [t1].[Discriminator], [t1].[ParentActionTemplateId], [t0].[DateTimeUtc], [t1].[Id] AS [Id2]
FROM [directcrm].[CustomerActions] AS [t0]
INNER JOIN [directcrm].[ActionTemplates] AS [t1] ON [t1].[Id] = [t0].[ActionTemplateId]
LEFT OUTER JOIN [directcrm].[ActionTemplates] AS [t2] ON [t2].[Id] = [t1].[ParentActionTemplateId]
) AS [t7]
WHERE (([t7].[Discriminator] = @p9) OR ([t7].[Discriminator] = @p10) OR ([t7].[Discriminator] = @p11) OR ([t7].[Discriminator] = @p12)) AND ([t7].[ParentActionTemplateId] IS NOT NULL) AND (([t7].[DateTimeUtc]) <= @p13) AND (([t7].[DateTimeUtc]) > @p14) AND ([t7].[Id] > @p15)
ORDER BY [t7].[Id]
But if we hack optimizer:
DECLARE @Top int = 1000
SELECT TOP (@Top) *
FROM (
SELECT [t0].[DateTimeUtc] AS [value], [t2].[SystemName], [t2].[Name], [t0].[Id], [t1].[Discriminator], [t1].[ParentActionTemplateId], [t0].[DateTimeUtc], [t1].[Id] AS [Id2]
FROM [directcrm].[CustomerActions] AS [t0]
INNER JOIN [directcrm].[ActionTemplates] AS [t1] ON [t1].[Id] = [t0].[ActionTemplateId]
LEFT OUTER JOIN [directcrm].[ActionTemplates] AS [t2] ON [t2].[Id] = [t1].[ParentActionTemplateId]
) AS [t7]
WHERE (([t7].[Discriminator] = @p9) OR ([t7].[Discriminator] = @p10) OR ([t7].[Discriminator] = @p11) OR ([t7].[Discriminator] = @p12)) AND ([t7].[ParentActionTemplateId] IS NOT NULL) AND (([t7].[DateTimeUtc]) <= @p13) AND (([t7].[DateTimeUtc]) > @p14) AND ([t7].[Id] > @p15)
ORDER BY [t7].[Id]
OPTION (OPTIMIZE FOR (@TOP = 100000000)) -- < here
We get an instant answer, because in first case it uses loop join which is slow as hell, in second - hash join.
Of course I rebuilded all statistics and all indices for all tables in database.
I don't know what can I do with this query, because it's just a simple join without any magic. I tried build some indices, rewrite query and so on, but optimizer always chooses the same plan and runs very slow. I'm unable to use hints because of ORM and because they seems to be really hacky. I use them to determine the bottleneck and then fix it by using DDL modifications. It usually works, but today it failed.