dbTalk Databases Forums  

Re: View Performance

Go Back dbTalk Databases Forums Databases comp.databases.ms-sqlserver Re: View Performance

comp.databases.ms-sqlserver comp.databases.ms-sqlserver


Discuss Re: View Performance in the comp.databases.ms-sqlserver forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Simon Hayes
 
Posts: n/a

Default Re: View Performance - 08-04-2003 , 03:08 PM





"bharilal" <bharilal (AT) hotmail (DOT) com> wrote

Quote:
I have a view and when I add a criteria to the view, it takes a very
long time, but if I just execute the SQL that I use for the view. It
runs much faster.

Why is this happening and is there anything I can do in the view to
achieve the same performance. The following is an example of what I am
talking about.


SELECT * FROM vwEmployee where EmpID = 12
Execution Time: 50 seconds

SELECT *
FROM tblEmployee
INNER JOIN Country ON EmpCountryID = CountryID
LEFT OUTER JOIN Address ON EmpAddID = AddID
WHERE EmpID = 12

Execution Time: 2 Seconds

Thanks in advance for the help!
Assuming it's not an indexed view, there shouldn't be any real difference,
since SQL Server replaces the view name with the view definition anyway when
the query is run. It's not entirely clear from the above exactly what your
view definition is - can you post your CREATE VIEW statement? The execution
plans of the two queries would also be useful (SET SHOWPLAN_TEXT ON).

Simon




Reply With Quote
  #2  
Old   
B Haril
 
Posts: n/a

Default Re: View Performance - 08-04-2003 , 03:43 PM





Thanks for the quick response Simon.

Here is the view creation.

CREATE VIEW dbo.vwPerformanceLogReport

AS

SELECT
ISNULL(ED.UPN,'') AS UPN,
ISNULL(ED.ProfileYear,'') AS ProfileYear,
ISNULL(FirstName,'') AS FirstName,
ISNULL(MiddleInit,'') AS MiddleInit,
ISNULL(LastName,'') AS LastName,
ISNULL(Name,'') AS Name,
ISNULL(CompanyCode,'') AS CompanyCode,
ISNULL(OrganizationID,'') AS OrganizationID,
ISNULL(CompanyName,'') AS CompanyName,
ISNULL(CounseleeNotesID,'') AS CounseleeNotesID,
ISNULL(Email,'') AS Email,
ISNULL(CounselorUPN,'') AS CounselorUPN,
ISNULL(CounselorName,'') AS CounselorName,
ISNULL(CounselorNotesID,'') AS CounselorNotesID,
ISNULL(CounselorEmail,'') AS CounselorEmail,
ISNULL(TeamLeaderUPN,'') AS TeamLeaderUPN,
ISNULL(TeamLeaderName,'') AS TeamLeaderName,
ISNULL(TeamLeaderNotesID,'') AS TeamLeaderNotesID,
ISNULL(TeamLeaderEmail,'') AS TeamLeaderEmail,
ISNULL(AreaOfficeCode,'') AS AreaOfficeCode,
ISNULL(AreaOffice,'') AS AreaOffice,
ISNULL(Sector,'') AS Sector,
ISNULL(BusinessUnitCode,'') AS BusinessUnitCode,
ISNULL(ServiceLine,'') AS ServiceLine,
ISNULL(EmployeeStatusCode,'') AS EmployeeStatusCode,
ISNULL(EmployeeStatus,'') AS EmployeeStatus,
ISNULL(CountryCode,'') AS CountryCode,
ISNULL(eTeam,'') AS eTeam,
ISNULL(ProductName,'') AS ProductName,
ISNULL(PrimaryAlignment,'') AS PrimaryAlignment,
ISNULL(Profession,'') AS Profession,
ISNULL(CareerPathID,0) AS CareerPathID,
ISNULL(CareerPath,'') AS CareerPath,
ISNULL(PracticeArea,'') AS PracticeArea,
ISNULL(PracticeAreaID,0) AS PracticeAreaID,
ISNULL(RankID,'') AS RankID,
ISNULL(RankName,'') AS RankName,
ISNULL(Specialty,'') AS Specialty,
ISNULL(RecentHireDate,'') AS RecentHireDate,
ISNULL(PriorServiceDate,'') AS PriorServiceDate,
ISNULL(LastPromoDate,'') AS LastPromoDate,
ISNULL(LocationCode,'') AS LocationCode,
ISNULL(WorkLocationState,'') AS WorkLocationState,
ISNULL(WorkLocationCity,'') AS WorkLocationCity,
ISNULL(WorkPhone,'') AS WorkPhone,
ISNULL(EYComm,'') AS EYComm,
ISNULL(Executive,'') AS Executive,
ISNULL(TerminationDate,'') AS TerminationDate,
ISNULL(TerminationIndicator,'') AS TerminationIndicator,
ISNULL(LearningLock,'') AS LearningLock,
ISNULL(PLId,0) AS PLId,
ISNULL(RoleID,0) AS RoleID,
ISNULL(RoleName,0) AS RoleName,
ISNULL(Status,'') AS Status,
ISNULL(PlanPerformanceGoals,'') AS PlanPerformanceGoals,
ISNULL(PlanCounseleeComments,'') AS PlanCounseleeComments,
ISNULL(PlanCounselorComments,'') AS PlanCounselorComments,
ISNULL(PlanPerformanceObjective,'') AS PlanPerformanceObjective,
ISNULL(PlanCounseleeSign,0) AS PlanCounseleeSign,
ISNULL(PlanCounseleeSignUPN,'') AS PlanCounseleeSignUPN,
ISNULL(PlanCounseleeSignName,'') AS PlanCounseleeSignName,
ISNULL(PlanCounseleeSignTime,'') AS PlanCounseleeSignTime,
ISNULL(PlanCounselorSign,0) AS PlanCounselorSign,
ISNULL(PlanCounselorSignUPN,'') AS PlanCounselorSignUPN,
ISNULL(PlanCounselorSignName,'') AS PlanCounselorSignName,
ISNULL(PlanCounselorSignTime,'') AS PlanCounselorSignTime,
ISNULL(MidYearCounseleeComments,'') AS MidYearCounseleeComments,
ISNULL(MidYearCounselorOverallComments,'') AS
MidYearCounselorOverallComments,
ISNULL(MidYearCounselorDevelopmentComments,'') AS
MidYearCounselorDevelopmentComments,
ISNULL(MidYearCounselorAddedValueComments,'') AS
MidYearCounselorAddedValueComments,
ISNULL(MidYearCounselorLeadershipComments,'') AS
MidYearCounselorLeadershipComments,
ISNULL(MidYearCounseleeSign,0) AS MidYearCounseleeSign,
ISNULL(MidYearCounseleeSignUPN,'') AS MidYearCounseleeSignUPN,
ISNULL(MidYearCounseleeSignName,'') AS MidYearCounseleeSignName,
ISNULL(MidYearCounseleeSignTime,'') AS MidYearCounseleeSignTime,
ISNULL(MidYearCounselorSign,0) AS MidYearCounselorSign,
ISNULL(MidYearCounselorSignUPN,'') AS MidYearCounselorSignUPN,
ISNULL(MidYearCounselorSignName,'') AS MidYearCounselorSignName,
ISNULL(MidYearCounselorSignTime,'') AS MidYearCounselorSignTime,
ISNULL(LearningGoal,'') AS LearningGoal,
ISNULL(MaxStaffCounseled,0) AS MaxStaffCounseled,
ISNULL(CounseleeLeadershipComments,'') AS CounseleeLeadershipComments,
ISNULL(CounselorLeadershipComments,'') AS CounselorLeadershipComments,
ISNULL(LeaderShipRating,'') AS LeaderShipRating,
ISNULL(CounseleeKnowledgeComments,'') AS CounseleeKnowledgeComments,
ISNULL(CounselorKnowledgeComments,'') AS CounselorKnowledgeComments,
ISNULL(KnowledgeRating,'') AS KnowledgeRating,
ISNULL(SalesGoal,'') AS SalesGoal,
ISNULL(CounseleeSalesComments,'') AS CounseleeSalesComments,
ISNULL(CounselorSalesComments,'') AS CounselorSalesComments,
ISNULL(SalesRating,'') AS SalesRating,
ISNULL(UtilizationGoal,'') AS UtilizationGoal,
ISNULL(RevenueManagedGoal,'') AS RevenueManagedGoal,
ISNULL(CounseleeServiceComments,'') AS CounseleeServiceComments,
ISNULL(CounselorServiceComments,'') AS CounselorServiceComments,
ISNULL(ServiceRating,'') AS ServiceRating,
ISNULL(YearEndRating,'') AS YearEndRating,
ISNULL(PerformanceObjResults,'') AS PerformanceObjResults,
ISNULL(YearEndStrength,'') AS YearEndStrength,
ISNULL(YearEndDevelopmentNeed,'') AS YearEndDevelopmentNeed,
ISNULL(YearEndCounseleeComments,'') AS YearEndCounseleeComments,
ISNULL(OverallAssessment,'') AS OverallAssessment,
ISNULL(YearEndCounselorDevelopmentComments,'') AS
YearEndCounselorDevelopmentComments,
ISNULL(YearEndCounselorAddedValueComments,'') AS
YearEndCounselorAddedValueComments,
ISNULL(YearEndCounselorLeadershipComments,'') AS
YearEndCounselorLeadershipComments,
ISNULL(YearEndCounselorComments,'') AS YearEndCounselorComments,
ISNULL(YearEndCounseleeSign,0) AS YearEndCounseleeSign,
ISNULL(YearEndCounseleeSignUPN,'') AS YearEndCounseleeSignUPN,
ISNULL(YearEndCounseleeSignName,'') AS YearEndCounseleeSignName,
ISNULL(YearEndCounseleeSignTime,'') AS YearEndCounseleeSignTime,
ISNULL(YearEndCounselorSign,0) AS YearEndCounselorSign,
ISNULL(YearEndCounselorSignUPN,'') AS YearEndCounselorSignUPN,
ISNULL(YearEndCounselorSignName,'') AS YearEndCounselorSignName,
ISNULL(YearEndCounselorSignTime,'') AS YearEndCounselorSignTime,
ISNULL(CounseleePrivate,0) AS CounseleePrivate,
ISNULL(CounselorPrivate,0) AS CounselorPrivate,
CASE CounselorPromotion
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'NO Response'
END AS CounselorPromotionText,
ISNULL(CounselorPromotion,2) AS CounselorPromotion,
ISNULL(CounselorPromotionComments,'') ASCounselorPromotionComments,
CASE TeamLeaderPromotion
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'NO Response'
END AS TeamLeaderPromotionText,
ISNULL(TeamLeaderPromotion,2) AS TeamLeaderPromotion,
ISNULL(TeamLeaderPromotionComments,'') AS TeamLeaderPromotionComments,
CASE BusinessUnitPromotion
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'NO Response'
END AS BusinessUnitPromotionText,
ISNULL(BusinessUnitPromotion,2) AS BusinessUnitPromotion,
ISNULL(PromotionComments,'') AS PromotionComments,
ISNULL(PromotionLock,0) AS PromotionLock,
CASE PlanCompliance
WHEN 1 THEN 2
ELSE
ISNULL(PlanCompleted,0)
END AS PlanCompleted,
CASE MidYearCompliance
WHEN 1 THEN 2
ELSE
ISNULL(MidYearCompleted,0)
END AS MidYearCompleted,
CASE YearEndCompliance
WHEN 1 THEN 2
ELSE
ISNULL(YearEndCompleted,0)
END AS YearEndCompleted,
ISNULL(Active,0) AS Active,
ISNULL(LastUpdateUPN,'') AS LastUpdateUPN,
ISNULL(LastUpdate,'') AS LastUpdate,
ISNULL(PLCompleted,0) AS PLCompleted,
ISNULL(LearningParticipantHours,0) AS LearningParticipantHours,
ISNULL(AuthorHours,0) AS AuthorHours,
ISNULL(LeaderHours,0) AS LeaderHours,
ISNULL(LeaderAPHours,0) AS LeaderAPHours,
ISNULL(LearningAPAhours,0) AS LearningAPAhours,
ISNULL(LearningTotal,0) AS LearningTotal,
ISNULL(LearningPercentComplete,0) AS LearningPercentComplete,
ISNULL(LearningLastImport,'') AS LearningLastImport,
ISNULL(HoursCounseling,0) AS HoursCounseling,
ISNULL(KLMPoints,0) AS KLMPoints,
ISNULL(KLMLastImport,'') AS KLMLastImport,
ISNULL(AvailableHours,0) AS AvailableHours,
ISNULL(ChargedHours,0) AS ChargedHours,
ISNULL(UtilizationPercentage,0) AS UtilizationPercentage,
ISNULL(UtilizationPeriod,'') AS UtilizationPeriod,
ISNULL(UtilizationLastImport,'') AS UtilizationLastImport,
ISNULL(ApprovedPursuitTime,0) AS ApprovedPursuitTime,
ISNULL(ServiceLineInitiativesTime,0) AS ServiceLineInitiativesTime,
ISNULL(SumRevenueManaged,0) AS SumRevenueManaged,
ISNULL(StaffEPUCompleted,0) AS StaffEPUCompleted,
ISNULL(EPUDate,'') AS EPUDate,
ISNULL(SalesTotal,0) AS SalesTotal,
ISNULL(CanadaLastMetricImport,'') AS CanadaLastMetricImport,
ISNULL(SalesLastImport,'') AS SalesLastImport,
ISNULL(HoursCounselingLastImport,'') AS HoursCounselingLastImport,
ISNULL(SalesPeriod,'') AS SalesPeriod
FROM
vwEmployeeDemographic ED
INNER JOIN vwPerformanceLog PL ON ED.ProfileYear = PL.ProfileYear AND
ED.upn = PL.upn
INNER JOIN vwMetric m ON ED.ProfileYear = m.ProfileYear AND ED.upn =
m.upn
WHERE
Executive = 0

-------
Here is the query I ran and the Execution plan Text. Also, execution
time in query analyser was 21 seconds.

Select * from vwPerformanceLogReport where UPN = '1601352' and
profileYear = '2003' AND Executive = 0


Quote:
--Filter(WHERE([EmployeeDemographic].[UPN]='1601352' AND
[EmployeeDemographic].[ProfileYear]='2003') AND Convert([Expr1125])=0))
--Compute
Scalar(DEFINE[EmployeeDemographic].[UPN]=[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear]=[EmployeeDemographic].[ProfileYear],
[Expr1077]=isnull(isnull([EmployeeDemographic].[FirstName], ''), ''),
[Expr1078]=isnull(isnull([EmployeeDemographic].[MiddleInit], ''), ''),
[Expr1079]=isnull(isnull([EmployeeDemographic].[LastName], ''), ''),
[Expr1080]=isnull(isnull([EmployeeDemographic].[Name], ''), ''),
[Expr1081]=isnull(isnull([EmployeeDemographic].[CompanyCode], ''), ''),
[Expr1082]=isnull(isnull([Organization].[OrganizationID], 0), 0),
[Expr1083]=isnull(isnull([EmployeeDemographic].[CompanyName], ''), ''),
[Expr1084]=isnull(isnull([EmployeeDemographic].[CounseleeNotesID], ''),
''), [Expr1085]=isnull(isnull([EmployeeDemographic].[email], ''), ''),
[Expr1086]=isnull(isnull([EmployeeDemographic].[CounselorUPN], ''), ''),
[Expr1087]=isnull(isnull([EmployeeDemographic].[Name], ''), ''),
[Expr1088]=isnull(isnull([EmployeeDemographic].[CounselorNotesID], ''),
''), [Expr1089]=isnull(isnull([EmployeeDemographic].[CounselorEmail],
''), ''),
[Expr1090]=isnull(isnull([EmployeeDemographic].[AreaOfficeCode], ''),
''), [Expr1091]=isnull(isnull([EmployeeDemographic].[AreaOffice], ''),
''), [Expr1092]=isnull(isnull([EmployeeDemographic].[TeamLeaderUPN],
''), ''), [Expr1093]=isnull(isnull([EmployeeDemographic].[Name], ''),
''), [Expr1094]=isnull(isnull([EmployeeDemographic].[TeamLeaderNotesID],
''), ''),
[Expr1095]=isnull(isnull([EmployeeDemographic].[TeamLeaderEmail], ''),
''), [Expr1096]=isnull(isnull([EmployeeDemographic].[AreaOfficeCode],
''), ''), [Expr1097]=isnull(isnull([EmployeeDemographic].[AreaOffice],
''), ''),
[Expr1098]=isnull(isnull([EmployeeDemographic].[AreaOfficeCode], ''),
''), [Expr1099]=isnull(isnull([EmployeeDemographic].[AreaOffice], ''),
''), [Expr1100]=isnull(isnull([EmployeeDemographic].[Sector], ''), ''),
[Expr1101]=isnull(isnull([EmployeeDemographic].[BusinessUnitCode], ''),
''), [Expr1102]=isnull(isnull([EmployeeDemographic].[ServiceLine], ''),
''),
[Expr1103]=isnull(isnull([EmployeeDemographic].[EmployeeStatusCode],
''), ''),
[Expr1104]=isnull(isnull([EmployeeDemographic].[EmployeeStatus], ''),
''), [Expr1105]=isnull(isnull([EmployeeDemographic].[CountryCode], ''),
''), [Expr1106]=isnull(isnull([EmployeeDemographic].[eTeam], ''), ''),
[Expr1107]=isnull(isnull([EmployeeDemographic].[ProductName], ''), ''),
[Expr1108]=isnull(isnull([EmployeeDemographic].[PrimaryAlignment], ''),
''), [Expr1109]=isnull(isnull([EmployeeDemographic].[Profession], ''),
''), [Expr1110]=isnull(isnull([EmployeeDemographic].[CareerPathID], 0),
0), [Expr1111]=isnull(isnull([CareerPath].[CareerPath], ''), ''),
[Expr1112]=isnull(isnull([Rank].[PracticeAreaDesc], ''), ''),
[Expr1113]=isnull(isnull([Rank].[PracticeAreaID], 0), 0),
[Expr1114]=isnull(isnull([EmployeeDemographic].[RankID], ''), ''),
[Expr1115]=isnull(isnull([EmployeeDemographic].[RankName], ''), ''),
[Expr1116]=isnull(isnull([EmployeeDemographic].[Specialty], ''), ''),
[Expr1117]=isnull(isnull(Convert([EmployeeDemographic].[RecentHireDate])
, ''), ''), [Expr1118]=isnull(If
([EmployeeDemographic].[PriorServiceDate]<>'Jan 1 1900 12:00AM' AND
[EmployeeDemographic].[PriorServiceDate]<>'May 24 2000 12:00AM') then
Convert(Convert([EmployeeDemographic].[PriorServiceDate])) else If
((isnull([EmployeeDemographic].[PriorServiceDate], 'Jan 1 1900
12:00AM')='Jan 1 1900 12:00AM' AND
[EmployeeDemographic].[RecentHireDate]<>'May 24 2000 12:00AM') AND
[EmployeeDemographic].[RecentHireDate]<>'Jan 1 1900 12:00AM') then
Convert(Convert([EmployeeDemographic].[RecentHireDate])) else If
([EmployeeDemographic].[PriorServiceDate]='May 24 2000 12:00AM' AND
isnull([EmployeeDemographic].[RecentHireDate], 'Jan 1 1900
12:00AM')='Jan 1 1900 12:00AM') then
Convert(Convert([EmployeeDemographic].[PriorServiceDate])) else If
([EmployeeDemographic].[RecentHireDate]='May 24 2000 12:00AM' AND
isnull([EmployeeDemographic].[PriorServiceDate], 'Jan 1 1900
12:00AM')='Jan 1 1900 12:..
Quote:
--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))
--Hash Match(Right Outer Join,
HASH[MetricSummary].[ProfileYear],
[MetricSummary].[UPN])=([EmployeeDemographic].[ProfileYear],
[EmployeeDemographic].[UPN]),
RESIDUAL[EmployeeDemographic].[ProfileYear]=[MetricSummary].[ProfileYe
ar] AND [EmployeeDemographic].[UPN]=[MetricSummary].[UPN]))
Quote:
|--Clustered Index
Scan(OBJECT[PDMS].[dbo].[MetricSummary].[PK_ExecutivePromotionMetricSu
mmary]))
Quote:
|--Hash Match(Right Outer Join,
HASH[ExecutivePromotion].[PLID])=([PerformanceLog].[PLId]))
|--Clustered Index
Scan(OBJECT[PDMS].[dbo].[ExecutivePromotion].[PK_ExecutivePromotion]))
|--Compute
Scalar(DEFINE[Expr1038]=Convert([PerformanceLog].[PlanCounseleeSignTim
e])+' '+Convert([PerformanceLog].[PlanCounseleeSignTime]),
[Expr1039]=Convert([PerformanceLog].[PlanCounselorSignTime])+'
'+Convert([PerformanceLog].[PlanCounselorSignTime]),
[Expr1040]=Convert([PerformanceLog].[MidYearCounseleeSignTime])+'
'+Convert([PerformanceLog].[MidYearCounseleeSignTime]),
[Expr1041]=Convert([PerformanceLog].[MidYearCounselorSignTime])+'
'+Convert([PerformanceLog].[MidYearCounselorSignTime]),
[Expr1383]=[Expr1383], [Expr1385]=[Expr1385], [Expr1387]=[Expr1387],
[Expr1389]=[Expr1389],
[Expr1066]=Convert([PerformanceLog].[YearEndCounseleeSignTime])+'
'+Convert([PerformanceLog].[YearEndCounseleeSignTime]),
[Expr1067]=Convert([PerformanceLog].[YearEndCounselorSignTime])+'
'+Convert([PerformanceLog].[YearEndCounselorSignTime]), [Expr1068]=If
(Convert([PerformanceLog].[PlanCounseleeSign])=1 AND
Convert([PerformanceLog].[PlanCounselorSign])=1) then 1 else 0,
[Expr1069]=If (Convert([PerformanceLog].[MidYearCounseleeSign])=1 AND
Convert([PerformanceLog].[MidYearCounselorSign])=1) then 1 else 0,
[Expr1070]=If (Convert([PerformanceLog].[YearEndCounseleeSign])=1 AND
Convert([PerformanceLog].[YearEndCounselorSign])=1) then 1 else 0,
[Expr1071]=If (isnull([PerformanceLog].[TeamDiscussionLeaderUPN],
'')='') then Convert([EmployeeDemographic].[TeamLeaderUPN]) else
isnull([PerformanceLog].[TeamDiscussionLeaderUPN], ''), [Expr1072]=If
(isnull([PerformanceLog].[TeamDiscussionLeaderUPN], '')='') then
[EmployeeDemographic].[Name] else
Convert(isnull([PerformanceLog].[TeamDiscussionLeaderName], '')),
[Expr1073]=If (isnull([PerformanceLog].[LCRRankid], '')='') then
isnull([EmployeeDemographic].[RankID], '') else
isnull([PerformanceLog].[LCRRankid], ''), [Expr1074]=If
(isnull([PerformanceLog].[LCRRankid], '')='') then
isnull([EmployeeDemographic].[RankName], '') else
isnull([PerformanceLog].[LCRRankName], '')))
Quote:
|--Merge Join(Right Outer Join,
MERGE[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([EmployeeDemographic].[TeamLeaderU
PN], [EmployeeDemographic].[ProfileYear]),
RESIDUAL[EmployeeDemographic].[UPN]=[EmployeeDemographic].[TeamLeaderU
PN] AND
[EmployeeDemographic].[ProfileYear]=[EmployeeDemographic].[ProfileYear])
)
Quote:
|--Sort(ORDER
BY[EmployeeDemographic].[UPN] ASC, [EmployeeDemographic].[ProfileYear]
ASC))
Quote:
| |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| |--Clustered Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Quote:
|--Sort(ORDER
BY[EmployeeDemographic].[TeamLeaderUPN] ASC,
[EmployeeDemographic].[ProfileYear] ASC))
Quote:
|--Merge Join(Right Outer
Join, MERGE[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([EmployeeDemographic].[CounselorUP
N], [EmployeeDemographic].[ProfileYear]),
RESIDUAL[EmployeeDemographic].[UPN]=[EmployeeDemographic].[CounselorUP
N] AND
[EmployeeDemographic].[ProfileYear]=[EmployeeDemographic].[ProfileYear])
)
Quote:
|--Sort(ORDER
BY[EmployeeDemographic].[UPN] ASC, [EmployeeDemographic].[ProfileYear]
ASC))
Quote:
| |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| |--Clustered
Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Quote:
|--Sort(ORDER
BY[EmployeeDemographic].[CounselorUPN] ASC,
[EmployeeDemographic].[ProfileYear] ASC))
Quote:
|--Hash Match(Right
Outer Join,
HASH[CareerPath].[CareerPathID])=([EmployeeDemographic].[CareerPathID]
),
RESIDUAL[EmployeeDemographic].[CareerPathID]=[CareerPath].[CareerPathI
D]))
Quote:
|--Clustered
Index Scan(OBJECT[PDMS].[dbo].[CareerPath].[PK_CareerPath]))
|--Hash
Match(Right Outer Join, HASH[Rank].[RankID],
[Rank].[RankName])=([EmployeeDemographic].[RankID],
[EmployeeDemographic].[RankName]),
RESIDUAL[Rank].[RankID]=[EmployeeDemographic].[RankID] AND
[Rank].[RankName]=[EmployeeDemographic].[RankName]))
Quote:
--Clustered Index Scan(OBJECT[PDMS].[dbo].[Rank].[PK_Rank]))
|--Hash
Match(Right Outer Join,
HASH[Organization].[Companycode])=([EmployeeDemographic].[CompanyCode]
),
RESIDUAL[Organization].[Companycode]=[EmployeeDemographic].[CompanyCod
e]))
Quote:
--Index Scan(OBJECT[PDMS].[dbo].[Organization].[IX_Organization]))

--Merge Join(Inner Join, MERGE[Metric].[UPN],
[Metric].[ProfileYear])=([PerformanceLog].[UPN],
[PerformanceLog].[ProfileYear]),
RESIDUAL[PerformanceLog].[ProfileYear]=[Metric].[ProfileYear] AND
[PerformanceLog].[UPN]=[Metric].[UPN]))
Quote:
--Compute
Scalar(DEFINE[Metric].[LearningAPAhours]=isnull([Metric].[AuthorHours]
, Convert(0))+isnull([Metric].[LeaderHours],
Convert(0))+isnull([Metric].[LeaderAPHours], Convert(0)),
[Metric].[LearningTotal]=isnull([Metric].[LearningParticipantHours],
Convert(0))+isnull([Metric].[AuthorHours],
Convert(0))+isnull([Metric].[LeaderHours],
Convert(0))+isnull([Metric].[LeaderAPHours], Convert(0)),
[Metric].[InvestmentUtilizationPercentage]=If
(isnull([Metric].[AvailableHours], 0)=Convert(0)) then Convert(0) else
Convert((Convert(100)*Convert(isnull([Metric].[InvestmentHours],
0))/Convert([Metric].[AvailableHours]))),
[Metric].[UtilizationPercentage]=If (isnull([Metric].[AvailableHours],
0)=Convert(0)) then Convert(0) else
Convert((Convert(100)*Convert(isnull([Metric].[ChargedHours],
0))/Convert([Metric].[AvailableHours]))),
[Metric].[UtilizationPeriod]='Oct 1, '+[Metric].[ProfileYear]+' -
'+Convert([Metric].[UtilizationLastImport])))
Quote:
|--Clustered Index
Scan(OBJECT[PDMS].[dbo].[Metric].[PK_Metrics]), ORDERED FORWARD)

--Merge Join(Inner Join, MERGE[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([PerformanceLog].[UPN],
[PerformanceLog].[ProfileYear]),
RESIDUAL[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileY
ear] AND [EmployeeDemographic].[UPN]=[PerformanceLog].[UPN]))
Quote:
--Sort(ORDER BY[EmployeeDemographic].[UPN] ASC,
[EmployeeDemographic].[ProfileYear] ASC))

|--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=[EmployeeDemographic].[First
Name]+If ([EmployeeDemographic].[MiddleInit]=NULL OR
[EmployeeDemographic].[MiddleInit]='') then Convert(' ') else ('
'+[EmployeeDemographic].[MiddleInit]+'
')+[EmployeeDemographic].[LastName]))
Quote:
|--Clustered Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]), WHEREConvert(isnull([EmployeeDemographic].[Executive], 0))=0))
Quote:
--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))

--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))

|--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))

| |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))

| | |--Sort(ORDER BY[PerformanceLog].[UPN] ASC,
[PerformanceLog].[ProfileYear] ASC))

| | | |--Hash Match(Right Outer Join,
HASH[ExecutivePromotion].[PLID])=([PerformanceLog].[PLId]))

| | | |--Index
Scan(OBJECT[PDMS].[dbo].[ExecutivePromotion].[IX_ExecutivePromotion]))

| | | |--Hash Match(Right Outer Join,
HASH[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([PerformanceLog].[YearEndCounselor
SignUPN], [PerformanceLog].[ProfileYear]),
RESIDUAL[PerformanceLog].[YearEndCounselorSignUPN]=[EmployeeDemographi
c].[UPN] AND
[PerformanceLog].[ProfileYear]=[EmployeeDemographic].[ProfileYear]))
Quote:
| | | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | | |--Clustered Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Quote:
| | | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | |--Bookmark
Lookup(BOOKMARK[Bmk1032]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | |--Nested Loops(Left Outer Join,
OUTER REFERENCES[PerformanceLog].[YearEndCounseleeSignUPN],
[PerformanceLog].[ProfileYear]) WITH PREFETCH)
Quote:
| | | |--Hash Match(Right Outer
Join, HASH[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([PerformanceLog].[MidYearCounselor
SignUPN], [PerformanceLog].[ProfileYear]),
RESIDUAL[PerformanceLog].[MidYearCounselorSignUPN]=[EmployeeDemographi
c].[UPN] AND
[PerformanceLog].[ProfileYear]=[EmployeeDemographic].[ProfileYear]))
Quote:
| | | | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | | | |--Clustered
Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Quote:
| | | | |--Hash Match(Right
Outer Join, HASH[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([PerformanceLog].[MidYearCounselee
SignUPN], [PerformanceLog].[ProfileYear]),
RESIDUAL[PerformanceLog].[MidYearCounseleeSignUPN]=[EmployeeDemographi
c].[UPN] AND
[PerformanceLog].[ProfileYear]=[EmployeeDemographic].[ProfileYear]))
Quote:
| | | | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | | | |--Clustered
Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Quote:
| | | | |--Hash
Match(Right Outer Join, HASH[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([PerformanceLog].[PlanCounselorSig
nUPN], [PerformanceLog].[ProfileYear]),
RESIDUAL[PerformanceLog].[PlanCounselorSignUPN]=[EmployeeDemographic].
[UPN] AND
[PerformanceLog].[ProfileYear]=[EmployeeDemographic].[ProfileYear]))
Quote:
| | | | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | | |
--Clustered Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Quote:
| | | | |--Hash
Match(Right Outer Join, HASH[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([PerformanceLog].[PlanCounseleeSig
nUPN], [PerformanceLog].[ProfileYear]),
RESIDUAL[PerformanceLog].[PlanCounseleeSignUPN]=[EmployeeDemographic].
[UPN] AND
[PerformanceLog].[ProfileYear]=[EmployeeDemographic].[ProfileYear]))
Quote:
| | | |
--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Clustered Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Quote:
| | | | |--Hash
Match(Right Outer Join, HASH[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([EmployeeDemographic].[TeamLeaderU
PN], [EmployeeDemographic].[ProfileYear]),
RESIDUAL[EmployeeDemographic].[TeamLeaderUPN]=[EmployeeDemographic].[U
PN] AND
[EmployeeDemographic].[ProfileYear]=[EmployeeDemographic].[ProfileYear])
)
Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Clustered Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Merge Join(Inner Join, MERGE[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear])=([PerformanceLog].[UPN],
[PerformanceLog].[ProfileYear]),
RESIDUAL[PerformanceLog].[UPN]=[EmployeeDemographic].[UPN] AND
[PerformanceLog].[ProfileYear]=[EmployeeDemographic].[ProfileYear]))
Sort(ORDER BY[EmployeeDemographic].[UPN] ASC,
[EmployeeDemographic].[ProfileYear] ASC))
Quote:
--Clustered Index
Scan(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic_
1]))
Sort(ORDER BY[PerformanceLog].[UPN] ASC,
[PerformanceLog].[ProfileYear] ASC))
Quote:
--Compute Scalar(DEFINE[PerformanceLog].[PLCompleted]=If
(((((Convert([PerformanceLog].[PlanCounseleeSign])=1 AND
Convert([PerformanceLog].[PlanCounselorSign])=1) AND
Convert([PerformanceLog].[MidYearCounseleeSign])=1) AND
Convert([PerformanceLog].[MidYearCounselorSign])=1) AND
Convert([PerformanceLog].[YearEndCounseleeSign])=1) AND
Convert([PerformanceLog].[YearEndCounselorSign])=1) then 1 else 0,
[PerformanceLog].[Status]=If
(Convert([PerformanceLog].[YearEndCounselorSign])=1 AND
Convert([PerformanceLog].[YearEndCounseleeSign])=1) then
Convert('Performance Log Completed') else If
(Convert([PerformanceLog].[MidYearCounselorSign])=1 AND
Convert([PerformanceLog].[MidYearCounseleeSign])=1) then 'Counselor and
Counselee are working on Year-End section' else If
(Convert([PerformanceLog].[PlanCounseleeSign])=1 AND
Convert([PerformanceLog].[PlanCounselorSign])=1) then 'Counselor and
Counselee are working on Mid-Year section' else 'Counselor and Counselee
are working on Planning section'))
Quote:
--Clustered Index
Scan(OBJECT[PDMS].[dbo].[PerformanceLog].[PK_PL]))

| | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[YearEndCounseleeSign
UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | |--Hash Match(Cache,
HASH[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
| | |--Assert(WHEREIf ([Expr1382]>1) then 0 else
NULL))

| | |--Stream
Aggregate(DEFINE[Expr1382]=Count(*),
[Expr1383]=ANY([Target].[LearningGoal])))
Quote:
| | |--Nested Loops(Inner Join, OUTER
REFERENCES[EmployeeDemographic].[CareerPathID],
[EmployeeDemographic].[RankID], [EmployeeDemographic].[CountryCode]))
Quote:
| | |--Bookmark
Lookup(BOOKMARK[Bmk1044]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | |--Clustered Index
Seek(OBJECT[PDMS].[dbo].[Target].[PK_Target]),
SEEK[Target].[CareerPathID]=[EmployeeDemographic].[CareerPathID] AND
[Target].[RankID]=[EmployeeDemographic].[RankID] AND
[Target].[CountryCode]=[EmployeeDemographic].[CountryCode] AND
[Target].[ProfileYear]=[PerformanceLog].[ProfileYear]) ORDERED FORWARD)
Quote:
| |--Hash Match(Cache, HASH[PerformanceLog].[ProfileYear],
[PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
| |--Assert(WHEREIf ([Expr1384]>1) then 0 else NULL))

| |--Stream Aggregate(DEFINE[Expr1384]=Count(*),
[Expr1385]=ANY([Target].[SalesGoal])))

| |--Nested Loops(Inner Join, OUTER
REFERENCES[EmployeeDemographic].[CareerPathID],
[EmployeeDemographic].[RankID], [EmployeeDemographic].[CountryCode]))
Quote:
| |--Bookmark Lookup(BOOKMARK[Bmk1050]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))

| | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| |--Clustered Index
Seek(OBJECT[PDMS].[dbo].[Target].[PK_Target]),
SEEK[Target].[CareerPathID]=[EmployeeDemographic].[CareerPathID] AND
[Target].[RankID]=[EmployeeDemographic].[RankID] AND
[Target].[CountryCode]=[EmployeeDemographic].[CountryCode] AND
[Target].[ProfileYear]=[PerformanceLog].[ProfileYear]) ORDERED FORWARD)
Quote:
|--Hash Match(Cache, HASH[PerformanceLog].[ProfileYear],
[PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
|--Assert(WHEREIf ([Expr1386]>1) then 0 else NULL))

|--Stream Aggregate(DEFINE[Expr1386]=Count(*),
[Expr1387]=ANY([Target].[UtilizationGoal])))

|--Nested Loops(Inner Join, OUTER
REFERENCES[EmployeeDemographic].[CareerPathID],
[EmployeeDemographic].[RankID], [EmployeeDemographic].[CountryCode]))
Quote:
|--Bookmark Lookup(BOOKMARK[Bmk1056]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))

| |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
|--Clustered Index
Seek(OBJECT[PDMS].[dbo].[Target].[PK_Target]),
SEEK[Target].[CareerPathID]=[EmployeeDemographic].[CareerPathID] AND
[Target].[RankID]=[EmployeeDemographic].[RankID] AND
[Target].[CountryCode]=[EmployeeDemographic].[CountryCode] AND
[Target].[ProfileYear]=[PerformanceLog].[ProfileYear]) ORDERED FORWARD)
Quote:
--Hash Match(Cache, HASH[PerformanceLog].[ProfileYear],
[PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
--Assert(WHEREIf ([Expr1388]>1) then 0 else NULL))

--Stream Aggregate(DEFINE[Expr1388]=Count(*),
[Expr1389]=ANY([Target].[RevenueManagedGoal])))

--Nested Loops(Inner Join, OUTER
REFERENCES[EmployeeDemographic].[CareerPathID],
[EmployeeDemographic].[RankID], [EmployeeDemographic].[CountryCode]))
Quote:
--Bookmark Lookup(BOOKMARK[Bmk1062]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))

|--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
--Clustered Index Seek(OBJECT[PDMS].[dbo].[Target].[PK_Target]),
SEEK[Target].[CareerPathID]=[EmployeeDemographic].[CareerPathID] AND
[Target].[RankID]=[EmployeeDemographic].[RankID] AND
[Target].[CountryCode]=[EmployeeDemographic].[CountryCode] AND
[Target].[ProfileYear]=[PerformanceLog].[ProfileYear]) ORDERED FORWARD)
Quote:
--Hash Match(Cache,
HASH[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
--Assert(WHEREIf ([Expr1358]>1) then 0 else
NULL))
--Stream
Aggregate(DEFINE[Expr1358]=Count(*),
[Expr1359]=ANY([PerformanceLog].[YearEndRating])))

Quote:
--Filter(WHEREConvert([PerformanceLog].[ProfileYear])=Convert([Perfor
manceLog].[ProfileYear])-1))
--Index
Spool(SEEK[PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
--Clustered Index
Scan(OBJECT[PDMS].[dbo].[PerformanceLog].[PK_PL]))


-----------

Here is the Execution plan Text when I just run the SQL for the view
with the same criteria. Also execution time was 2 seconds.

Quote:
--Compute
Scalar(DEFINE[Expr1073]=isnull([EmployeeDemographic].[UPN], ''),
[Expr1074]=isnull([EmployeeDemographic].[ProfileYear], ' '),
[Expr1075]=isnull(isnull([EmployeeDemographic].[FirstName], ''), ''),
[Expr1076]=isnull(isnull([EmployeeDemographic].[MiddleInit], ''), ''),
[Expr1077]=isnull(isnull([EmployeeDemographic].[LastName], ''), ''),
[Expr1078]=isnull(isnull([EmployeeDemographic].[Name], ''), ''),
[Expr1079]=isnull(isnull([EmployeeDemographic].[CompanyCode], ''), ''),
[Expr1080]=isnull(isnull([Organization].[OrganizationID], 0), 0),
[Expr1081]=isnull(isnull([EmployeeDemographic].[CompanyName], ''), ''),
[Expr1082]=isnull(isnull([EmployeeDemographic].[CounseleeNotesID], ''),
''), [Expr1083]=isnull(isnull([EmployeeDemographic].[email], ''), ''),
[Expr1084]=isnull(isnull([EmployeeDemographic].[CounselorUPN], ''), ''),
[Expr1085]=isnull(isnull([EmployeeDemographic].[Name], ''), ''),
[Expr1086]=isnull(isnull([EmployeeDemographic].[CounselorNotesID], ''),
''), [Expr1087]=isnull(isnull([EmployeeDemographic].[CounselorEmail],
''), ''),
[Expr1088]=isnull(isnull([EmployeeDemographic].[AreaOfficeCode], ''),
''), [Expr1089]=isnull(isnull([EmployeeDemographic].[AreaOffice], ''),
''), [Expr1090]=isnull(isnull([EmployeeDemographic].[TeamLeaderUPN],
''), ''), [Expr1091]=isnull(isnull([EmployeeDemographic].[Name], ''),
''), [Expr1092]=isnull(isnull([EmployeeDemographic].[TeamLeaderNotesID],
''), ''),
[Expr1093]=isnull(isnull([EmployeeDemographic].[TeamLeaderEmail], ''),
''), [Expr1094]=isnull(isnull([EmployeeDemographic].[AreaOfficeCode],
''), ''), [Expr1095]=isnull(isnull([EmployeeDemographic].[AreaOffice],
''), ''),
[Expr1096]=isnull(isnull([EmployeeDemographic].[AreaOfficeCode], ''),
''), [Expr1097]=isnull(isnull([EmployeeDemographic].[AreaOffice], ''),
''), [Expr1098]=isnull(isnull([EmployeeDemographic].[Sector], ''), ''),
[Expr1099]=isnull(isnull([EmployeeDemographic].[BusinessUnitCode], ''),
''), [Expr1100]=isnull(isnull([EmployeeDemographic].[ServiceLine], ''),
''),
[Expr1101]=isnull(isnull([EmployeeDemographic].[EmployeeStatusCode],
''), ''),
[Expr1102]=isnull(isnull([EmployeeDemographic].[EmployeeStatus], ''),
''), [Expr1103]=isnull(isnull([EmployeeDemographic].[CountryCode], ''),
''), [Expr1104]=isnull(isnull([EmployeeDemographic].[eTeam], ''), ''),
[Expr1105]=isnull(isnull([EmployeeDemographic].[ProductName], ''), ''),
[Expr1106]=isnull(isnull([EmployeeDemographic].[PrimaryAlignment], ''),
''), [Expr1107]=isnull(isnull([EmployeeDemographic].[Profession], ''),
''), [Expr1108]=isnull(isnull([EmployeeDemographic].[CareerPathID], 0),
0), [Expr1109]=isnull(isnull([CareerPath].[CareerPath], ''), ''),
[Expr1110]=isnull(isnull([Rank].[PracticeAreaDesc], ''), ''),
[Expr1111]=isnull(isnull([Rank].[PracticeAreaID], 0), 0),
[Expr1112]=isnull(isnull([EmployeeDemographic].[RankID], ''), ''),
[Expr1113]=isnull(isnull([EmployeeDemographic].[RankName], ''), ''),
[Expr1114]=isnull(isnull([EmployeeDemographic].[Specialty], ''), ''),
[Expr1115]=isnull(isnull(Convert([EmployeeDemographic].[RecentHireDate])
, ''), ''), [Expr1116]=isnull(If
([EmployeeDemographic].[PriorServiceDate]<>'Jan 1 1900 12:00AM' AND
[EmployeeDemographic].[PriorServiceDate]<>'May 24 2000 12:00AM') then
Convert(Convert([EmployeeDemographic].[PriorServiceDate])) else If
((isnull([EmployeeDemographic].[PriorServiceDate], 'Jan 1 1900
12:00AM')='Jan 1 1900 12:00AM' AND
[EmployeeDemographic].[RecentHireDate]<>'May 24 2000 12:00AM') AND
[EmployeeDemographic].[RecentHireDate]<>'Jan 1 1900 12:00AM') then
Convert(Convert([EmployeeDemographic].[RecentHireDate])) else If
([EmployeeDemographic].[PriorServiceDate]='May 24 2000 12:00AM' AND
isnull([EmployeeDemographic].[RecentHireDate], 'Jan 1 1900
12:00AM')='Jan 1 1900 12:00AM') then
Convert(Convert([EmployeeDemographic].[PriorServiceDate])) else If
([EmployeeDemographic].[RecentHireDate]='May 24 2000 12:00AM' AND
isnull([EmployeeDemographic].[PriorServiceDate], 'Jan 1 1900
12:00AM')='Jan 1 1900 12:00AM') then Convert..
Quote:
--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))
--Nested Loops(Left Outer Join, OUTER
REFERENCES[EmployeeDemographic].[UPN],
[EmployeeDemographic].[ProfileYear]))
Quote:
|--Bookmark Lookup(BOOKMARK[Bmk1004]),
OBJECT[PDMS].[dbo].[ExecutivePromotion] AS [EP]))
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[PLId]))
| |--Compute
Scalar(DEFINE[Expr1036]=Convert([PerformanceLog].[PlanCounseleeSignTim
e])+' '+Convert([PerformanceLog].[PlanCounseleeSignTime]),
[Expr1037]=Convert([PerformanceLog].[PlanCounselorSignTime])+'
'+Convert([PerformanceLog].[PlanCounselorSignTime]),
[Expr1038]=Convert([PerformanceLog].[MidYearCounseleeSignTime])+'
'+Convert([PerformanceLog].[MidYearCounseleeSignTime]),
[Expr1039]=Convert([PerformanceLog].[MidYearCounselorSignTime])+'
'+Convert([PerformanceLog].[MidYearCounselorSignTime]),
[Expr1381]=[Expr1381], [Expr1383]=[Expr1383], [Expr1385]=[Expr1385],
[Expr1387]=[Expr1387],
[Expr1064]=Convert([PerformanceLog].[YearEndCounseleeSignTime])+'
'+Convert([PerformanceLog].[YearEndCounseleeSignTime]),
[Expr1065]=Convert([PerformanceLog].[YearEndCounselorSignTime])+'
'+Convert([PerformanceLog].[YearEndCounselorSignTime]), [Expr1066]=If
(Convert([PerformanceLog].[PlanCounseleeSign])=1 AND
Convert([PerformanceLog].[PlanCounselorSign])=1) then 1 else 0,
[Expr1067]=If (Convert([PerformanceLog].[MidYearCounseleeSign])=1 AND
Convert([PerformanceLog].[MidYearCounselorSign])=1) then 1 else 0,
[Expr1068]=If (Convert([PerformanceLog].[YearEndCounseleeSign])=1 AND
Convert([PerformanceLog].[YearEndCounselorSign])=1) then 1 else 0,
[Expr1069]=If (isnull([PerformanceLog].[TeamDiscussionLeaderUPN],
'')='') then Convert([EmployeeDemographic].[TeamLeaderUPN]) else
isnull([PerformanceLog].[TeamDiscussionLeaderUPN], ''), [Expr1070]=If
(isnull([PerformanceLog].[TeamDiscussionLeaderUPN], '')='') then
[EmployeeDemographic].[Name] else
Convert(isnull([PerformanceLog].[TeamDiscussionLeaderName], '')),
[Expr1071]=If (isnull([PerformanceLog].[LCRRankid], '')='') then
isnull([EmployeeDemographic].[RankID], '') else
isnull([PerformanceLog].[LCRRankid], ''), [Expr1072]=If
(isnull([PerformanceLog].[LCRRankid], '')='') then
isnull([EmployeeDemographic].[RankName], '') else
isnull([PerformanceLog].[LCRRankName], '')))
Quote:
| | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | |--Bookmark
Lookup(BOOKMARK[Bmk1014]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | |--Nested Loops(Left Outer
Join, OUTER REFERENCES[EmployeeDemographic].[ProfileYear],
[EmployeeDemographic].[TeamLeaderUPN]))
Quote:
| | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | |--Bookmark
Lookup(BOOKMARK[Bmk1012]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | |--Nested
Loops(Left Outer Join, OUTER
REFERENCES[EmployeeDemographic].[ProfileYear],
[EmployeeDemographic].[CounselorUPN]))
Quote:
| | | |--Nested
Loops(Left Outer Join, OUTER
REFERENCES[EmployeeDemographic].[CareerPathID]))
Quote:
| | | |
--Nested Loops(Left Outer Join, OUTER
REFERENCES[EmployeeDemographic].[RankID],
[EmployeeDemographic].[RankName]))
Quote:
| | | | |
--Nested Loops(Left Outer Join, OUTER
REFERENCES[EmployeeDemographic].[CompanyCode]))
| | | | |
|--Nested Loops(Inner Join)
| | | | |
| |--Nested Loops(Inner Join)
| | | | |
| | |--Compute
Scalar(DEFINE[Metric].[LearningAPAhours]=isnull([Metric].[AuthorHours]
, Convert(0))+isnull([Metric].[LeaderHours],
Convert(0))+isnull([Metric].[LeaderAPHours], Convert(0)),
[Metric].[LearningTotal]=isnull([Metric].[LearningParticipantHours],
Convert(0))+isnull([Metric].[AuthorHours],
Convert(0))+isnull([Metric].[LeaderHours],
Convert(0))+isnull([Metric].[LeaderAPHours], Convert(0)),
[Metric].[InvestmentUtilizationPercentage]=If
(isnull([Metric].[AvailableHours], 0)=Convert(0)) then Convert(0) else
Convert((Convert(100)*Convert(isnull([Metric].[InvestmentHours],
0))/Convert([Metric].[AvailableHours]))),
[Metric].[UtilizationPercentage]=If (isnull([Metric].[AvailableHours],
0)=Convert(0)) then Convert(0) else
Convert((Convert(100)*Convert(isnull([Metric].[ChargedHours],
0))/Convert([Metric].[AvailableHours]))),
[Metric].[UtilizationPeriod]='Oct 1, '+[Metric].[ProfileYear]+' -
'+Convert([Metric].[UtilizationLastImport])))
Quote:
| | | | |
| | | |--Clustered Index
Seek(OBJECT[PDMS].[dbo].[Metric].[PK_Metrics]),
SEEK[Metric].[UPN]='1601352' AND [Metric].[ProfileYear]='2003')
ORDERED FORWARD)
Quote:
| | | | |
| |
--Filter(WHEREConvert(isnull([EmployeeDemographic].[Executive],
0))=0))
| | | | |
| | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=[EmployeeDemographic].[First
Name]+If ([EmployeeDemographic].[MiddleInit]=NULL OR
[EmployeeDemographic].[MiddleInit]='') then Convert(' ') else ('
'+[EmployeeDemographic].[MiddleInit]+'
')+[EmployeeDemographic].[LastName]))
Quote:
| | | | |
| | |--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
| | | | |
| | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]='1601352' AND
[EmployeeDemographic].[ProfileYear]='2003') ORDERED FORWARD)
Quote:
| | | | |
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))
| | | | |
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))
| | | | |
| | |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))
| | | | |
| | | |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]))
| | | | |
| | | | |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[PLId]))
| | | | |
| | | | | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | | |
| | | | | | |--Bookmark
Lookup(BOOKMARK[Bmk1032]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | | |
| | | | | | |--Nested Loops(Left Outer
Join, OUTER REFERENCES[PerformanceLog].[YearEndCounselorSignUPN],
[PerformanceLog].[ProfileYear]))
Quote:
| | | | |
| | | | | | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | | |
| | | | | | | |--Bookmark
Lookup(BOOKMARK[Bmk1030]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | | |
| | | | | | | |--Nested
Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[YearEndCounseleeSignUPN],
[PerformanceLog].[ProfileYear]))
Quote:
| | | | |
| | | | | | |
--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | | |
| | | | | | | |
--Bookmark Lookup(BOOKMARK[Bmk1028]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
| | | | |
| | | | | | | |
--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[MidYearCounselorSignUPN],
[PerformanceLog].[ProfileYear]))
Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Bookmark Lookup(BOOKMARK[Bmk1026]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[MidYearCounseleeSignUPN],
[PerformanceLog].[ProfileYear]))
Quote:
--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
|--Bookmark Lookup(BOOKMARK[Bmk1024]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[PlanCounselorSignUPN],
[PerformanceLog].[ProfileYear]))
Quote:
| | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | |--Bookmark Lookup(BOOKMARK[Bmk1022]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
| | | | |--Nested Loops(Left Outer Join, OUTER
REFERENCES[PerformanceLog].[PlanCounseleeSignUPN],
[PerformanceLog].[ProfileYear]))
Quote:
| | | | |--Compute
Scalar(DEFINE[EmployeeDemographic].[Name]=EmployeeDemographic.[FirstNa
me]+If (EmployeeDemographic.[MiddleInit]=NULL OR
EmployeeDemographic.[MiddleInit]='') then Convert(' ') else ('
'+EmployeeDemographic.[MiddleInit]+' ')+EmployeeDemographic.[LastName]))
Quote:
| | | | | |--Bookmark
Lookup(BOOKMARK[Bmk1020]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | | | |--Nested Loops(Left
Outer Join, OUTER REFERENCES[EmployeeDemographic].[ProfileYear],
[EmployeeDemographic].[TeamLeaderUPN]))
Quote:
| | | | | | |--Nested
Loops(Inner Join)
| | | | | | | |--Bookmark
Lookup(BOOKMARK[Bmk1018]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | | | | | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]='1601352' AND
[EmployeeDemographic].[ProfileYear]='2003') ORDERED FORWARD)
Quote:
| | | | | | | |--Compute
Scalar(DEFINE[PerformanceLog].[PLCompleted]=If
(((((Convert([PerformanceLog].[PlanCounseleeSign])=1 AND
Convert([PerformanceLog].[PlanCounselorSign])=1) AND
Convert([PerformanceLog].[MidYearCounseleeSign])=1) AND
Convert([PerformanceLog].[MidYearCounselorSign])=1) AND
Convert([PerformanceLog].[YearEndCounseleeSign])=1) AND
Convert([PerformanceLog].[YearEndCounselorSign])=1) then 1 else 0,
[PerformanceLog].[Status]=If
(Convert([PerformanceLog].[YearEndCounselorSign])=1 AND
Convert([PerformanceLog].[YearEndCounseleeSign])=1) then
Convert('Performance Log Completed') else If
(Convert([PerformanceLog].[MidYearCounselorSign])=1 AND
Convert([PerformanceLog].[MidYearCounseleeSign])=1) then 'Counselor and
Counselee are working on Year-End section' else If
(Convert([PerformanceLog].[PlanCounseleeSign])=1 AND
Convert([PerformanceLog].[PlanCounselorSign])=1) then 'Counselor and
Counselee are working on Mid-Year section' else 'Counselor and Counselee
are working on Planning section'))
Quote:
| | | | | | |
--Clustered Index Scan(OBJECT[PDMS].[dbo].[PerformanceLog].[PK_PL]),
WHERE[PerformanceLog].[UPN]='1601352' AND
[PerformanceLog].[ProfileYear]='2003'))
Quote:
| | | | | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[EmployeeDemographic].[TeamLeaderUPN]
AND
[EmployeeDemographic].[ProfileYear]=[EmployeeDemographic].[ProfileYear])
ORDERED FORWARD)
Quote:
| | | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[PlanCounseleeSignUPN
] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[PlanCounselorSignUPN
] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[MidYearCounseleeSign
UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[MidYearCounselorSign
UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | | | |
| | | | | | |
--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[YearEndCounseleeSign
UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | | | |
| | | | | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[YearEndCounselorSign
UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | | | |
| | | | | |--Index
Seek(OBJECT[PDMS].[dbo].[ExecutivePromotion].[IX_ExecutivePromotion]),
SEEK[ExecutivePromotion].[PLID]=[PerformanceLog].[PLId]) ORDERED
FORWARD)
Quote:
| | | | |
| | | | |--Hash Match(Cache,
HASH[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
| | | | |
| | | | |--Assert(WHEREIf ([Expr1380]>1)
then 0 else NULL))
| | | | |
| | | | |--Stream
Aggregate(DEFINE[Expr1380]=Count(*),
[Expr1381]=ANY([Target].[LearningGoal])))
Quote:
| | | | |
| | | | |--Nested Loops(Inner Join,
OUTER REFERENCES[EmployeeDemographic].[CareerPathID],
[EmployeeDemographic].[RankID], [EmployeeDemographic].[CountryCode]))
Quote:
| | | | |
| | | | |--Bookmark
Lookup(BOOKMARK[Bmk1042]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | | |
| | | | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | | | |
| | | | |--Clustered Index
Seek(OBJECT[PDMS].[dbo].[Target].[PK_Target]),
SEEK[Target].[CareerPathID]=[EmployeeDemographic].[CareerPathID] AND
[Target].[RankID]=[EmployeeDemographic].[RankID] AND
[Target].[CountryCode]=[EmployeeDemographic].[CountryCode] AND
[Target].[ProfileYear]=[PerformanceLog].[ProfileYear]) ORDERED FORWARD)
Quote:
| | | | |
| | | |--Hash Match(Cache,
HASH[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
| | | | |
| | | |--Assert(WHEREIf ([Expr1382]>1) then 0
else NULL))
| | | | |
| | | |--Stream
Aggregate(DEFINE[Expr1382]=Count(*),
[Expr1383]=ANY([Target].[SalesGoal])))
Quote:
| | | | |
| | | |--Nested Loops(Inner Join,
OUTER REFERENCES[EmployeeDemographic].[CareerPathID],
[EmployeeDemographic].[RankID], [EmployeeDemographic].[CountryCode]))
Quote:
| | | | |
| | | |--Bookmark
Lookup(BOOKMARK[Bmk1048]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | | |
| | | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | | | |
| | | |--Clustered Index
Seek(OBJECT[PDMS].[dbo].[Target].[PK_Target]),
SEEK[Target].[CareerPathID]=[EmployeeDemographic].[CareerPathID] AND
[Target].[RankID]=[EmployeeDemographic].[RankID] AND
[Target].[CountryCode]=[EmployeeDemographic].[CountryCode] AND
[Target].[ProfileYear]=[PerformanceLog].[ProfileYear]) ORDERED FORWARD)
Quote:
| | | | |
| | |--Hash Match(Cache,
HASH[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
| | | | |
| | |--Assert(WHEREIf ([Expr1384]>1) then 0 else
NULL))
| | | | |
| | |--Stream
Aggregate(DEFINE[Expr1384]=Count(*),
[Expr1385]=ANY([Target].[UtilizationGoal])))
Quote:
| | | | |
| | |--Nested Loops(Inner Join, OUTER
REFERENCES[EmployeeDemographic].[CareerPathID],
[EmployeeDemographic].[RankID], [EmployeeDemographic].[CountryCode]))
Quote:
| | | | |
| | |--Bookmark
Lookup(BOOKMARK[Bmk1054]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | | |
| | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | | | |
| | |--Clustered Index
Seek(OBJECT[PDMS].[dbo].[Target].[PK_Target]),
SEEK[Target].[CareerPathID]=[EmployeeDemographic].[CareerPathID] AND
[Target].[RankID]=[EmployeeDemographic].[RankID] AND
[Target].[CountryCode]=[EmployeeDemographic].[CountryCode] AND
[Target].[ProfileYear]=[PerformanceLog].[ProfileYear]) ORDERED FORWARD)
Quote:
| | | | |
| |--Hash Match(Cache,
HASH[PerformanceLog].[ProfileYear], [PerformanceLog].[UPN]),
RESIDUAL[PerformanceLog].[ProfileYear]=[PerformanceLog].[ProfileYear]
AND [PerformanceLog].[UPN]=[PerformanceLog].[UPN]))
Quote:
| | | | |
| |--Assert(WHEREIf ([Expr1386]>1) then 0 else
NULL))
| | | | |
| |--Stream
Aggregate(DEFINE[Expr1386]=Count(*),
[Expr1387]=ANY([Target].[RevenueManagedGoal])))
Quote:
| | | | |
| |--Nested Loops(Inner Join, OUTER
REFERENCES[EmployeeDemographic].[CareerPathID],
[EmployeeDemographic].[RankID], [EmployeeDemographic].[CountryCode]))
Quote:
| | | | |
| |--Bookmark
Lookup(BOOKMARK[Bmk1060]),
OBJECT[PDMS].[dbo].[EmployeeDemographic]))
Quote:
| | | | |
| | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[PK_EmployeeDemographic]
), SEEK[EmployeeDemographic].[UPN]=[PerformanceLog].[UPN] AND
[EmployeeDemographic].[ProfileYear]=[PerformanceLog].[ProfileYear])
ORDERED FORWARD)
Quote:
| | | | |
| |--Clustered Index
Seek(OBJECT[PDMS].[dbo].[Target].[PK_Target]),
SEEK[Target].[CareerPathID]=[EmployeeDemographic].[CareerPathID] AND
[Target].[RankID]=[EmployeeDemographic].[RankID] AND
[Target].[CountryCode]=[EmployeeDemographic].[CountryCode] AND
[Target].[ProfileYear]=[PerformanceLog].[ProfileYear]) ORDERED FORWARD)
Quote:
| | | | |
|--Index
Seek(OBJECT[PDMS].[dbo].[Organization].[IX_Organization]),
SEEK[Organization].[Companycode]=[EmployeeDemographic].[CompanyCode])
ORDERED FORWARD)
Quote:
| | | | |
--Clustered Index Seek(OBJECT[PDMS].[dbo].[Rank].[PK_Rank]),
SEEK[Rank].[RankID]=[EmployeeDemographic].[RankID] AND
[Rank].[RankName]=[EmployeeDemographic].[RankName]) ORDERED FORWARD)
Quote:
| | | |
--Clustered Index
Seek(OBJECT[PDMS].[dbo].[CareerPath].[PK_CareerPath]),
SEEK[CareerPath].[CareerPathID]=[EmployeeDemographic].[CareerPathID])
ORDERED FORWARD)
Quote:
| | | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[EmployeeDemographic].[CounselorUPN]
AND
[EmployeeDemographic].[ProfileYear]=[EmployeeDemographic].[ProfileYear])
ORDERED FORWARD)
Quote:
| | |--Index
Seek(OBJECT[PDMS].[dbo].[EmployeeDemographic].[IX_EmployeeDemographic]
),
SEEK[EmployeeDemographic].[UPN]=[EmployeeDemographic].[TeamLeaderUPN]
AND
[EmployeeDemographic].[ProfileYear]=[EmployeeDemographic].[ProfileYear])
ORDERED FORWARD)
Quote:
| |--Index
Seek(OBJECT[PDMS].[dbo].[ExecutivePromotion].[IX_ExecutivePromotion]
AS [EP]), SEEK[EP].[PLID]=[PerformanceLog].[PLId]) ORDERED FORWARD)
Quote:
|--Clustered Index
Seek(OBJECT[PDMS].[dbo].[MetricSummary].[PK_ExecutivePromotionMetricSu
mmary] AS [ms]), SEEK[ms].[UPN]=[EmployeeDemographic].[UPN] AND
[ms].[ProfileYear]=[EmployeeDemographic].[ProfileYear]) ORDERED FORWARD)
Quote:
--Table Spool
--Assert(WHEREIf ([Expr1356]>1) then 0 else NULL))
--Stream Aggregate(DEFINE[Expr1356]=Count(*),
[Expr1357]=ANY([PerformanceLog].[YearEndRating])))
--Clustered Index
Scan(OBJECT[PDMS].[dbo].[PerformanceLog].[PK_PL]),
WHERE[PerformanceLog].[UPN]=[PerformanceLog].[UPN] AND
Convert([PerformanceLog].[ProfileYear])=Convert([PerformanceLog].[Profil
eYear])-1))










