Správičky 2 819 Blogy 948 Fórum 18 746

SQL View Ordering performance

photo
Liero
10. 5. 2018 16:02:49
Body: 9750
Najaktívnejší č.: 6

SQL View Ordering performance

Snazim sa urobit nad existujucou databazou view. View vracia cca 2500 zaznamov za cca 3 sekundy, no akonahle sa snazim vysledky zotriedit, trva to 30 a viac sekund a dostavam timeout.

Preco zotriedenie 2500 riadkov trva tak dlho? Co sa s tym da urobit?

SELECT TOP(1) *

FROM [Assignable Assessors]
WHERE Authorised Assessor Person ID] NOT IN (2, 1)
	AND Career Level] < 12
	AND [Certificate Type ID] = 'TAA'

-- Ked zakomentujem order by, tak to zbehne do 4 sekund
ORDER BY [Pending Interviews Count], [Last Interview Date]

 

 

ALTER VIEW [dbo].[Assignable Assessors]
AS
WITH MaxAchievedLevel AS (
	SELECT 
		MAX([Certificate Level in Family]) AS [Certificate Level In Programme]
		, [Authorised Assessor Person ID]
		, [Certification Programme ID]	
	FROM [Authorised Assessors] assessors
		INNER JOIN [Certificate Types] _t ON assessors.[Certificate Type ID] = _t.[Certificate Type ID]
		INNER JOIN [Certification Families] _family on _family.[Certification Family ID] = _t.[Certification Family ID]
	GROUP BY
		[Authorised Assessor Person ID], [Certification Programme ID]	
),

PendingInterviews AS (
	SELECT 
		COUNT (assignment.[Certification Event ID]) AS [Count]
		, assignment.[Assessor Person ID]
	FROM [Certification Events: Assessor Assignments] assignment
		INNER JOIN [Current Certification Events] evt ON evt.[Certification Event Type ID] = 'INTA' AND evt.[Certification Event ID] = assignment.[Certification Event ID]
	GROUP BY assignment.[Assessor Person ID]
),

LatestInterviews AS (
	SELECT
        MAX(evt.[Event DateTime]) AS [Interview Date]
		, assessment.[Assessor Person ID]
	FROM [Certification Events: Assessor Assessments] assessment
		INNER JOIN [Certification Events] evt ON evt.[Certification Event ID] = assessment.[Certification Event ID]
	GROUP BY [Assessor Person ID]
)

SELECT 	
	assessors.[Authorised Assessor Person ID]
	, assessors.[Certificate Type ID]
	, p.[Enterprise ID]
	, p.Email
	, p.[Career Level]
	, p.[Geography]
	, p.[Region]
	, PendingInterviews.[Count] AS [Pending Interviews Count]
	, LatestInterviews.[Interview Date] AS [Last Interview Date]
	, MaxAchievedLevel.[Certificate Level In Programme] AS [Achieved Certificate Level]

FROM        [Authorised Assessors]  AS assessors
INNER JOIN  [People Current Information EXT] p ON assessors.[Authorised Assessor Person ID] = p.[Person ID]

LEFT OUTER JOIN PendingInterviews ON PendingInterviews.[Assessor Person ID] = assessors.[Authorised Assessor Person ID]
LEFT OUTER JOIN LatestInterviews ON LatestInterviews.[Assessor Person ID] = assessors.[Authorised Assessor Person ID]

--Max Achieved Level
INNER JOIN [Certificate Types] t ON assessors.[Certificate Type ID] = t.[Certificate Type ID]
INNER JOIN [Certification Families] family ON family.[Certification Family ID] = t.[Certification Family ID]
LEFT OUTER JOIN MaxAchievedLevel ON MaxAchievedLevel.[Certification Programme ID] = family.[Certification Programme ID]
									AND MaxAchievedLevel.[Authorised Assessor Person ID] = assessors.[Authorised Assessor Person ID]
WHERE assessors.[Is Suspended] = 0

 

 

[Reakcia]

photo
harrison314
14. 5. 2018 9:01:39
Body: 1195
Najaktívnejší č.: 23

RE: SQL View Ordering performance

V MsSQl studio si pozri exekucny plan, ten ti presne povie, kde je problem.

[Reakcia]



Najaktívnejší užívatelia
1. 37810 b. photo vlko
2. 21520 b. photo T
3. 15965 b. photo spigi
4. 15450 b. photo Anonymous
5. 11120 b. photo dudok
6. 9750 b. photo Liero
7. 6920 b. photo siro
8. 6245 b. photo slavof
9. 5395 b. photo duracellko
10. 4685 b. photo xxxmatko