Ett av våra system kräver krångliga sp´s.. och nu har det hamnat på mitt bord att optimera dessa. :bla
Efter några tester med b.la SET STATISTICS och Profiler´n har jag sett att just denna sp suger ganska mycket kräm.
Någon som känner sig manad att ta en titt och komma med några idéer? :)
[red]CREATE PROCEDURE spAspCandidateList_Gold
AS
SELECT
Fo1.Id,
Fi1.Value AS [Förnamn],
Fi2.Value AS [Efternamn],
Fi3.Value AS [Postnummer],
Fi4.Value AS [Statusfärg],
ISNULL(Fi6.Value, Fi5.Value) AS [Arbetsgivare],
Fi7.Value AS [Befattning],
(SELECT CASE WHEN Fi8.FormId IS NULL THEN COUNT(Id) ELSE COUNT(Id) - 1 END FROM SCM_Form WHERE FormDefId = 18 AND OwnerFormId = Fo1.Id) AS [Antal uppdrag],
(SELECT COUNT(Id) FROM SCM_Form WHERE FormDefId = 9 AND OwnerFormId = Fo1.Id AND SiblingFormId IS NULL) AS [Antal CV],
Fi8.Value AS [Uppdrag], Fi8.FormId AS [UppdragsId],
Fi10.Value AS [UppdragsStatus],
Fi11.Value AS [Kandidattyp],
Fi11.Value AS [Kandidattypfärg]
FROM
SCM_Form Fo1
INNER JOIN SCM_Field Fi0 ON Fo1.Id = Fi0.FormId AND Fi0.FieldDefId = 253
INNER JOIN SCM_Field Fi1 ON Fo1.Id = Fi1.FormId AND Fi1.FieldDefId = 16
INNER JOIN SCM_Field Fi2 ON Fo1.Id = Fi2.FormId AND Fi2.FieldDefId = 17
INNER JOIN SCM_Field Fi3 ON Fo1.Id = Fi3.FormId AND Fi3.FieldDefId = 251
INNER JOIN SCM_Field Fi4 ON Fo1.Id = Fi4.FormId AND Fi4.FieldDefId = 258
LEFT JOIN SCM_Form Fo2 ON Fo1.Id = Fo2.sys_origin AND Fo2.FormDefId = 9 AND Fo2.SiblingFormId = - 1
LEFT JOIN SCM_Field Fi5 ON Fo2.Id = Fi5.FormId AND Fi5.FieldDefId = 238
LEFT JOIN SCM_Field Fi6 ON CONVERT(int, Fi5.Value) = Fi6.FormId AND Fi6.FieldDefId = 219
LEFT JOIN SCM_Field Fi7 ON Fo2.Id = Fi7.FormId AND Fi7.FieldDefId = 239
LEFT JOIN SCM_Form Fo3 INNER JOIN SCM_Field Fi8 ON Fo3.SiblingFormId = Fi8.FormId AND Fi8.FieldDefId = 36
INNER JOIN SCM_Field Fi9 ON Fo3.Id = Fi9.FormId AND Fi9.FieldDefId = 305 AND Fi9.Value <> '1'
INNER JOIN SCM_Field Fi10 ON Fo3.Id = Fi10.FormId AND Fi10.FieldDefId = 307 ON Fo1.Id = Fo3.OwnerFormId AND Fo3.FormDefId = 18
INNER JOIN SCM_Field Fi11 ON Fo1.Id = Fi11.FormId AND Fi11.FieldDefId = 257
WHERE
Fo1.FormDefId = 8
AND NOT Fi0.Value = '1'
AND NOT Fi0.Value = '2'
AND Fi11.Value = 'Guld'
GO[/red]