Index Seeks cost of OR operator

Posted on

Question :

I have a query which is slow and it takes 15 seconds to execute.
The query fetches data from a view and in the where clause, I have few conditions which includes OR and AND.

exec sp_executesql N'SELECT 
    [GroupBy1].[A1] AS [C1]
        COUNT(1) AS [A1]
        FROM  (SELECT 
    [Searchview].[ReportId] AS [ReportId], 
    [Searchview].[ReportDateTime] AS [ReportDateTime], 
    [Searchview].[SearchDetailPageId] AS [SearchDetailPageId], 
    [Searchview].[FormId] AS [FormId], 
    [Searchview].[ReportSearchActivityNumber] AS [ReportSearchActivityNumber], 
    [Searchview].[IsMed] AS [IsMed], 
    [Searchview].[SearchActivityNumber] AS [SearchActivityNumber], 
    [Searchview].[AgentId] AS [AgentId], 
    [Searchview].[FileCode] AS [FileCode], 
    [Searchview].[AgentPhone] AS [AgentPhone], 
    [Searchview].[AgentCounty] AS [AgentCounty], 
    [Searchview].[AgentIsMed] AS [AgentIsMed], 
    [Searchview].[AgentDistrictId] AS [AgentDistrictId], 
    [Searchview].[County] AS [County], 
    [Searchview].[City] AS [City], 
    [Searchview].[Institution] AS [Institution], 
    [Searchview].[ReportDate] AS [ReportDate], 
    [Searchview].[ReportTime] AS [ReportTime], 
    [Searchview].[SubUnit] AS [SubUnit], 
    [Searchview].[SupplementNumber] AS [SupplementNumber], 
    [Searchview].[AccessNumber] AS [AccessNumber], 
    [Searchview].[SearchStatus] AS [SearchStatus], 
    [Searchview].[Reviewed] AS [Reviewed], 
    [Searchview].[SearchName] AS [SearchName], 
    [Searchview].[NatureOfSearch] AS [NatureOfSearch], 
    [Searchview].[InvestBy] AS [InvestBy], 
    [Searchview].[SecondaryAgent] AS [SecondaryAgent], 
    [Searchview].[Disposed] AS [Disposed], 
    [Searchview].[Property] AS [Property], 
    [Searchview].[PropertyDescriptions] AS [PropertyDescriptions], 
    [Searchview].[Forfeiture] AS [Forfeiture], 
    [Searchview].[ColdCaseNumber] AS [ColdCaseNumber], 
    [Searchview].[DateOccurred] AS [DateOccurred], 
    [Searchview].[TimeOccurred] AS [TimeOccurred], 
    [Searchview].[FirstSubmissionDate] AS [FirstSubmissionDate], 
    [Searchview].[IsReportOffline] AS [IsReportOffline], 
    [Searchview].[IsSupplement] AS [IsSupplement], 
    [Searchview].[PrimaryAgentId] AS [PrimaryAgentId], 
    [Searchview].[SecurityLevel] AS [SecurityLevel]
    FROM [dbo].[Searchview] AS [Searchview]) AS [Extent1]
        LEFT OUTER JOIN [dbo].[SerachDetailsPages] AS [Extent2] ON [Extent1].[SearchDetailPageId] = [Extent2].[Id]
        WHERE ((N''Public'' = [Extent1].[SecurityLevel]) OR (N''Private'' = [Extent1].[SecurityLevel]) OR ([Extent1].[PrimaryAgentId] = @p__linq__0) 
        OR ( EXISTS (SELECT 
            1 AS [C1]
            FROM  [dbo].[SerachDetailsPages] AS [Extent3]
            INNER JOIN (SELECT 
    [Permissions].[Id] AS [Id], 
    [Permissions].[AgentId] AS [AgentId], 
    [Permissions].[UserId] AS [UserId]
    FROM [dbo].[Permissions] AS [Permissions]) AS [Extent4] ON [Extent3].[FormId] = [Extent4].[Id]
            WHERE ([Extent1].[SearchDetailPageId] = [Extent3].[Id]) AND ([Extent4].[UserId] = @p__linq__1) AND ([Extent4].[AgentId] = @p__linq__2)
        AND ([Extent1].[ReportDateTime] >= @p__linq__3) AND ([Extent1].[ReportDateTime] <= @p__linq__4) AND ([Extent1].[AgentId] = @p__linq__5) AND ([Extent1].[IsReportOffline] <> 1) 
        AND ([Extent1].[IsSupplement] <> 1)
    )  AS [GroupBy1] ',N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 varchar(8000),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 varchar(8000)
    ',@p__linq__0=9049,@p__linq__1=9049,@p__linq__2='330068',@p__linq__3='2010-06-25 00:00:00',@p__linq__4='2011-06-25 23:59:00',@p__linq__5='330068'

when i check the execution plan,i can see some index seeks(clustered) are very high(98%) and the SentryOne plan explorer shows that those Index seeks are the top operations are those operators costs are high.

As per my analysis those index seeks comes from the permissions view and whenever i comment the below part from the query,

            1 AS [C1]
            FROM  [dbo].[SerachDetailsPages] AS [Extent3]
            INNER JOIN (SELECT 
    [Permissions].[Id] AS [Id], 
    [Permissions].[AgentId] AS [AgentId], 
    [Permissions].[UserId] AS [UserId]
    FROM [dbo].[Permissions] AS [Permissions]) AS [Extent4] ON [Extent3].[FormId] = [Extent4].[Id]
            WHERE ([Extent1].[SearchDetailPageId] = [Extent3].[Id]) AND ([Extent4].[UserId] = @p__linq__1) AND ([Extent4].[AgentId] = @p__linq__2)

it runs fast.

The options I have is to change the query. But since the query is generating from the .net application(linq-sql) I don’t have control over it.
Can anyone suggest any feasible solutions.
The Permissions view is a union of few tables in inner join.
When I run the commented part of the query alone it is fast and Index seeks cost are less.

I created a non clustered index for the primary key where index seek(clustered) is high(98%),but still the plan uses the same pk clustered index.

I came across an interesting article while I was trying to figure out this issue.
When I comment out the mentioned part on the query and then it runs fast and I can see query is using parallel processing in execution plan. So I guess that part of query which I commented out is forcing serialization which results in long execution time.
Is that part of query is something which can cause serialization? Any idea?

Answer :

Currently it takes 15 seconds or getting index seek because you have less records now.

[Extent1] condition should be written here

FROM [dbo].[Searchview] AS [Searchview] where [Searchview].[SecurityLevel] in('Private','Public') and so on)

I think it will improve cardinality estimate.

If view return int id in place of ‘Private’,’Public’ then its better.

In OR ( EXISTS (SELECT part you do not need [dbo].[SerachDetailsPages] again

        FROM [dbo].[Permissions] AS [Permissions]) AS [Extent4]
where  [Extent4] condition inside exists)

Lastly if you need only count then why mention so many columns.

Finally i was able to improve the performance by 80% after a long analysis.

Like i mentioned in the post when i comment the block of sql query the query runs faster and in parallel mode,that was causing the slow down.
The views used in the query is pretty complex and have few inner joins and left joins,so in the execution plan the index seeks(for a simple table with two columns) and other operators were expensive.

Later when i turned my focus “why the query is not executing parallel in parallel mode” ,i got some answers.
I read this article Forcing a parallel execution plan and that helped me.

I didn’t had to force the parallel execution plan instead i had to remove “Parallelism-Inhibiting Components” from the query.
Developers used a Scalar-valued function in the query to Cast the datetime and that was the thing stopping parallel execution plan.
I removed the function and used the CAST directly in the view definition and query started running fast.

Still i have to include the logic to handle empty string in the view(that handled in the scalar-value function),but at least i got some answers.

I would suggest all those who are doing performance improvement for query to try the below step as well;
If you SQL server edition supports parallel execution, and you have enabled parallel execution in the server level or in query level;

  1. Check if the query is executing in parallel mode.
  2. If not check for the components which are forcing parallel execution.You
    can find that in the article mentioned earlier.

Leave a Reply

Your email address will not be published. Required fields are marked *