-1

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]
    FROM ( SELECT 
        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,

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)
        )) 

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?

  • Believe it or not, but you actually do have the power to change it, even before SQL Server 2016. Plan Guides can be used to change or augment queries. Since the guide is matched against specific or generic sets of query text and sources, you can do something now...just might take a while. Instead, why not talk to the developers? – clifton_h Jun 26 '18 at 4:32
  • yes that part of query is wrong, because that part of query is written wrong ,it is giving you very high cardinality estimate. You can correct it via linq-sql code itself. This is best and permanent solution. Creating any index in such query ma give you short relief. – KumarHarsh Jun 26 '18 at 10:46
  • If you want help with a query plan, post the query plan. – Erik Darling Jun 26 '18 at 16:16
  • I tried,but i get the message "The supplied XML is not a valid SQL Server query plan xml." – user9516827 Jun 26 '18 at 17:46
0

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

OR  EXISTS (SELECT 1
        FROM [dbo].[Permissions] AS [Permissions]) AS [Extent4]
where  [Extent4] condition inside exists)

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.