January 23, 2009 at 12:11 pm
I'm decomposing a complex select statement that is having performance problems, and I have already found a condition I can't explain.
I have a table, person, which has 12,003 rows. It contains a column of type uniqueidentifier, which has a corresponding value in a column of the table, validationlist, which is basically a lookup table of key/value pairs. validationlist has a non-clustered unique index on a column of type uniqueidentifier which gets its value from a newid() function. validationlist has 21,681 rows.
An execution plan for the following select shows a table scan on validation list:
select per.col1,
per.col2,
(select desc from validationlist where vID = per.col3) as col3
from person
I expect a table scan on person, since I'm requesting all rows of this table, but I don't understand why a table scan is being performed on validationlist. I especially don't understand, because when I add a where condition for the person table, the execution plan shows an index seek for validation list:
select per.col1,
per.col2,
(select desc from validationlist where vID = per.col3) as col3
from person
where person.col1 = 'some value'
In this simple example, the performance is no big issue, but person is joined to many other tables, each of which have several unique identifier columns that have in-line selects back to validationlist. All those in-line selects cause table scans of validationlist and the performance is unacceptable.
I tried using a hint like the one below, but even though the execution plan now states it is using an index seek, I see no change in performance:
select per.col1,
per.col2,
(select desc from validationlist with (index(ix_validationlist_vID)) where vID = per.col3) as col3
from person
Is there something I'm missing. I noticed that the "foreign key" in person is not explicitly defined as a foreign key. Does this have an impact on the choices made in the execution plan?
TIA for any help.
Ed
January 23, 2009 at 12:19 pm
It's because it's going to have to get 12,003 rows out of 21,681. At that point, it's easier to do a scan and join to it than to do one-at-a-time lookups. In other words, it's what you're going to get.
What I'm not clear on, is why do this as an inline select at all, why not just join the tables and pull the columns? That's what SQL is going to do to it, in the execution plan, so why not write the code that way in the first place?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 1:07 pm
See here - http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Edit: don't use index hints unless you are really sure you know what they're doing and why the optimiser didn't pick that index itself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2009 at 1:42 pm
Thanks for the info. So even if 20 coded columns need to access the lookup table to decode to descriptions, a table scan will be performed 20 times? The sql below is a pruning of the actual select statement (it's maybe 10-15% of the total statement), re-written to use joins instead of in-line selects, which is used to create a view. If there is no further optimization of this select, should it be used to create a materialized view instead?
Thanks,
Ed
SELECT
--- Employee Data
-- Person fields
per.PersonID,
per.PartyID,
svl01.validationlistdesc as SalutationValPartyID,
svl02.validationlistdesc as PostTitleValPartyID,
per.FirstName,
per.MiddleName,
per.LastName,
per.KnownAs,
per.InternalEmailAddress,
per.externalemailaddress as EmpNotesID,
-- Combined person fields
(per.lastname + ', ' + per.firstname) as fullname,
-- Assignmentdetails fields
AssDet.StartDate,
AssDet.EndDate,
svl03.validationlistdesc as OrgUnit1,
svl04.validationlistdesc as OrgUnit2,
svl05.validationlistdesc as OrgUnit3,
svl06.validationlistdesc as OrgUnit4,
svl07.validationlistdesc as ADCountryValPartyID,
svl08.validationlistdesc as LocationValPartyID,
svl09.validationlistdesc as FunctionValPartyID,
AssDet.HoursPerWeek,
svl10.validationlistdesc as OvertimeValPartyID ,
AssDet.FTE,
svl11.validationlistdesc as StatusValPartyID,
svl12.validationlistdesc as AssigmentDetailsStatus,
AssDet.ExpatMarker,
AssDet.RepatriateDate,
svl13.validationlistdesc as PTSPayGroupValPartyID,
svl14.validationlistdesc as PTSRateTypeValPartyID,
svl15.validationlistdesc as PTSFLSAStatusValPartyID,
svl16.validationlistdesc as ShiftDetailsValPartyID,
svl17.validationlistdesc as PTSWorkScheduleValPartyID ,
svl18.validationlistdesc as EEO1JobCategoryDesc,
svl19.validationlistcode as EEO1JobCategoryCode,
svl20.validationlistcode as workmanscomp
FROMdbo.Person per
-- Employee Joins
LEFT OUTER JOIN dbo.Assignment *** ON ***.PersonPartyID = per.PartyID
AND ***.StartDate = (select max(startdate) from Assignment Ass2 where per.partyid = Ass2.personpartyid
AND startdate <= CONVERT(varchar(10), GETDATE(), 112))
LEFT OUTER JOIN dbo.AssignmentDetails AssDet ON ***.AssignmentPartyID = AssDet.AssignmentPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl01 on svl01.ValidationPartyID = per.SalutationValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl02 on svl02.ValidationPartyID = per.PostTitleValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl03 on svl03.ValidationPartyID = AssDet.OrgUnit1
LEFT OUTER JOIN dbo.sysvalidationlist svl04 on svl04.ValidationPartyID = AssDet.OrgUnit2
LEFT OUTER JOIN dbo.sysvalidationlist svl05 on svl05.ValidationPartyID = AssDet.OrgUnit3
LEFT OUTER JOIN dbo.sysvalidationlist svl06 on svl06.ValidationPartyID = AssDet.OrgUnit4
LEFT OUTER JOIN dbo.sysvalidationlist svl07 on svl07.ValidationPartyID = AssDet.CountryValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl08 on svl08.ValidationPartyID = AssDet.LocationValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl09 on svl09.ValidationPartyID = AssDet.FunctionValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl10 on svl10.ValidationPartyID = AssDet.OvertimeValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl11 on svl11.ValidationPartyID = AssDet.StatusValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl12 on svl12.ValidationPartyID = AssDet.AssigmentDetailsStatus
LEFT OUTER JOIN dbo.sysvalidationlist svl13 on svl13.ValidationPartyID = AssDet.PTSPayGroupValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl14 on svl14.ValidationPartyID = AssDet.PTSRateTypeValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl15 on svl15.ValidationPartyID = AssDet.PTSFLSAStatusValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl16 on svl16.ValidationPartyID = AssDet.ShiftDetailsValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl17 on svl17.ValidationPartyID = AssDet.PTSWorkScheduleValPartyID
LEFT OUTER JOIN dbo.sysvalidationlist svl18 on svl18.ValidationPartyID = AssDet.EEO1JobCategoryValPartyid
LEFT OUTER JOIN dbo.sysvalidationlist svl19 on svl19.ValidationPartyID = AssDet.EEO1JobCategoryValPartyid
LEFT OUTER JOIN dbo.sysvalidationlist svl20 on svl20.ValidationPartyID = AssDet.PTSWorkmansCompValPartyID
Execution Plan:
"110112063.53NullNullNull31.25069SELECTFalseNull
|--Hash Match(Right Outer Join, HASH:([svl20].[ValidationPartyID])=([AssDet].[PTSWorkmansCompValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl20].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSWorkmansCompValPartyID] as [AssDet].[PTSWorkmansCompValPartyID]))121Hash MatchRight Outer JoinHASH:([svl20].[ValidationPartyID])=([AssDet].[PTSWorkmansCompValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl20].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSWorkmansCompValPartyID] as [AssDet].[PTSWorkmansCompValPartyID])12063.5300.3040218219931.25069[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [svl14].[ValidationListDesc], [svl15].[ValidationListDesc], [svl16].[ValidationListDesc], [svl17].[ValidationListDesc], [svl18].[ValidationListDesc], [svl19].[ValidationListCode], [svl20].[ValidationListCode], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl20]))132Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl20])[svl20].[ValidationPartyID], [svl20].[ValidationListCode]64650.36756940.0072685370.3748379[svl20].[ValidationPartyID], [svl20].[ValidationListCode]PLAN_ROWFalse1
|--Merge Join(Right Outer Join, MERGE:([svl19].[ValidationPartyID])=([AssDet].[EEO1JobCategoryValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl19].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[EEO1JobCategoryValPartyID] as [AssDet].[EEO1JobCategoryValPartyID]))142Merge JoinRight Outer JoinMERGE:([svl19].[ValidationPartyID])=([AssDet].[EEO1JobCategoryValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl19].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[EEO1JobCategoryValPartyID] as [AssDet].[EEO1JobCategoryValPartyID])12063.5300.04581334220330.57183[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[PTSWorkmansCompValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [svl14].[ValidationListDesc], [svl15].[ValidationListDesc], [svl16].[ValidationListDesc], [svl17].[ValidationListDesc], [svl18].[ValidationListDesc], [svl19].[ValidationListCode], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl19]), ORDERED FORWARD)154Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl19]), ORDERED FORWARD[svl19].[ValidationPartyID], [svl19].[ValidationListCode]64650.36756940.0072685370.3748379[svl19].[ValidationPartyID], [svl19].[ValidationListCode]PLAN_ROWFalse1
|--Merge Join(Right Outer Join, MERGE:([svl18].[ValidationPartyID])=([AssDet].[EEO1JobCategoryValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl18].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[EEO1JobCategoryValPartyID] as [AssDet].[EEO1JobCategoryValPartyID]))164Merge JoinRight Outer JoinMERGE:([svl18].[ValidationPartyID])=([AssDet].[EEO1JobCategoryValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl18].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[EEO1JobCategoryValPartyID] as [AssDet].[EEO1JobCategoryValPartyID])12063.5300.04581334220730.15117[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [svl14].[ValidationListDesc], [svl15].[ValidationListDesc], [svl16].[ValidationListDesc], [svl17].[ValidationListDesc], [svl18].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl18]), ORDERED FORWARD)176Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl18]), ORDERED FORWARD[svl18].[ValidationPartyID], [svl18].[ValidationListDesc]64650.36756940.00726851270.3748379[svl18].[ValidationPartyID], [svl18].[ValidationListDesc]PLAN_ROWFalse1
|--Sort(ORDER BY:([AssDet].[EEO1JobCategoryValPartyID] ASC))186SortSortORDER BY:([AssDet].[EEO1JobCategoryValPartyID] ASC)12063.530.011261260.7508503210529.73052[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [svl14].[ValidationListDesc], [svl15].[ValidationListDesc], [svl16].[ValidationListDesc], [svl17].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl17].[ValidationPartyID])=([AssDet].[PTSWorkScheduleValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl17].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSWorkScheduleValPartyID] as [AssDet].[PTSWorkScheduleValPartyID]))198Hash MatchRight Outer JoinHASH:([svl17].[ValidationPartyID])=([AssDet].[PTSWorkScheduleValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl17].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSWorkScheduleValPartyID] as [AssDet].[PTSWorkScheduleValPartyID])12063.5300.686011210528.96841[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [svl14].[ValidationListDesc], [svl15].[ValidationListDesc], [svl16].[ValidationListDesc], [svl17].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl17]))1109Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl17])[svl17].[ValidationPartyID], [svl17].[ValidationListDesc]64650.36756940.00726851270.3748379[svl17].[ValidationPartyID], [svl17].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl16].[ValidationPartyID])=([AssDet].[ShiftDetailsValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl16].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[ShiftDetailsValPartyID] as [AssDet].[ShiftDetailsValPartyID]))1119Hash MatchRight Outer JoinHASH:([svl16].[ValidationPartyID])=([AssDet].[ShiftDetailsValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl16].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[ShiftDetailsValPartyID] as [AssDet].[ShiftDetailsValPartyID])12063.5300.686011201927.90755[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [svl14].[ValidationListDesc], [svl15].[ValidationListDesc], [svl16].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl16]))11211Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl16])[svl16].[ValidationPartyID], [svl16].[ValidationListDesc]64650.36756940.00726851270.3748379[svl16].[ValidationPartyID], [svl16].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl15].[ValidationPartyID])=([AssDet].[PTSFLSAStatusValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl15].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSFLSAStatusValPartyID] as [AssDet].[PTSFLSAStatusValPartyID]))11311Hash MatchRight Outer JoinHASH:([svl15].[ValidationPartyID])=([AssDet].[PTSFLSAStatusValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl15].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSFLSAStatusValPartyID] as [AssDet].[PTSFLSAStatusValPartyID])12063.5300.686011193326.8467[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [svl14].[ValidationListDesc], [svl15].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl15]))11413Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl15])[svl15].[ValidationPartyID], [svl15].[ValidationListDesc]64650.36756940.00726851270.3748379[svl15].[ValidationPartyID], [svl15].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl14].[ValidationPartyID])=([AssDet].[PTSRateTypeValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl14].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSRateTypeValPartyID] as [AssDet].[PTSRateTypeValPartyID]))11513Hash MatchRight Outer JoinHASH:([svl14].[ValidationPartyID])=([AssDet].[PTSRateTypeValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl14].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSRateTypeValPartyID] as [AssDet].[PTSRateTypeValPartyID])12063.5300.686011184725.78585[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [svl14].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl14]))11615Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl14])[svl14].[ValidationPartyID], [svl14].[ValidationListDesc]64650.36756940.00726851270.3748379[svl14].[ValidationPartyID], [svl14].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl13].[ValidationPartyID])=([AssDet].[PTSPayGroupValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl13].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSPayGroupValPartyID] as [AssDet].[PTSPayGroupValPartyID]))11715Hash MatchRight Outer JoinHASH:([svl13].[ValidationPartyID])=([AssDet].[PTSPayGroupValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl13].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[PTSPayGroupValPartyID] as [AssDet].[PTSPayGroupValPartyID])12063.5300.686011176124.725[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [svl13].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl13]))11817Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl13])[svl13].[ValidationPartyID], [svl13].[ValidationListDesc]64650.36756940.00726851270.3748379[svl13].[ValidationPartyID], [svl13].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl12].[ValidationPartyID])=([AssDet].[AssigmentDetailsStatus]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl12].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[AssigmentDetailsStatus] as [AssDet].[AssigmentDetailsStatus]))11917Hash MatchRight Outer JoinHASH:([svl12].[ValidationPartyID])=([AssDet].[AssigmentDetailsStatus]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl12].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[AssigmentDetailsStatus] as [AssDet].[AssigmentDetailsStatus])12063.5300.686011167523.66415[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [svl12].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl12]))12019Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl12])[svl12].[ValidationPartyID], [svl12].[ValidationListDesc]64650.36756940.00726851270.3748379[svl12].[ValidationPartyID], [svl12].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl11].[ValidationPartyID])=([AssDet].[StatusValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl11].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[StatusValPartyID] as [AssDet].[StatusValPartyID]))12119Hash MatchRight Outer JoinHASH:([svl11].[ValidationPartyID])=([AssDet].[StatusValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl11].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[StatusValPartyID] as [AssDet].[StatusValPartyID])12063.5300.686011158922.60329[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [svl11].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl11]))12221Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl11])[svl11].[ValidationPartyID], [svl11].[ValidationListDesc]64650.36756940.00726851270.3748379[svl11].[ValidationPartyID], [svl11].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl10].[ValidationPartyID])=([AssDet].[OvertimeValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl10].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OvertimeValPartyID] as [AssDet].[OvertimeValPartyID]))12321Hash MatchRight Outer JoinHASH:([svl10].[ValidationPartyID])=([AssDet].[OvertimeValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl10].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OvertimeValPartyID] as [AssDet].[OvertimeValPartyID])12063.5300.686011150321.54244[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [svl10].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl10]))12423Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl10])[svl10].[ValidationPartyID], [svl10].[ValidationListDesc]64650.36756940.00726851270.3748379[svl10].[ValidationPartyID], [svl10].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl09].[ValidationPartyID])=([AssDet].[FunctionValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl09].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[FunctionValPartyID] as [AssDet].[FunctionValPartyID]))12523Hash MatchRight Outer JoinHASH:([svl09].[ValidationPartyID])=([AssDet].[FunctionValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl09].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[FunctionValPartyID] as [AssDet].[FunctionValPartyID])12063.5300.686011141720.48159[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [svl09].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl09]))12625Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl09])[svl09].[ValidationPartyID], [svl09].[ValidationListDesc]64650.36756940.00726851270.3748379[svl09].[ValidationPartyID], [svl09].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl08].[ValidationPartyID])=([AssDet].[LocationValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl08].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[LocationValPartyID] as [AssDet].[LocationValPartyID]))12725Hash MatchRight Outer JoinHASH:([svl08].[ValidationPartyID])=([AssDet].[LocationValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl08].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[LocationValPartyID] as [AssDet].[LocationValPartyID])12063.5300.686011133119.42074[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [svl08].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl08]))12827Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl08])[svl08].[ValidationPartyID], [svl08].[ValidationListDesc]64650.36756940.00726851270.3748379[svl08].[ValidationPartyID], [svl08].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl07].[ValidationPartyID])=([AssDet].[CountryValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl07].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[CountryValPartyID] as [AssDet].[CountryValPartyID]))12927Hash MatchRight Outer JoinHASH:([svl07].[ValidationPartyID])=([AssDet].[CountryValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl07].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[CountryValPartyID] as [AssDet].[CountryValPartyID])12063.5300.686011124518.35989[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [svl07].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl07]))13029Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl07])[svl07].[ValidationPartyID], [svl07].[ValidationListDesc]64650.36756940.00726851270.3748379[svl07].[ValidationPartyID], [svl07].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl06].[ValidationPartyID])=([AssDet].[OrgUnit4]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl06].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OrgUnit4] as [AssDet].[OrgUnit4]))13129Hash MatchRight Outer JoinHASH:([svl06].[ValidationPartyID])=([AssDet].[OrgUnit4]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl06].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OrgUnit4] as [AssDet].[OrgUnit4])12063.5300.686011115917.29904[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [svl06].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl06]))13231Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl06])[svl06].[ValidationPartyID], [svl06].[ValidationListDesc]64650.36756940.00726851270.3748379[svl06].[ValidationPartyID], [svl06].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl05].[ValidationPartyID])=([AssDet].[OrgUnit3]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl05].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OrgUnit3] as [AssDet].[OrgUnit3]))13331Hash MatchRight Outer JoinHASH:([svl05].[ValidationPartyID])=([AssDet].[OrgUnit3]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl05].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OrgUnit3] as [AssDet].[OrgUnit3])12063.5300.686011107316.23818[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [svl05].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl05]))13433Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl05])[svl05].[ValidationPartyID], [svl05].[ValidationListDesc]64650.36756940.00726851270.3748379[svl05].[ValidationPartyID], [svl05].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl04].[ValidationPartyID])=([AssDet].[OrgUnit2]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl04].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OrgUnit2] as [AssDet].[OrgUnit2]))13533Hash MatchRight Outer JoinHASH:([svl04].[ValidationPartyID])=([AssDet].[OrgUnit2]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl04].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OrgUnit2] as [AssDet].[OrgUnit2])12063.5300.68601198715.17733[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[OrgUnit3], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [svl04].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl04]))13635Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl04])[svl04].[ValidationPartyID], [svl04].[ValidationListDesc]64650.36756940.00726851270.3748379[svl04].[ValidationPartyID], [svl04].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl03].[ValidationPartyID])=([AssDet].[OrgUnit1]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl03].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OrgUnit1] as [AssDet].[OrgUnit1]))13735Hash MatchRight Outer JoinHASH:([svl03].[ValidationPartyID])=([AssDet].[OrgUnit1]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl03].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[OrgUnit1] as [AssDet].[OrgUnit1])12063.5300.686011390114.11648[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[OrgUnit2], [AssDet].[OrgUnit3], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [svl03].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl03]))13837Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl03])[svl03].[ValidationPartyID], [svl03].[ValidationListDesc]64650.36756940.00726851270.3748379[svl03].[ValidationPartyID], [svl03].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl02].[ValidationPartyID])=([per].[PostTitleValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl02].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[Person].[PostTitleValPartyID] as [per].[PostTitleValPartyID]))13937Hash MatchRight Outer JoinHASH:([svl02].[ValidationPartyID])=([per].[PostTitleValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl02].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[Person].[PostTitleValPartyID] as [per].[PostTitleValPartyID])12063.5300.68601181513.05563[per].[PersonID], [per].[PartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[OrgUnit1], [AssDet].[OrgUnit2], [AssDet].[OrgUnit3], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [svl02].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl02]))14039Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl02])[svl02].[ValidationPartyID], [svl02].[ValidationListDesc]64650.36756940.00726851270.3748379[svl02].[ValidationPartyID], [svl02].[ValidationListDesc]PLAN_ROWFalse1
|--Hash Match(Right Outer Join, HASH:([svl01].[ValidationPartyID])=([per].[SalutationValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl01].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[Person].[SalutationValPartyID] as [per].[SalutationValPartyID]))14139Hash MatchRight Outer JoinHASH:([svl01].[ValidationPartyID])=([per].[SalutationValPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[sysValidationList].[ValidationPartyID] as [svl01].[ValidationPartyID]=[DEV2-HRMS-036].[dbo].[Person].[SalutationValPartyID] as [per].[SalutationValPartyID])12063.5300.68601172911.99478[per].[PersonID], [per].[PartyID], [per].[PostTitleValPartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[OrgUnit1], [AssDet].[OrgUnit2], [AssDet].[OrgUnit3], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [svl01].[ValidationListDesc], [Expr1053]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl01]))14241Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[sysValidationList].[IX_sysValidationList] AS [svl01])[svl01].[ValidationPartyID], [svl01].[ValidationListDesc]64650.36756940.00726851270.3748379[svl01].[ValidationPartyID], [svl01].[ValidationListDesc]PLAN_ROWFalse1
|--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([***].[AssignmentPartyID])=([AssDet].[AssignmentPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[Assignment].[AssignmentPartyID] as [***].[AssignmentPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[AssignmentPartyID] as [AssDet].[AssignmentPartyID]))14341Merge JoinLeft Outer JoinMANY-TO-MANY MERGE:([***].[AssignmentPartyID])=([AssDet].[AssignmentPartyID]), RESIDUAL:([DEV2-HRMS-036].[dbo].[Assignment].[AssignmentPartyID] as [***].[AssignmentPartyID]=[DEV2-HRMS-036].[dbo].[AssignmentDetails].[AssignmentPartyID] as [AssDet].[AssignmentPartyID])12063.530.068181020.0820043364310.93392[per].[PersonID], [per].[PartyID], [per].[SalutationValPartyID], [per].[PostTitleValPartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[OrgUnit1], [AssDet].[OrgUnit2], [AssDet].[OrgUnit3], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID], [Expr1053]PLAN_ROWFalse1
|--Sort(ORDER BY:([***].[AssignmentPartyID] ASC))14443SortSortORDER BY:([***].[AssignmentPartyID] ASC)120030.011261260.74668193337.890187[per].[PersonID], [per].[PartyID], [per].[SalutationValPartyID], [per].[PostTitleValPartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [***].[AssignmentPartyID], [Expr1053]PLAN_ROWFalse1
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([per].[PartyID]))14544Nested LoopsLeft Outer JoinOUTER REFERENCES:([per].[PartyID])1200300.050172543337.132244[per].[PersonID], [per].[PartyID], [per].[SalutationValPartyID], [per].[PostTitleValPartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [***].[AssignmentPartyID], [Expr1053]PLAN_ROWFalse1
| |--Compute Scalar(DEFINE:([Expr1053]=([DEV2-HRMS-036].[dbo].[Person].[LastName] as [per].[LastName]+', ')+[DEV2-HRMS-036].[dbo].[Person].[FirstName] as [per].[FirstName]))14645Compute ScalarCompute ScalarDEFINE:([Expr1053]=([DEV2-HRMS-036].[dbo].[Person].[LastName] as [per].[LastName]+', ')+[DEV2-HRMS-036].[dbo].[Person].[FirstName] as [per].[FirstName])[Expr1053]=([DEV2-HRMS-036].[dbo].[Person].[LastName] as [per].[LastName]+', ')+[DEV2-HRMS-036].[dbo].[Person].[FirstName] as [per].[FirstName]1200300.00120033170.425093[per].[PersonID], [per].[PartyID], [per].[SalutationValPartyID], [per].[PostTitleValPartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress], [Expr1053]PLAN_ROWFalse1
| | |--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[Person].[PK_Person] AS [per]))14746Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[Person].[PK_Person] AS [per])[per].[PersonID], [per].[PartyID], [per].[SalutationValPartyID], [per].[PostTitleValPartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress]120030.41053240.01336032540.4238927[per].[PersonID], [per].[PartyID], [per].[SalutationValPartyID], [per].[PostTitleValPartyID], [per].[FirstName], [per].[MiddleName], [per].[LastName], [per].[KnownAs], [per].[InternalEmailAddress], [per].[ExternalEmailAddress]PLAN_ROWFalse1
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008]))15145Nested LoopsInner JoinOUTER REFERENCES:([Expr1008])104.18E-06236.656978[***].[AssignmentPartyID]PLAN_ROWFalse12003
| |--Stream Aggregate(DEFINE:([Expr1008]=MAX([DEV2-HRMS-036].[dbo].[Assignment].[StartDate] as [Ass2].[StartDate])))15251Stream AggregateAggregate[Expr1008]=MAX([DEV2-HRMS-036].[dbo].[Assignment].[StartDate] as [Ass2].[StartDate])101.1E-06153.034131[Expr1008]PLAN_ROWFalse12003
| | |--Top(TOP EXPRESSION:((1)))15352TopTopTOP EXPRESSION:((1))101E-07153.020928[Ass2].[StartDate]PLAN_ROWFalse12003
| | |--Clustered Index Seek(OBJECT:([DEV2-HRMS-036].[dbo].[Assignment].[IX_Assignment] AS [Ass2]), SEEK:([Ass2].[PersonPartyID]=[DEV2-HRMS-036].[dbo].[Person].[PartyID] as [per].[PartyID] AND [Ass2].[StartDate] <= CONVERT_IMPLICIT(datetime,CONVERT(varchar(10),getdate(),112),0)) ORDERED BACKWARD)15453Clustered Index SeekClustered Index SeekOBJECT:([DEV2-HRMS-036].[dbo].[Assignment].[IX_Assignment] AS [Ass2]), SEEK:([Ass2].[PersonPartyID]=[DEV2-HRMS-036].[dbo].[Person].[PartyID] as [per].[PartyID] AND [Ass2].[StartDate] <= CONVERT_IMPLICIT(datetime,CONVERT(varchar(10),getdate(),112),0)) ORDERED BACKWARD[Ass2].[StartDate]10.0031250.0001586436153.019728[Ass2].[StartDate]PLAN_ROWFalse12003
| |--Clustered Index Seek(OBJECT:([DEV2-HRMS-036].[dbo].[Assignment].[IX_Assignment] AS [***]), SEEK:([***].[PersonPartyID]=[DEV2-HRMS-036].[dbo].[Person].[PartyID] as [per].[PartyID] AND [***].[StartDate]=[Expr1008]) ORDERED FORWARD)15951Clustered Index SeekClustered Index SeekOBJECT:([DEV2-HRMS-036].[dbo].[Assignment].[IX_Assignment] AS [***]), SEEK:([***].[PersonPartyID]=[DEV2-HRMS-036].[dbo].[Person].[PartyID] as [per].[PartyID] AND [***].[StartDate]=[Expr1008]) ORDERED FORWARD[***].[AssignmentPartyID]10.0031250.0001581233.572674[***].[AssignmentPartyID]PLAN_ROWFalse12003
|--Sort(ORDER BY:([AssDet].[AssignmentPartyID] ASC))16043SortSortORDER BY:([AssDet].[AssignmentPartyID] ASC)180830.011261261.1739293482.893548[AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[AssignmentPartyID], [AssDet].[OrgUnit1], [AssDet].[OrgUnit2], [AssDet].[OrgUnit3], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID]PLAN_ROWFalse1
|--Clustered Index Scan(OBJECT:([DEV2-HRMS-036].[dbo].[AssignmentDetails].[IX_AssignmentDetails] AS [AssDet]))16160Clustered Index ScanClustered Index ScanOBJECT:([DEV2-HRMS-036].[dbo].[AssignmentDetails].[IX_AssignmentDetails] AS [AssDet])[AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[AssignmentPartyID], [AssDet].[OrgUnit1], [AssDet].[OrgUnit2], [AssDet].[OrgUnit3], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID]180831.688310.02004833481.708359[AssDet].[StartDate], [AssDet].[EndDate], [AssDet].[AssignmentPartyID], [AssDet].[OrgUnit1], [AssDet].[OrgUnit2], [AssDet].[OrgUnit3], [AssDet].[OrgUnit4], [AssDet].[CountryValPartyID], [AssDet].[LocationValPartyID], [AssDet].[FunctionValPartyID], [AssDet].[HoursPerWeek], [AssDet].[OvertimeValPartyID], [AssDet].[FTE], [AssDet].[StatusValPartyID], [AssDet].[AssigmentDetailsStatus], [AssDet].[ExpatMarker], [AssDet].[RepatriateDate], [AssDet].[ShiftDetailsValPartyID], [AssDet].[EEO1JobCategoryValPartyID], [AssDet].[PTSFLSAStatusValPartyID], [AssDet].[PTSPayGroupValPartyID], [AssDet].[PTSRateTypeValPartyID], [AssDet].[PTSWorkmansCompValPartyID], [AssDet].[PTSWorkScheduleValPartyID]PLAN_ROWFalse1
January 23, 2009 at 2:02 pm
It generally won't do 20 scans in that case. The optimizer is usually smart enough to figure out that one scan will do it. The only times I've seen it do that many scans, was when the inline query is a multi-table function with input parameters from the outer table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 2:06 pm
Ed (1/23/2009)
So even if 20 coded columns need to access the lookup table to decode to descriptions, a table scan will be performed 20 times?
Not sure what you're asking.
Frankly, that exec plan is unreadable. I'm not even going to try. Since you're on 2005 (I assume so, since this is the 2005 forum), save the exec plan as a .sqlplan file, zip it and attach that zip file to your post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2009 at 2:29 pm
Thanks for your help. I'm using Toad for SQL Server, so hopefully this is the format you're looking for.
Regards,
Ed
January 23, 2009 at 2:38 pm
Ed (1/23/2009)
Thanks for your help. I'm using Toad for SQL Server, so hopefully this is the format you're looking for.
No. I was looking for the exec plan that management studio writes. That can be opened in management studio to see the graphical plan. I'm guessing only Toad can display a graphical version of what it wrote out, and I don't have Toad.
Try running the query with SET STATISTICS XML ON beforehand. It should write out some xml. That's exacly the same format as management studio's showplan. Save that to a file, zip and attach.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2009 at 2:56 pm
When I tried that, it returned a second pane with a header, "Microsoft SQL Server XML Showplan", but there were no other contents. I was able to copy the contents of the execution plan and save them as an .rtf file, both as an MS Graphic plan and as a Tree plan. Are either of these useful?
January 23, 2009 at 4:58 pm
Out of interest, what's the performance like if you run the sql without the joins to your validation list.
ie. exclude the joins past this bit and remove some columns from the select
FROM dbo.Person per
-- Employee Joins
LEFT OUTER JOIN dbo.Assignment *** ON ***.PersonPartyID = per.PartyID
AND ***.StartDate = (select max(startdate) from Assignment Ass2 where per.partyid = Ass2.personpartyid
AND startdate <= CONVERT(varchar(10), GETDATE(), 112))
LEFT OUTER JOIN dbo.AssignmentDetails AssDet ON ***.AssignmentPartyID = AssDet.AssignmentPartyID
January 23, 2009 at 7:12 pm
David's recommendation is pretty much, spot on. Since you're a newbie at this, you may want to visit W3Schools.com and learn how to join tables without creating an alias for every column you want to return in the select list.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2009 at 1:35 am
Ed (1/23/2009)
Are either of these useful?
No. The image doesn't contain all the info, there's a lot that's in tooltips and operator properties. The xml (if that's what you saved to the first file) seems to have lost all of the xml tags.
The reason I'm asking for the xml is not because I want to read XML, it's because I can open that in management studio and see the full graphical plan. Anything that 'formats' the xml to make it human readable means that management studio won't read it again, which defeats the purpose of asking for it.
If you want us to look at the execution plan, take that piece of xml that SQL outputted under the heading "Microsoft SQL Server XML Showplan", save it as a .txt file (plain text, no formatting, not rtf, etc), zip that and attach.
See attached for a screenshot of both what that setting should output and an execution plan that's useful. (I know you're using a different tool, it should be similar)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2009 at 3:43 pm
It only takes about 2-3 seconds for the query, as opposed to about 60 seconds with all the joins to the lookup table.
January 25, 2009 at 3:48 pm
Thanks Gail. I can't seem to figure out how to generate the xml in Toad, so I opened MS SQL Server Management Studio, ran the execution plan there, and saved the results. I didn't see any options for what details go into the file, so hopefully the attached is ok.
January 26, 2009 at 12:43 am
Looks fine. I'll take a detailed look later.
One thing I can tell you straight off, there's no clustered index on the table that you're repeatedly joining to, and no useful nonclustered indexes. 20-odd table scans aren't going to help any query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply