本文介紹了SQL 查詢運行速度比存儲過程快的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
我最近在生產(chǎn)服務(wù)器上修改了一個存儲過程，它開始表現(xiàn)不佳，但是當(dāng)我獨立運行查詢時，它運行良好.時間差異很大，即比較 5 秒和 10 分鐘令人震驚.
I have a stored procedure which I modified recently on my production server and it started performing poorly but when I run the query independently it runs fine. The difference in timing is a lot i.e comparing 5 secs to 10 mins is alarming.
我讀了這篇文章SQL Server:查詢速度快，但從程序 并嘗試使用所有可能的解決方案解決我的問題，但存儲過程仍然不起作用，它在時間上有相同的差異.
I read this post SQL Server: Query fast, but slow from procedure and tried resolving my issue with all the possible solutions but still the stored proc does not work it is having the same difference in timings.
任何人都可以讓我知道我應(yīng)該更改什么，以便我的存儲過程至少與查詢一樣快地運行?我對 SQL Server 的這種行為感到有些驚訝
Can anyone let me know what should I change so that my stored proc runs at least as fast as the query? I am a bit surprised by this behavior of SQL Server
好的計劃
|--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Assessment].[PK_Assessment] AS [a]), SEEK:([a].[Assessment_Id]=[@AssessmentID]) ORDERED FORWARD)
|--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(float(53),[Expr1008]-(1),0)))
|--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1012],0)))
|--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
|--Stream Aggregate(GROUP BY:([ci].[Candidate_Instance_Id]))
|--Parallelism(Gather Streams, ORDER BY:([rr].[Candidate_Instance_ID] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Candidate_Instance_ID]))
|--Sort(ORDER BY:([rr].[Candidate_Instance_ID] ASC))
| |--Clustered Index Scan(OBJECT:([IndusLynk].[dbo].[Processed_Response].[PK_Processed_Response] AS [rr]), WHERE:([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]=[@AssessmentID] AND [IndusLynk].[dbo].[Processed_Response].[Client_ID] as [rr].[Client_ID]=[@UserID]))
|--Stream Aggregate(DEFINE:([ci].[Candidate_Instance_Id]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Candidate_Instance_Id] as [ci].[Candidate_Instance_Id])))
|--Nested Loops(Inner Join, WHERE:([IndusLynk].[dbo].[Candidate_Instance].[Instance_Status] as [ci].[Instance_Status]=[status]))
|--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate_Instance].[PK_Candidate_Instance] AS [ci]), SEEK:([ci].[Candidate_Instance_Id]=[IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]) ORDERED FORWARD)
|--Table Scan(OBJECT:(@AssessmentStatuses))
|--Table Insert(OBJECT:(@ItemDetailTable), SET:([Candidate_Id] = [IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id],[Instance_End_Time] = [IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time],[Candidate_Instance_Id] = [IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID],[Assessment_Id] = [IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID],[OverAllScore] = [Expr1030],[Section_ID] = [IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID],[AttempteCount] = [Expr1025],[CorrectCount] = [Expr1026],[Respone_Time] = [Expr1027],[Display_Name] = [Expr1032],[Display_Type_Id] = [IndusLynk].[dbo].[Question].[Display_Type_Id] as [q].[Display_Type_Id],[MaxMarks] = [Expr1033],[Item_ID] = [IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID],[Percentile] = NULL))
|--Compute Scalar(DEFINE:([Expr1033]=CONVERT_IMPLICIT(int,[Expr1031],0)))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1032]=CONVERT_IMPLICIT(varchar(100),[IndusLynk].[dbo].[Display_Type].[Display_Name] as [dt].[Display_Name],0)))
|--Sort(DISTINCT ORDER BY:([ci].[Instance_End_Time] DESC, [rr].[Candidate_Instance_ID] DESC, [c].[Candidate_Id] ASC, [rr].[Section_ID] ASC, [q].[Display_Type_Id] ASC, [Expr1030] ASC, [Expr1025] ASC, [Expr1026] ASC, [Expr1027] ASC, [dt].[Display_Name] ASC, [Expr1031] ASC, [rr].[Item_ID] ASC))
|--Compute Scalar(DEFINE:([Expr1030]=round([Expr1024],(2)), [Expr1031]=CASE WHEN [IndusLynk].[dbo].[Question].[Display_Type_Id] as [q].[Display_Type_Id]=(8) THEN [Expr1028] ELSE [Expr1029] END))
|--Stream Aggregate(GROUP BY:([rr].[Candidate_Instance_ID], [c].[First_Name], [c].[Last_Name], [rr].[Section_ID], [q].[Display_Type_Id], [rr].[Item_ID]) DEFINE:([Expr1024]=SUM([Expr1034]), [Expr1025]=SUM([Expr1035]), [Expr1026]=SUM([Expr1036]), [Expr1027]=SUM([Expr1037]), [Expr1028]=SUM([Expr1038]), [Expr1029]=MAX([Expr1039]), [ci].[Instance_End_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time]), [c].[Candidate_Id]=ANY([IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id]), [rr].[Assessment_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]), [dt].[Display_Name]=ANY([IndusLynk].[dbo].[Display_Type].[Display_Name] as [dt].[Display_Name])))
|--Compute Scalar(DEFINE:([Expr1038]=CASE WHEN isnull([IndusLynk].[dbo].[Answer].[Answer_Weightage] as [ans].[Answer_Weightage],(0.000000000000000e+000))>(-1.000000000000000e+000) THEN isnull([IndusLynk].[dbo].[Answer].[Answer_Weightage] as [ans].[Answer_Weightage],(0.000000000000000e+000)) ELSE (0.000000000000000e+000) END, [Expr1039]=isnull([IndusLynk].[dbo].[Answer].[Answer_Weightage] as [ans].[Answer_Weightage],(0.000000000000000e+000))))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([id].[Answer_Id], [Expr1052]) WITH ORDERED PREFETCH)
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([rr].[Item_ID]))
| |--Sort(ORDER BY:([rr].[Candidate_Instance_ID] ASC, [c].[First_Name] ASC, [c].[Last_Name] ASC, [rr].[Section_ID] ASC, [q].[Display_Type_Id] ASC, [rr].[Item_ID] ASC))
| | |--Stream Aggregate(GROUP BY:([rr].[Processed_Response_ID]) DEFINE:([rr].[Candidate_Instance_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]), [rr].[Assessment_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]), [rr].[Section_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID]), [rr].[Item_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID]), [ci].[Instance_End_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time]), [c].[Candidate_Id]=ANY([IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id]), [c].[First_Name]=ANY([IndusLynk].[dbo].[Candidate].[First_Name] as [c].[First_Name]), [c].[Last_Name]=ANY([IndusLynk].[dbo].[Candidate].[Last_Name] as [c].[Last_Name]), [q].[Display_Type_Id]=ANY([IndusLynk].[dbo].[Question].[Display_Type_Id] as [q].[Display_Type_Id]), [dt].[Display_Type_Id]=ANY([IndusLynk].[dbo].[Display_Type].[Display_Type_Id] as [dt].[Display_Type_Id]), [dt].[Display_Name]=ANY([IndusLynk].[dbo].[Display_Type].[Display_Name] as [dt].[Display_Name]), [Expr1034]=ANY([Expr1034]), [Expr1035]=ANY([Expr1035]), [Expr1036]=ANY([Expr1036]), [Expr1037]=ANY([Expr1037])))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([ci].[Candidate_Id]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([q].[Display_Type_Id]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([i].[Question_Id]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Item_ID]))
| | | | | |--Nested Loops(Inner Join, WHERE:([IndusLynk].[dbo].[Candidate_Instance].[Instance_Status] as [ci].[Instance_Status]=[status]))
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Candidate_Instance_ID]))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1034]=isnull([IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained],(0.000000000000000e+000)), [Expr1035]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Display_Type_ID] as [rr].[Display_Type_ID]=(2) THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted] IS NULL THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time]>(0.000000000000000e+000) THEN (1) ELSE (0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END, [Expr1036]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained]>(0.000000000000000e+000) THEN (1) ELSE (0) END, [Expr1037]=isnull([IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time],(0.000000000000000e+000))))
| | | | | | | | |--Clustered Index Scan(OBJECT:([IndusLynk].[dbo].[Processed_Response].[PK_Processed_Response] AS [rr]), WHERE:([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]=[@AssessmentID] AND [IndusLynk].[dbo].[Processed_Response].[Client_ID] as [rr].[Client_ID]=[@UserID]) ORDERED FORWARD)
| | | | | | | |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate_Instance].[PK_Candidate_Instance] AS [ci]), SEEK:([ci].[Candidate_Instance_Id]=[IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]) ORDERED FORWARD)
| | | | | | |--Table Scan(OBJECT:(@AssessmentStatuses))
| | | | | |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Item].[PK_Item] AS [i]), SEEK:([i].[Item_Id]=[IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Question].[PK_Question] AS [q]), SEEK:([q].[Question_Id]=[IndusLynk].[dbo].[Item].[Question_Id] as [i].[Question_Id]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Display_Type].[PK_Display_Type] AS [dt]), SEEK:([dt].[Display_Type_Id]=[IndusLynk].[dbo].[Question].[Display_Type_Id] as [q].[Display_Type_Id]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate].[PK_Candidate] AS [c]), SEEK:([c].[Candidate_Id]=[IndusLynk].[dbo].[Candidate_Instance].[Candidate_Id] as [ci].[Candidate_Id]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Item_Details].[PK_Item_Details] AS [id]), SEEK:([id].[Item_Id]=[IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Answer].[PK_Answer] AS [ans]), SEEK:([ans].[Answer_Id]=[IndusLynk].[dbo].[Item_Details].[Answer_Id] as [id].[Answer_Id]) ORDERED FORWARD)
|--Table Insert(OBJECT:(@SectionDetailTable), SET:([Candidate_Id] = [IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id],[Candidate_Instance_Id] = [IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID],[Assessment_Id] = [IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID],[OverAllScore] = [Expr1025],[Section_ID] = [IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID],[Section_Name] = [Expr1028],[Section_Description] = [Expr1029],[AttempteCount] = [Expr1020],[CorrectCount] = [Expr1021],[Respone_Time] = [Expr1026],[TotaQuestionCount] = [Expr1023],[MaxMarks] = [Expr1027],[Percentile] = NULL))
|--Compute Scalar(DEFINE:([Expr1025]=round([Expr1019],(2)), [Expr1026]=CASE WHEN [IndusLynk].[dbo].[Section].[Section_Duration] as [s].[Section_Duration]<>(0) THEN CASE WHEN [Expr1022]/(6.000000000000000e+001)>CONVERT_IMPLICIT(float(53),[IndusLynk].[dbo].[Section].[Section_Duration] as [s].[Section_Duration],0) THEN CONVERT_IMPLICIT(float(53),[IndusLynk].[dbo].[Section].[Section_Duration] as [s].[Section_Duration],0) ELSE [Expr1022]/(6.000000000000000e+001) END ELSE [Expr1022]/(6.000000000000000e+001) END, [Expr1027]=CONVERT(float(53),[Expr1024],0)))
|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY:([ci].[Instance_End_Time] DESC, [rr].[Candidate_Instance_ID] DESC, [c].[Candidate_Id] ASC, [rr].[Section_ID] ASC))
|--Compute Scalar(DEFINE:([Expr1028]=CONVERT_IMPLICIT(varchar(100),[IndusLynk].[dbo].[Section].[Section_Title] as [s].[Section_Title],0), [Expr1029]=CONVERT_IMPLICIT(varchar(100),[IndusLynk].[dbo].[Section].[Section_Description] as [s].[Section_Description],0)))
|--Compute Scalar(DEFINE:([Expr1023]=CONVERT_IMPLICIT(int,[Expr1047],0), [Expr1024]=CASE WHEN [Expr1048]=(0) THEN NULL ELSE [Expr1049] END))
|--Stream Aggregate(GROUP BY:([rr].[Candidate_Instance_ID], [c].[First_Name], [c].[Last_Name], [rr].[Section_ID]) DEFINE:([Expr1047]=Count(*), [Expr1019]=SUM([Expr1030]), [Expr1020]=SUM([Expr1031]), [Expr1021]=SUM([Expr1032]), [Expr1022]=SUM([Expr1033]), [Expr1048]=COUNT_BIG(@ItemDetailTable.[MaxMarks] as [id].[MaxMarks]), [Expr1049]=SUM(@ItemDetailTable.[MaxMarks] as [id].[MaxMarks]), [ci].[Instance_End_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time]), [c].[Candidate_Id]=ANY([IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id]), [rr].[Assessment_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]), [s].[Section_Title]=ANY([IndusLynk].[dbo].[Section].[Section_Title] as [s].[Section_Title]), [s].[Section_Description]=ANY([IndusLynk].[dbo].[Section].[Section_Description] as [s].[Section_Description]), [s].[Section_Duration]=ANY([IndusLynk].[dbo].[Section].[Section_Duration] as [s].[Section_Duration])))
|--Nested Loops(Inner Join, WHERE:([IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]=@ItemDetailTable.[Candidate_Instance_Id] as [id].[Candidate_Instance_Id] AND [IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID]=@ItemDetailTable.[Section_ID] as [id].[Section_ID] AND @ItemDetailTable.[Item_ID] as [id].[Item_ID]=[IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID]))
|--Sort(ORDER BY:([rr].[Candidate_Instance_ID] ASC, [c].[First_Name] ASC, [c].[Last_Name] ASC, [rr].[Section_ID] ASC))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([ci].[Candidate_Id]))
| |--Nested Loops(Left Semi Join, WHERE:([IndusLynk].[dbo].[Candidate_Instance].[Instance_Status] as [ci].[Instance_Status]=[status]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Candidate_Instance_ID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Section_Id]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Section_ID]))
| | | | | |--Compute Scalar(DEFINE:([Expr1030]=isnull([IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained],(0.000000000000000e+000)), [Expr1031]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Display_Type_ID] as [rr].[Display_Type_ID]=(2) THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted] IS NULL THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time]>(0.000000000000000e+000) THEN (1) ELSE (0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END, [Expr1032]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained]>(0.000000000000000e+000) THEN (1) ELSE (0) END, [Expr1033]=isnull([IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time],(0.000000000000000e+000))))
| | | | | | |--Clustered Index Scan(OBJECT:([IndusLynk].[dbo].[Processed_Response].[PK_Processed_Response] AS [rr]), WHERE:([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]=[@AssessmentID] AND [IndusLynk].[dbo].[Processed_Response].[Client_ID] as [rr].[Client_ID]=[@UserID]))
| | | | | |--Index Seek(OBJECT:([IndusLynk].[dbo].[Section].[IX_Section] AS [s]), SEEK:([s].[Assessment_Id]=[@AssessmentID] AND [s].[Section_Id]=[IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Section].[PK_Section] AS [s]), SEEK:([s].[Section_Id]=[IndusLynk].[dbo].[Section].[Section_Id] as [s].[Section_Id]) LOOKUP ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate_Instance].[PK_Candidate_Instance] AS [ci]), SEEK:([ci].[Candidate_Instance_Id]=[IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]) ORDERED FORWARD)
| | |--Table Scan(OBJECT:(@AssessmentStatuses))
| |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate].[PK_Candidate] AS [c]), SEEK:([c].[Candidate_Id]=[IndusLynk].[dbo].[Candidate_Instance].[Candidate_Id] as [ci].[Candidate_Id]) ORDERED FORWARD)
|--Table Scan(OBJECT:(@ItemDetailTable AS [id]))
|--Table Insert(OBJECT:(@AssessmentDetailTable), SET:([Candidate_Id] = [IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id],[Candidate_Instance_Id] = [IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID],[Assessment_Id] = [IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID],[First_Name] = [IndusLynk].[dbo].[Candidate].[First_Name] as [c].[First_Name],[Last_Name] = [Expr1018],[OverAllScore] = [Expr1019],[Instance_Start_Time] = [Expr1020],[AttempteCount] = [Expr1015],[CorrectCount] = [Expr1016],[Respone_Time] = [Expr1021],[InvitationID] = [IndusLynk].[dbo].[Candidate_Instance].[Invitation_Id] as [ci].[Invitation_Id],[Email_ID] = [Expr1023],[RowNumber] = [Expr1024],[Percentile] = NULL))
|--Compute Scalar(DEFINE:([Expr1024]=CONVERT_IMPLICIT(int,[Expr1022],0)))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1023]=CONVERT_IMPLICIT(varchar(50),[IndusLynk].[dbo].[Candidate].[Email_Id] as [c].[Email_Id],0)))
|--Sequence Project(DEFINE:([Expr1022]=row_number))
|--Segment
|--Sort(ORDER BY:([ci].[Instance_End_Time] DESC, [rr].[Candidate_Instance_ID] DESC, [c].[Candidate_Id] ASC))
|--Compute Scalar(DEFINE:([Expr1018]=isnull([IndusLynk].[dbo].[Candidate].[Last_Name] as [c].[Last_Name],''), [Expr1019]=round([Expr1014],(2)), [Expr1020]=isnull([IndusLynk].[dbo].[Candidate_Instance].[Instance_Start_Time] as [ci].[Instance_Start_Time],dateadd(minute, -[@AssessmentDuration],[IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time])), [Expr1021]=CASE WHEN [Expr1017]/(6.000000000000000e+001)>CONVERT_IMPLICIT(float(53),[@AssessmentDuration],0) THEN CONVERT_IMPLICIT(float(53),[@AssessmentDuration],0) ELSE [Expr1017]/(6.000000000000000e+001) END))
|--Stream Aggregate(GROUP BY:([c].[Email_Id], [rr].[Candidate_Instance_ID]) DEFINE:([Expr1014]=SUM([Expr1026]), [Expr1015]=SUM([Expr1027]), [Expr1016]=SUM([Expr1028]), [Expr1017]=SUM([Expr1029]), [ci].[Instance_End_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time]), [c].[Candidate_Id]=ANY([IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id]), [rr].[Assessment_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]), [c].[First_Name]=ANY([IndusLynk].[dbo].[Candidate].[First_Name] as [c].[First_Name]), [c].[Last_Name]=ANY([IndusLynk].[dbo].[Candidate].[Last_Name] as [c].[Last_Name]), [ci].[Instance_Start_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_Start_Time] as [ci].[Instance_Start_Time]), [ci].[Invitation_Id]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Invitation_Id] as [ci].[Invitation_Id])))
|--Sort(ORDER BY:([c].[Email_Id] ASC, [rr].[Candidate_Instance_ID] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ci].[Candidate_Id]))
|--Nested Loops(Left Semi Join, WHERE:([IndusLynk].[dbo].[Candidate_Instance].[Instance_Status] as [ci].[Instance_Status]=[status]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Candidate_Instance_ID]))
| | |--Compute Scalar(DEFINE:([Expr1026]=isnull([IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained],(0.000000000000000e+000)), [Expr1027]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Display_Type_ID] as [rr].[Display_Type_ID]=(2) THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted] IS NULL THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time]>(0.000000000000000e+000) THEN (1) ELSE (0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END, [Expr1028]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained]>(0.000000000000000e+000) THEN (1) ELSE (0) END, [Expr1029]=isnull([IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time],(0.000000000000000e+000))))
| | | |--Clustered Index Scan(OBJECT:([IndusLynk].[dbo].[Processed_Response].[PK_Processed_Response] AS [rr]), WHERE:([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]=[@AssessmentID] AND [IndusLynk].[dbo].[Processed_Response].[Client_ID] as [rr].[Client_ID]=[@UserID]))
| | |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate_Instance].[PK_Candidate_Instance] AS [ci]), SEEK:([ci].[Candidate_Instance_Id]=[IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]) ORDERED FORWARD)
| |--Table Scan(OBJECT:(@AssessmentStatuses))
|--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate].[PK_Candidate] AS [c]), SEEK:([c].[Candidate_Id]=[IndusLynk].[dbo].[Candidate_Instance].[Candidate_Id] as [ci].[Candidate_Id]) ORDERED FORWARD)
|--Table Insert(OBJECT:(@AssessmentFilteredTable), SET:([Candidate_Id] = [Candidate_Id],[Candidate_Instance_Id] = [Candidate_Instance_Id],[Assessment_Id] = [Assessment_Id],[First_Name] = [First_Name],[Last_Name] = [Last_Name],[OverAllScore] = [OverAllScore],[Instance_Start_Time] = [Instance_Start_Time],[AttempteCount] = [AttempteCount],[CorrectCount] = [CorrectCount],[Respone_Time] = [Respone_Time],[Percentile] = [Percentile],[RowNumber] = [Expr1009]))
|--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1008],0)))
|--Top(ROWCOUNT est 0)
|--Sequence Project(DEFINE:([Expr1008]=row_number))
|--Segment
|--Sort(ORDER BY:([RowNumber] ASC))
|--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN @SectionDetailTable.[OverAllScore] as [i].[OverAllScore]>(0.000000000000000e+000) THEN isnull(@SectionDetailTable.[Percentile] as [i].[Percentile],(1.000000000000000e+002)) ELSE isnull(@SectionDetailTable.[Percentile] as [i].[Percentile],(0.000000000000000e+000)) END))
|--Table Scan(OBJECT:(@SectionDetailTable AS [i]))
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [OverAllScore]>(0.000000000000000e+000) THEN isnull([Percentile],(1.000000000000000e+002)) ELSE isnull([Percentile],(0.000000000000000e+000)) END))
|--Table Scan(OBJECT:(@AssessmentDetailTable))
|--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]))
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1009],0)))
|--Stream Aggregate(DEFINE:([Expr1009]=Count(*)))
|--Table Scan(OBJECT:(@AssessmentDetailTable))
推薦答案
SP 是預(yù)編譯的，可能使用了壞"查詢計劃，這對傳遞給 SP 的參數(shù)不利.您可以在 SP 內(nèi)部的查詢上添加 OPTION (RECOMPILE)，以強制 SQL Server 通過查詢優(yōu)化器重新運行查詢并找到給定參數(shù)的最佳查詢.
SPs are precompiled, it may be that a "bad" query plan is used which is not good for the arguments passed into the SP. You can add a OPTION (RECOMPILE) on the queries inside the SP to force SQL Server to re-run the query through the query optimizer and find the best query for the given parameters.
請參閱此處或此處 一些示例和背景信息...
See here or here for some examples and background info...
這篇關(guān)于SQL 查詢運行速度比存儲過程快的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！