Sql-server – Poor performance by LINQ to SQL generated query execution plan

You should look at the Execution Plan and see where most of the cost is for the query. Without the results of the Execution Plan it is difficult to offer advice.

I suspect the query optimizer is using the best process possible for the query as it is structured. You might try breaking the query into smaller parts, like query the email field to get the results into a temp table first, then run your main query using the temp table of emails.

The might not make the query run faster, but this might expose information in the Execution Plan that will help you troubleshoot.

Another option is to use include to add columns to your non-clustered index to return all the values in the index scan to maybe speed up the process.

You can view operator level CPU and elapsed time metrics in SQL Server Management Studio, although I can't say how reliable they are for queries that finish as quickly as yours. Your plan only has row mode operators so the time metrics apply to that operator as well as the operators in the subtree below it. Using the nested loop join as an example, SQL Server is telling you that entire subtree took 60 ms of CPU time and 80 ms of elapsed time:

Most of that subtree time is spent on the index seek. Index seeks take CPU too. It looks like your index has exactly the columns needed so it isn't clear how you could reduce the CPU costs of that operator. Other than the seeks most of the CPU time in the plan is spent on the hash matches which implement your joins.

This is a huge oversimplification, but the CPU taken by those hash joins is going to depend on the size of the input for the hash table and the number of rows processed on the probe side. Observing a few things about this query plan:

• At most 461 returned rows have C.[UserId] = @UserId. These rows don't care about the joins at all.
• For the rows that do need the joins, SQL Server isn't able to apply any of the filtering early (except for OU.[UserId] = @UserId).
• Nearly all of the processed rows are eliminated near the end of the query plan (reading from right to left) by the filter: [vault].[dbo].[Cipher].[UserId] as [C].[UserId]=[@UserId] OR ([vault].[dbo].[OrganizationUser].[AccessAll] as [OU].[AccessAll]=(1) OR [vault].[dbo].[CollectionUser].[CollectionId] as [CU].[CollectionId] IS NOT NULL OR [vault].[dbo].[Group].[AccessAll] as [G].[AccessAll]=(1) OR [vault].[dbo].[CollectionGroup].[CollectionId] as [CG].[CollectionId] IS NOT NULL) AND [vault].[dbo].[Cipher].[UserId] as [C].[UserId] IS NULL AND [vault].[dbo].[OrganizationUser].[Status] as [OU].[Status]=(2) AND [vault].[dbo].[Organization].[Enabled] as [O].[Enabled]=(1)

It would be more natural to write your query as a UNION ALL. The first half of the UNION ALL can include rows where C.[UserId] = @UserId and the second half can include rows where C.[UserId] IS NULL. You're already doing two index seeks on [dbo].[Cipher] (one for @UserId and one for NULL) so it seems unlikely that the UNION ALL version would be slower. Writing out the queries separately will allow you to do some of the filtering early, both on the build and the probe sides. Queries can be faster if they need to process less intermediate data.

I don't know if your version of SQL Server supports this, but if that doesn't help you could try adding a columnstore index to your query to make your hash joins eligible for batch mode. My preferred way is to create an empty table with a CCI on it and to left join to that table. Hash joins can be much more efficient when they run in batch mode compared to row mode.