November 2, 2018 at 8:56 am
Hi,
I have a query which is slow and sometimes takes almost a minute to execute.
The query is generated from the .net application using EFF and LINQ. So i have zero control over editing the query.
Here is the query generated from application.exec sp_executesql N'SELECT TOP (@p__linq__8)
[Project2].[R_Id] AS [R_Id],
[Project2].[R_DateTime] AS [R_DateTime],
[Project2].[FormId] AS [FormId],
[Project2].[R_IncidentNumber] AS [R_IncidentNumber],
[Project2].[IncidentNumber] AS [IncidentNumber],
[Project2].[A_Code] AS [A_Code],
[Project2].[F_Class] AS [F_Class],
[Project2].[AgencyPhone] AS [AgencyPhone],
[Project2].[AgencyCounty] AS [AgencyCounty],
[Project2].[AgencyIsMsp] AS [AgencyIsMsp],
[Project2].[AgencyDistrictId] AS [AgencyDistrictId],
[Project2].[County] AS [County],
[Project2].[CityTwp] AS [CityTwp],
[Project2].[Institution] AS [Institution],
[Project2].[ReportDate] AS [ReportDate],
[Project2].[ReportTime] AS [ReportTime],
[Project2].[SubUnit] AS [SubUnit],
[Project2].[S_Number] AS [S_Number],
[Project2].[B_Number] AS [B_Number],
[Project2].[I_Status] AS [I_Status],
[Project2].[Reviewed] AS [Reviewed],
[Project2].[IncidentName] AS [IncidentName],
[Project2].[NatureOfIncident] AS [NatureOfIncident],
[Project2].[InvestBy] AS [InvestBy],
[Project2].[Disposed] AS [Disposed],
[Project2].[Property] AS [Property],
[Project2].[PropertyDescriptions] AS [PropertyDescriptions],
[Project2].[Forfeiture] AS [Forfeiture],
[Project2].[C_Number] AS [C_Number],
[Project2].[DateOccurred] AS [DateOccurred],
[Project2].[TimeOccurred] AS [TimeOccurred],
[Project2].[F_Date] AS [F_Date],
[Project2].[Is_Offline] AS [Is_Offline],
[Project2].[IsSupplement] AS [IsSupplement],
[Project2].[P_OfficerId] AS [P_OfficerId],
[Project2].[S_Level] AS [S_Level],
[Project2].[I_DetailPageId] AS [I_DetailPageId],
[Project2].[Is_M] AS [Is_M],
[Project2].[S_Officer] AS [S_Officer],
[Project2].[InvestigatedByUserId] AS [InvestigatedByUserId],
[Project2].[S_OfficerUserId] AS [S_OfficerUserId]
FROM ( SELECT [Project2].[R_Id] AS [R_Id], [Project2].[R_DateTime] AS [R_DateTime], [Project2].[I_DetailPageId] AS [I_DetailPageId], [Project2].[FormId] AS
[FormId], [Project2].[R_IncidentNumber] AS [R_IncidentNumber], [Project2].[Is_M] AS [Is_M], [Project2].[IncidentNumber] AS [IncidentNumber], [Project2].[A_Code] AS [A_Code], [Project2].[F_Class] AS [F_Class], [Project2].[AgencyPhone] AS [AgencyPhone], [Project2].[AgencyCounty] AS [AgencyCounty], [Project2].[AgencyIsMsp] AS [AgencyIsMsp], [Project2].[AgencyDistrictId] AS [AgencyDistrictId], [Project2].[County] AS [County], [Project2].[CityTwp] AS [CityTwp], [Project2].[Institution] AS [Institution], [Project2].[ReportDate] AS [ReportDate], [Project2].[ReportTime] AS [ReportTime], [Project2].[SubUnit] AS [SubUnit], [Project2].[S_Number] AS [S_Number], [Project2].[B_Number] AS [B_Number], [Project2].[I_Status] AS [I_Status], [Project2].[Reviewed] AS [Reviewed], [Project2].[IncidentName] AS [IncidentName], [Project2].[NatureOfIncident] AS [NatureOfIncident], [Project2].[InvestBy] AS [InvestBy], [Project2].[S_Officer] AS [S_Officer], [Project2].[InvestigatedByUserId] AS [InvestigatedByUserId], [Project2].[S_OfficerUserId] AS [S_OfficerUserId], [Project2].[Disposed] AS [Disposed], [Project2].[Property] AS [Property], [Project2].[PropertyDescriptions] AS [PropertyDescriptions], [Project2].[Forfeiture] AS [Forfeiture], [Project2].[C_Number] AS [C_Number], [Project2].[DateOccurred] AS [DateOccurred], [Project2].[TimeOccurred] AS [TimeOccurred], [Project2].[F_Date] AS [F_Date], [Project2].[Is_Offline] AS [Is_Offline], [Project2].[IsSupplement] AS [IsSupplement], [Project2].[P_OfficerId] AS [P_OfficerId], [Project2].[S_Level] AS [S_Level], row_number() OVER (ORDER BY [Project2].[A_Code] ASC, [Project2].[R_IncidentNumber] ASC, [Project2].[S_Number] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[R_Id] AS [R_Id],
[Extent1].[R_DateTime] AS [R_DateTime],
[Extent1].[I_DetailPageId] AS [I_DetailPageId],
[Extent1].[FormId] AS [FormId],
[Extent1].[R_IncidentNumber] AS [R_IncidentNumber],
[Extent1].[Is_M] AS [Is_M],
[Extent1].[IncidentNumber] AS [IncidentNumber],
[Extent1].[A_Code] AS [A_Code],
[Extent1].[F_Class] AS [F_Class],
[Extent1].[AgencyPhone] AS [AgencyPhone],
[Extent1].[AgencyCounty] AS [AgencyCounty],
[Extent1].[AgencyIsMsp] AS [AgencyIsMsp],
[Extent1].[AgencyDistrictId] AS [AgencyDistrictId],
[Extent1].[County] AS [County],
[Extent1].[CityTwp] AS [CityTwp],
[Extent1].[Institution] AS [Institution],
[Extent1].[ReportDate] AS [ReportDate],
[Extent1].[ReportTime] AS [ReportTime],
[Extent1].[SubUnit] AS [SubUnit],
[Extent1].[S_Number] AS [S_Number],
[Extent1].[B_Number] AS [B_Number],
[Extent1].[I_Status] AS [I_Status],
[Extent1].[Reviewed] AS [Reviewed],
[Extent1].[IncidentName] AS [IncidentName],
[Extent1].[NatureOfIncident] AS [NatureOfIncident],
[Extent1].[InvestBy] AS [InvestBy],
[Extent1].[S_Officer] AS [S_Officer],
[Extent1].[InvestigatedByUserId] AS [InvestigatedByUserId],
[Extent1].[S_OfficerUserId] AS [S_OfficerUserId],
[Extent1].[Disposed] AS [Disposed],
[Extent1].[Property] AS [Property],
[Extent1].[PropertyDescriptions] AS [PropertyDescriptions],
[Extent1].[Forfeiture] AS [Forfeiture],
[Extent1].[C_Number] AS [C_Number],
[Extent1].[DateOccurred] AS [DateOccurred],
[Extent1].[TimeOccurred] AS [TimeOccurred],
[Extent1].[F_Date] AS [F_Date],
[Extent1].[Is_Offline] AS [Is_Offline],
[Extent1].[IsSupplement] AS [IsSupplement],
[Extent1].[P_OfficerId] AS [P_OfficerId],
[Extent1].[S_Level] AS [S_Level]
FROM (SELECT
[SearchModel].[R_Id] AS [R_Id],
[SearchModel].[R_DateTime] AS [R_DateTime],
[SearchModel].[I_DetailPageId] AS [I_DetailPageId],
[SearchModel].[FormId] AS [FormId],
[SearchModel].[R_IncidentNumber] AS [R_IncidentNumber],
[SearchModel].[Is_M] AS [Is_M],
[SearchModel].[IncidentNumber] AS [IncidentNumber],
[SearchModel].[A_Code] AS [A_Code],
[SearchModel].[F_Class] AS [F_Class],
[SearchModel].[AgencyPhone] AS [AgencyPhone],
[SearchModel].[AgencyCounty] AS [AgencyCounty],
[SearchModel].[AgencyIsMsp] AS [AgencyIsMsp],
[SearchModel].[AgencyDistrictId] AS [AgencyDistrictId],
[SearchModel].[County] AS [County],
[SearchModel].[CityTwp] AS [CityTwp],
[SearchModel].[Institution] AS [Institution],
[SearchModel].[ReportDate] AS [ReportDate],
[SearchModel].[ReportTime] AS [ReportTime],
[SearchModel].[SubUnit] AS [SubUnit],
[SearchModel].[S_Number] AS [S_Number],
[SearchModel].[B_Number] AS [B_Number],
[SearchModel].[I_Status] AS [I_Status],
[SearchModel].[Reviewed] AS [Reviewed],
[SearchModel].[IncidentName] AS [IncidentName],
[SearchModel].[NatureOfIncident] AS [NatureOfIncident],
[SearchModel].[InvestBy] AS [InvestBy],
[SearchModel].[S_Officer] AS [S_Officer],
[SearchModel].[InvestigatedByUserId] AS [InvestigatedByUserId],
[SearchModel].[S_OfficerUserId] AS [S_OfficerUserId],
[SearchModel].[Disposed] AS [Disposed],
[SearchModel].[Property] AS [Property],
[SearchModel].[PropertyDescriptions] AS [PropertyDescriptions],
[SearchModel].[Forfeiture] AS [Forfeiture],
[SearchModel].[C_Number] AS [C_Number],
[SearchModel].[DateOccurred] AS [DateOccurred],
[SearchModel].[TimeOccurred] AS [TimeOccurred],
[SearchModel].[F_Date] AS [F_Date],
[SearchModel].[Is_Offline] AS [Is_Offline],
[SearchModel].[IsSupplement] AS [IsSupplement],
[SearchModel].[P_OfficerId] AS [P_OfficerId],
[SearchModel].[S_Level] AS [S_Level]
FROM [dbo].[SearchModel] AS [SearchModel]) AS [Extent1]
LEFT OUTER JOIN [dbo].[I_DetailsPages] AS [Extent2] ON [Extent1].[I_DetailPageId] = [Extent2].[Id]
WHERE ((N''Public'' = [Extent1].[S_Level]) OR (N''Private'' = [Extent1].[S_Level]) OR ([Extent1].[P_OfficerId] = @p__linq__0) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[I_DetailsPages] AS [Extent3]
INNER JOIN (SELECT
[Permissions].[Id] AS [Id],
[Permissions].[A_Code] AS [A_Code],
[Permissions].[UserId] AS [UserId]
FROM [dbo].[Permissions] AS [Permissions]) AS [Extent4] ON [Extent3].[FormId] = [Extent4].[Id]
WHERE ([Extent1].[I_DetailPageId] = [Extent3].[Id]) AND ([Extent4].[UserId] = @p__linq__1) AND ([Extent4].[A_Code] = @p__linq__2)
))) AND ([Extent1].[R_DateTime] >= @p__linq__3) AND ([Extent1].[R_DateTime] <= @p__linq__4) AND ([Extent1].[A_Code] = @p__linq__5) AND (([Extent1].[I_Status] = @p__linq__6) OR (([Extent1].[I_Status] IS NULL) AND (@p__linq__6 IS NULL))) AND ([Extent1].[Is_Offline] <> 1) AND ([Extent1].[IsSupplement] <> 1)
) AS [Project2]
) AS [Project2]
WHERE [Project2].[row_number] > @p__linq__7
ORDER BY [Project2].[A_Code] ASC, [Project2].[R_IncidentNumber] ASC, [Project2].[S_Number] ASC',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__6 varchar(8000),@p__linq__7 int,@p__linq__8 int',@p__linq__0=90495,@p__linq__1=90495,@p__linq__2='MI3300568',@p__linq__3='2017-11-01 00:00:00',@p__linq__4='2018-11-01 23:59:00',@p__linq__5='MI3300568',@p__linq__6='0 - OPEN',@p__linq__7=0,@p__linq__8=100
Here is the execution plan.
Execution plan
The query works fast when AND (([Extent1].[IncidentStatus] = @p__linq__6) OR (([Extent1].[IncidentStatus] IS NULL) AND (@p__linq__6 IS NULL))) is not there.When the user includes IncidentStatus as a search criteria then the query is slow.
I have been trying to find out the issue whole day yesterday,but no luck.
In the execution plan there are few index seek which are bit costly,but without the IncidentStatus search criteria also those Index seek exist and the query is fast then.
Also those index seek are on a table which has only Id(Primary Key) and another column.I am not sure why those index seeks costly even though there is clustered and non-clustered index on that table.
Any help will be much appreciated.
Thanks
November 2, 2018 at 10:09 am
Do you have the create table and create index scripts?
Which indexes and in what fieldorder are those on [dbo].[IncidentFormSearchModel] ?
Does your used index include IncidentStatus?
What version of Entity Framework (6 allows manual queries, maybe also possible in 5?)
November 2, 2018 at 11:28 am
Hi,
[dbo].[IncidentFormSearchModel] is a view created from couple of tables.
And IncidentStatus column comes from another view and it is joined to [IncidentFormSearchModel] view.
Yeah,the IncidentStatus column in view is indexed(Non clustured index) in the table from which the view is created.
Changing code is not an option at the moment,i talked to application team regarding optimizing query,but it is not an option now.
This is the [dbo].[IncidentFormSearchModel] view.
CREATE VIEW [dbo].[SearchModel] AS
SELECT
r.Id AS R_Id,
r.ReportDateTime AS ReportDateTime,
ISNULL(idp.Id,'00000000-0000-0000-0000-000000000000') AS IncidentDetailPageId,
ISNULL(f.Id, '00000000-0000-0000-0000-000000000000') AS FormId,
r.IncidentNumber AS R_Number,
CASE WHEN r.Is_M = 1 THEN CAST(1 AS BIT)ELSE CAST(0 AS BIT)END AS Is_M,
idph.IncidentNumber AS IncidentNumber,
ISNULL(idph.Agency, '') AS A_Code,
idph.FileClass AS F_Class,
a.PhoneNumber AS AgencyPhone,
a.County AS AgencyCounty,
ISNULL(a.IsMSP, 0) AS IsMs,
a.DistrictId AS AgencyDistrictId,
idpv.County AS County,
idpv.City AS CityTwp,
idpv.Institution AS Institution,
idph.ReportDate AS ReportDate,
idph.ReportTime AS ReportTime,
idph.SubUnit AS SubUnit,
idps.S_Number AS S_Number,
CASE WHEN idps.Id IS NOT NULL THEN idps.B_Number ELSE idpv.B_Number END AS B_Number,
cis.IncidentStatus_FieldValue AS IncidentStatus,
(CASE WHEN EXISTS(SELECT Value FROM FormAdditionalProperty fap WITH (NOLOCK)
WHERE Name = 'CurrentState' AND Value NOT IN ('1','2','3') AND fap.FormId = f.Id)
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) AS Reviewed,
idpi.IncidentName AS IncidentName,
idpi.N_Incident AS N_Incident,
CASE WHEN idps.Id IS NOT NULL THEN idps.PrimaryOfficer ELSE idpv.PrimaryOfficer END AS InvestBy,
CASE WHEN idps.Id IS NOT NULL THEN idps.S_Officer ELSE idpv.S_Officer END AS S_Officer,
CASE WHEN idps.Id IS NOT NULL THEN idps.P_OfficerUserId ELSE idpv.P_OfficerUserId END AS InvestigatedByUserId,
CASE WHEN idps.Id IS NOT NULL THEN idps.S_OfficerUserId ELSE idpv.S_OfficerUserId END AS S_OfficerUserId,
(CASE WHEN EXISTS(SELECT pph.P_Description FROM P_Pages pp WITH (NOLOCK)
LEFT JOIN PropertyPage_Header pph WITH (NOLOCK) ON (pph.PageId = pp.Id)
WHERE pp.FormId = f.Id AND pph.P_Description IN ('RELEASED', 'SOLD', 'RETAINED', 'DESTROYED')) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) AS Disposed,
ISNULL(((CASE WHEN EXISTS(SELECT Id FROM P_Pages pp WITH (NOLOCK) WHERE pp.FormId = f.Id) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END)),0) AS Property,
STUFF((SELECT ',' + pph.Description FROM PropertyPage_header pph WITH (NOLOCK)
INNER JOIN P_Pages pp ON pph.PageId = pp.Id
WHERE pp.FormId = f.Id FOR XML PATH('')), 1, 1, '') AS PropertyDescriptions,
ISNULL((CASE WHEN EXISTS(SELECT pph.Forfeiture FROM P_Pages pp WITH (NOLOCK)
LEFT JOIN PropertyPage_Header pph WITH (NOLOCK) ON (pph.PageId = pp.Id)
WHERE pp.FormId = f.Id AND pph.Forfeiture = 'YES') THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END), CAST(0 AS BIT)) AS Forfeiture,
idpi.ColdCaseNumber AS ColdCaseNumber,
idph.IncidentOnAfterDate AS DateOccurred,
idph.IncidentOnAfterTime AS TimeOccurred,
(SELECT TOP 1 Value FROM FormAdditionalProperty fap WITH (NOLOCK) WHERE fap.Name = 'FirstSubmittedOn' AND fap.FormId = f.Id) AS FirstSubmissionDate,
ISNULL((CASE WHEN r.IsOffline = 1 THEN CAST(1 AS BIT)ELSE CAST(0 AS BIT)END),0) AS IsReportOffline,
ISNULL((CASE WHEN idps.Id IS NOT NULL THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END),0) AS IsSupplement,
r.PrimaryOfficerId AS PrimaryOfficerId,
r.SecurityLevel AS SecurityLevel
FROM
DetailsPages idp WITH (NOLOCK)
LEFT JOIN
Form f WITH (NOLOCK) ON (idp.FormId = f.Id)
LEFT JOIN
Report r WITH (NOLOCK) ON (f.R_Id = r.Id)
LEFT JOIN
Agencies a WITH (NOLOCK) ON (r.AgencyOri = a.Ori)
LEFT JOIN
CurrentStatus cis WITH (NOLOCK) ON (r.Id = cis.R_Id)
LEFT JOIN
DetailsPage_Incident idpi WITH (NOLOCK) ON (idp.Id = idpi.PageId)
LEFT JOIN
DetailsPage_Header idph WITH (NOLOCK) ON (idp.Id = idph.PageId)
LEFT JOIN
DetailsPage_Venues idpv WITH (NOLOCK) ON (idp.Id = idpv.PageId)
LEFT JOIN
DetailsPage_Supplements idps WITH (NOLOCK) ON (idp.Id = idps.PageId)
GO
Here is the [dbo].[CurrentStatus] view from which the IncidentStatus column comes.
This view is joined in [dbo].[SearchModel]
CREATE VIEW [dbo].[CurrentStatus]
AS
SELECT [incidentDetails].[IncidentStatus], [incidentDetails].[IncidentStatus_FieldValue], [incidentDetails].[IncidentStatus_Description], Report.Id R_Id, Form.Id FormId
FROM
[dbo].[DetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[DetailsPages] 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.R_Id = Report.Id
LEFT OUTER JOIN dbo.DetailsPage_Supplements supplement WITH (NOLOCK)
ON detailsPage.Id = supplement.PageId
INNER JOIN
(
SELECT R_Id, Max(FormNumber) R_FormNumber FROM
(
SELECT Report.Id R_Id, FormId, COALESCE(S_Number, '0000') formNumber
FROM dbo.DetailsPages detailPage WITH (NOLOCK)
INNER JOIN Form WITH (NOLOCK)
ON detailPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
ON Form.R_Id = Report.Id
INNER JOIN dbo.DetailsPage_Incident incident WITH (NOLOCK)
ON detailPage.Id = incident.PageId
LEFT OUTER JOIN dbo.DetailsPage_Supplements supplement WITH (NOLOCK)
ON detailPage.Id = supplement.PageId) FormNumbers
GROUP By R_Id) R_Form
ON Report.Id = R_Form.R_Id AND
R_Form.R_FormNumber = COALESCE(supplement.S_Number, '0000')
GO
One thing i know is when i query [CurrentIncidentStatus] view ,it uses a HASH MATCH(Aggregate) which is costly and it shows up in the main execution plan i posted.
If that HASH MATCH (Aggregate) can be removed,it will make the query more faster i believe.
Hashing is happening on Report.Id column and i has a Clustured Index on it.
November 2, 2018 at 4:51 pm
Thats quite a complex nested view to diagnose.
Maybe it is possible to rewrite currentstatus because you have a group by there.for R_FormNumber, with probably causes the hash match. Good luck
COALESCE(S_Number, '0000') formNumber
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply