افزایش کارایی کوئری با اصلاح کد T-SQL

ALTER PROCEDURE [PerKar].[sp_rep_Personels2]
(
@CurrentDate varchar(15),
@EmployStateCode nvarchar(10)
)
AS
DECLARE @tbTmp TABLE (PersonalID int PRIMARY KEY);
DECLARE @vwPerEdu_1 TABLE (PersonalID int, PEducationID int, CollectDate int, EduAverage nchar(6),
Degree tinyint, DegreeName nvarchar(80), EduEvidenceID int, University nvarchar(100),
Comments nvarchar(480), EduBranchID int, EduEvidenceDetails nvarchar(100),
EduDegree tinyint, EBName nvarchar(100), IsChangeByOthers int);
DECLARE @tbPersonnelEmployState TABLE(PersonalID int PRIMARY KEY, ItemID int, Name nvarchar(50));
DECLARE @PersonalLocation TABLE (PersonalID int PRIMARY KEY, LocationID int, LocationName nvarchar(80));
DECLARE @tbHokm_ TABLE (tbHokmID int PRIMARY KEY, PersonalID int, Rotbeh tinyint, Tabaghe int, FatherName nvarchar(30),
IssuePlace nvarchar(250), BirthPlace nvarchar(40), SanavateKhedmateRasmi nvarchar(250),
OrgJobUnitTitle nvarchar(100), Childs tinyint);
DECLARE @tbOrgJob TABLE(PersonalID int PRIMARY KEY, JobType tinyint, Name nvarchar(50),
OrgJobID nvarchar(11), OrgJobTitle nvarchar(200));
INSERT INTO @tbTmp
SELECT DISTINCT POS.PersonalID
FROM [PerKar].[tbPersonnelOfficeStates] AS POS
INNER JOIN (SELECT PersonalID, MAX(StartDate) AS StartDate
FROM [PerKar].[tbPersonnelOfficeStates] AS POS
GROUP BY PersonalID) AS tbTmp1
ON POS.PersonalID=tbTmp1.PersonalID AND POS.StartDate=tbTmp1.StartDate
WHERE POS.OfficeStateType<>43;
INSERT INTO @vwPerEdu_1
SELECT vwPerEdu.PersonalID, vwPerEdu.PEducationID, vwPerEdu.CollectDate, vwPerEdu.EduAverage,
vwPerEdu.Degree, vwPerEdu.DegreeName, vwPerEdu.EduEvidenceID, vwPerEdu.University,
vwPerEdu.Comments, vwPerEdu.EduBranchID, vwPerEdu.EduEvidenceDetails, vwPerEdu.EduDegree,
vwPerEdu.EBName, vwPerEdu.IsChangeByOthers
FROM PerKar.vwPersonnelEducations AS vwPerEdu
INNER JOIN (SELECT PersonalID, MAX(Degree) AS Degree FROM PerKar.vwPersonnelEducations
GROUP BY PersonalID) AS PersonnelDegrees
ON vwPerEdu.PersonalID = PersonnelDegrees.PersonalID AND vwPerEdu.Degree = PersonnelDegrees.Degree;
INSERT INTO @tbPersonnelEmployState
SELECT DISTINCT X.PersonalID, tbConstantItems_2.ItemID, tbConstantItems_2.Name
FROM (SELECT PersonalID, MAX(StartEmployDate) AS StartEmployDate,
(SELECT EmployType FROM PerKar.tbPersonnelEmployState AS PES1
WHERE (PersonalID = PES.PersonalID)
AND (StartEmployDate = MAX(PES.StartEmployDate))) AS EmployType
FROM PerKar.tbPersonnelEmployState AS PES
GROUP BY PersonalID) AS X
INNER JOIN PerKar.tbConstantItems AS tbConstantItems_2
ON X.EmployType = tbConstantItems_2.ItemID AND tbConstantItems_2.ConstantID=9;
INSERT INTO @PersonalLocation
SELECT tbOrgJobToPersonel.PersonalID, PerKar.tbLocations.LocationID, PerKar.tbLocations.LocationName
FROM PerKar.tbLocations
INNER JOIN PerOrg.tbOrganizationUnit
ON PerKar.tbLocations.LocationID = PerOrg.tbOrganizationUnit.LocationID
INNER JOIN PerOrg.tbOrganizationJob
ON PerOrg.tbOrganizationUnit.tbUnitID = PerOrg.tbOrganizationJob.tbUnitID
INNER JOIN (SELECT PersonalID, MAX(StartDate) AS StartDate,
(SELECT TOP 1 tbOrgJobID
FROM PerKar.tbOrgJobToPersonel AS tbOrgJobToPersonel_1
WHERE (PersonalID = jtp.PersonalID)
AND (StartDate = MAX(jtp.StartDate))) AS tbOrgJobID
FROM PerKar.tbOrgJobToPersonel AS jtp
GROUP BY PersonalID) AS tbOrgJobToPersonel
ON PerOrg.tbOrganizationJob.tbOrgJobID = tbOrgJobToPersonel.tbOrgJobID;
INSERT INTO @tbHokm_
SELECT h.tbHokmID,h.PersonalID,h.Rotbeh,h.Tabaghe,h.FatherName,
h.IssuePlace,h.BirthPlace,h.SanavateKhedmateRasmi,h.OrgJobUnitTitle,h.Childs
FROM [PerKar].[tbHokm_] AS h
INNER JOIN (SELECT PersonalID,MAX(tbHokmID) AS tbHokmID,
MAX(ExecutionDate) AS ExecutionDate
FROM [PerKar].[tbHokm_]
GROUP BY PersonalID) AS t2
ON h.tbHokmID=t2.tbHokmID;
INSERT INTO @tbOrgJob
SELECT ojp.PersonalID, oj.JobType, t.Name, oj.OrgJobID, oj.OrgJobTitle
FROM [PerOrg].[tbOrganizationJob] AS oj
INNER JOIN [PerKar].[tbOrgJobToPersonel] AS ojp
ON oj.tbOrgJobID=ojp.tbOrgJobID AND ojp.EndDate IS NULL
INNER JOIN (SELECT ci.ItemID, ci.Name
FROM [PerKar].[tbConstants] AS c
INNER JOIN [PerKar].[tbConstantItems] AS ci
ON c.ConstantID=ci.ConstantID
WHERE c.Name=N'JobType') AS t
ON t.ItemID=oj.JobType;
;WITH CTE1 AS
(
SELECT PerOrg.tbPersonalSpex.PersonalID, PerOrg.tbPersonalSpex.PersonalNum, PerOrg.tbPersonalSpex.FName,
PerOrg.tbPersonalSpex.LName, PerKar.tbConstantItems.ItemID AS GenreCode,
PerKar.tbConstantItems.Name AS GenreName, PerOrg.tbPersonalSpex.BCNo, PerOrg.tbPersonalSpex.NationalCode,
PerKar.fn_ConvertIntToDate(PerOrg.tbPersonalSpex.BirthDate) AS SBirthDate, PerOrg.tbPersonalSpex.BirthDate,
tbConstantItems_1.ItemID AS MaritalCode, tbConstantItems_1.Name AS MaritalName, vwPerEdu_1.Degree,
vwPerEdu_1.DegreeName, vwPerEdu_1.EduBranchID, vwPerEdu_1.EduEvidenceDetails,
PerKar.fn_GetJebheStates(PerOrg.tbPersonalSpex.PersonalID) AS JebheState,
tbPersonnelEmployState.ItemID AS EmployStateCode, tbPersonnelEmployState.Name AS EmployStateName,
PerKar.fn_ConvertIntToDate(PerOrg.tbPersonalSpex.EmployDate) AS SEmployDate, PerOrg.tbPersonalSpex.EmployDate,
PerOrg.tbPersonalSpex.AccountNo,
(SELECT Title FROM PerKar.tb_EmploymentAllocation
WHERE Code=LEFT(PerKar.vw_PersonelAllocation.RasteCode,1)) AS RasteAsli,
PerKar.vw_PersonelAllocation.RasteCode, PerKar.vw_PersonelAllocation.Raste,
PerKar.vw_PersonelAllocation.ReshteCode, PerKar.vw_PersonelAllocation.Reshte,
PersonalLocation.LocationID AS MKhedmatID, PersonalLocation.LocationName AS MKhedmat, tbHokm_.tbHokmID,
tbHokm_.Rotbeh,tbHokm_.Tabaghe, tbHokm_.FatherName,tbHokm_.IssuePlace,tbHokm_.BirthPlace,
tbHokm_.SanavateKhedmateRasmi,tbHokm_.OrgJobUnitTitle,tbHokm_.Childs,
PerKar.fn_FindEmploymentYears(PerOrg.tbPersonalSpex.PersonalID, dbo.fn_ConvertHijriDateToDays(@CurrentDate)) AS SanavatKhedmat
,tbOrgJob.JobType AS JobTypeID, tbOrgJob.Name AS JobTypeName, tbOrgJob.OrgJobID, tbOrgJob.OrgJobTitle
FROM PerOrg.tbPersonalSpex
INNER JOIN @tbTmp AS tbTmp
ON PerOrg.tbPersonalSpex.PersonalID=tbTmp.PersonalID
INNER JOIN PerKar.tbConstantItems
ON PerOrg.tbPersonalSpex.Genre = PerKar.tbConstantItems.ItemID AND PerKar.tbConstantItems.ConstantID = 43
INNER JOIN PerKar.tbConstantItems AS tbConstantItems_1
ON PerOrg.tbPersonalSpex.MaritalState = tbConstantItems_1.ItemID AND tbConstantItems_1.ConstantID = 5
INNER JOIN @vwPerEdu_1 AS vwPerEdu_1
ON PerOrg.tbPersonalSpex.PersonalID = vwPerEdu_1.PersonalID
INNER JOIN PerKar.vw_PersonelAllocation
ON PerOrg.tbPersonalSpex.PersonalID = PerKar.vw_PersonelAllocation.PersonalID
INNER JOIN @tbPersonnelEmployState AS tbPersonnelEmployState
ON tbPersonnelEmployState.PersonalID = PerOrg.tbPersonalSpex.PersonalID
INNER JOIN @PersonalLocation AS PersonalLocation
ON PersonalLocation.PersonalID = PerOrg.tbPersonalSpex.PersonalID
INNER JOIN @tbHokm_ AS tbHokm_
ON tbHokm_.PersonalID = PerOrg.tbPersonalSpex.PersonalID
INNER JOIN @tbOrgJob AS tbOrgJob
ON tbOrgJob.PersonalID = PerOrg.tbPersonalSpex.PersonalID
)
SELECT ROW_NUMBER() OVER (ORDER BY PersonalID) AS RowNo, *
FROM CTE1
WHERE EmployStateCode=@EmployStateCode;