*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Reply With Quote
  #3  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: View Performance - 08-04-2003 , 03:57 PM


bharilal (bharilal (AT) hotmail (DOT) com) writes:
Quote:
SELECT * FROM vwEmployee where EmpID = 12
Execution Time: 50 seconds

SELECT *
FROM tblEmployee
INNER JOIN Country ON EmpCountryID = CountryID
LEFT OUTER JOIN Address ON EmpAddID = AddID
WHERE EmpID = 12

Execution Time: 2 Seconds

Thanks in advance for the help!
A view is basically a macro, so there should not be any difference
between the view and the query defintion itself. Not even if the
view is indexed, because SQL Server should be able to use the
view anyway.

One possibility is that when you ran the view, all data was on disk,
so everything had to be loaded into memory. Thus, when you ran the
SQL statement, all data was in cache, which gave much faster response.

To factor this out, you can issue DBCC DROPCLEANBUFFERS to flush the
cache entirely. Don't do this on a production server though, as you
are likely to cause a temporary slowdown.

Another possibility, is that auto-statistics hit. When SQL Server
needs to scan a table, it may take the occasion to gather statistics
on columns involved in the query. Next time you issue the query,
SQL Server may use that statistics and come up with a faster query
plan.

I should here say that this applies to SQL 2000 and SQL 7. If you are
on 6.5, the situation is somewhat different.

--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Reply With Quote
  #4  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: View Performance - 08-05-2003 , 04:56 PM


B Haril (bharilal (AT) hotmail (DOT) com) writes:
Quote:
Looks like there was a text limitation when I tried to insert the
execution plan for both queries. But looking at the query plan I can see
that there is significant difference in what is happening when I do the
query off of the view and when I do the query not using the view but the
SQL to the view.
This means that the difference is not only because you have brought
the data into cache. Since you have run both queries alternately,
neither does it seem that a auto-update of statistics gave you a
better plan the second time round.

Still I think you should get the same plan, with everything else
equal. But you query was quite complex, judging from the query plan.
Apparently the query builds on other views.

Quote:
The select using just the SQL incurred 86% of the cost doing a clustered
index scan against the table. This query took less than a second to run.

Is there anything I can do to force the view to do the same as the SQL?
You can use an index hint. To force a clustered index scan, you can
say:

SELECT * FROM tbl WITH (INDEX = 1)

But since the view referred to other views, you would have to refer
to the underlying table. (Unless those views are indexed.)


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top