0

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan

CREATE VIEW [dbo].[CurrentIncidentStatus]
AS
SELECT [incidentDetails].[IncidentStatus], [incidentDetails].[IncidentStatus_FieldValue], [incidentDetails].[IncidentStatus_Description], Report.Id ReportId, Form.Id FormId
FROM
[dbo].[IncidentDetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[IncidentDetailsPages] detailsPage WITH (NOLOCK)
ON incidentDetails.PageId = detailsPage.Id
INNER JOIN Form WITH (NOLOCK)
ON detailsPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
ON Form.ReportId = Report.Id
LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
ON detailsPage.Id = supplement.PageId
INNER JOIN
(
    SELECT ReportId, Max(FormNumber) RecentFormNumber FROM
    (
        SELECT Report.Id ReportId, FormId, COALESCE(SupplementNumber, '0000') formNumber
        FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
        INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
        INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id 
        INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId 
        LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId) FormNumbers
        GROUP By ReportId) RecentForm
ON Report.Id = RecentForm.ReportId AND
RecentForm.RecentFormNumber = COALESCE(supplement.SupplementNumber, '0000')
GO

I analyzed the plan in Sentry Plan explorer and learned that the HASH JOIN Aggregate is the costly operation in the plan. I am trying to avoid/remove that HASH JOIN someway.? If any of you have experienced similar situation please give some suggestion.

Additional Info:

The plan says Hash key is built on Table Report and column Id. In-fact the Idcolumn is the primary key in the Report table ,so a clustered index is present also a Non-clustered index is created for Id column. Still why Hashing is required for Report.Id?

Here is the screenshot of the HASH MATCH Aggregate operation.

enter image description here

Info: When i select TOP 100 or 1000 it doesn't show HASH MATCH (Aggregate).

Thanks

  • Did you run the query in SentryOne Plan Explorer, or did you just open a plan you captured elsewhere? – Aaron Bertrand Nov 1 '18 at 16:54
  • I ran the query in SSMS and then copied that XML into SentryOne Plan Explorer. – user9516827 Nov 1 '18 at 17:02
  • Try running the query from Plan Explorer. You get a bunch of additional metrics for free and when the query runs long enough we will show you the actual distribution of costs (when we just have the XML, we can only show you the estimate, which will always stay the same no matter what happens at runtime). Sometimes this will show that the operator that was estimated to be most costly wasn't the problem in the end. – Aaron Bertrand Nov 1 '18 at 17:07
  • I tried to connect to the db server,but the security settings is not allowing me to connect to the db server.I have to contact someone and try. – user9516827 Nov 1 '18 at 17:14
  • If it's because of cross-domain, you can connect to SQL Servers in another domain with a variety of applications using the runas trick I describe here. Having the actual plan from Plan Explorer will make it much easier for people to help you, and you can even upload your .pesession file to our support forum here. – Aaron Bertrand Nov 1 '18 at 17:19
0

This is not a full answer, but I need more room than a comment allows, so...

First, you should consolidate your query - you have an unnecessary step in it:

INNER JOIN (
    SELECT Report.Id ReportId, Max(COALESCE(SupplementNumber, '0000')) RecentFormNumber
    FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
    INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
    INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id 
    INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId 
    LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId
    GROUP By Report.Id
    ) RecentForm

Second, you should get rid of the redundant use of the Report table, ending up with a query like this:

CREATE VIEW [dbo].[CurrentIncidentStatus]
AS
SELECT [incidentDetails].[IncidentStatus], 
    [incidentDetails].[IncidentStatus_FieldValue], 
    [incidentDetails].[IncidentStatus_Description], 
    Report.Id ReportId, 
    Form.Id FormId
FROM [dbo].[IncidentDetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[IncidentDetailsPages] detailsPage WITH (NOLOCK)
    ON incidentDetails.PageId = detailsPage.Id
INNER JOIN Form WITH (NOLOCK)
    ON detailsPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
    ON Form.ReportId = Report.Id
LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
    ON detailsPage.Id = supplement.PageId
INNER JOIN (
    SELECT Report.Id ReportId, Max(COALESCE(SupplementNumber, '0000')) RecentFormNumber
    FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
    INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
    INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id 
    INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId 
    LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId
    GROUP By Report.Id
    ) RecentForm
    ON Form.ReportId = RecentForm.ReportId 
    AND RecentForm.RecentFormNumber = COALESCE(supplement.SupplementNumber, '0000')
GO

With these simplifications, please check the query plan again.

  • I did consolidate the query like you mentioned,but the plan still has HASH MATCH (Aggregate).I know that HASH MATCH is done on Report.Id but i don't understand why it is doing HASH Match even though there is an index for Id. – user9516827 Nov 1 '18 at 17:01

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.