I have a table called People.People which links to a number of other tables. A person may or may not have a row in these tables, depending on the work they do. I have the following query (saved as a view) which returns a series of booleans depending on whether there are rows in these related tables:
SELECT DISTINCT P.PersonID,
CASE WHEN av.VisitorID IS NULL THEN 0 ELSE 1 END AS VisitorInfo,
CASE WHEN amd.AssociateMemberID IS NULL THEN 0 ELSE 1 END AS AssociateMemberInfo,
CASE WHEN phw.PersonWorkloadID IS NULL THEN 0 ELSE 1 END AS WorkloadInfo,
CASE WHEN fa.ApplicationID IS NULL THEN 0 ELSE 1 END AS FundingInfo,
CASE WHEN ph.PostHolderID IS NULL THEN 0 ELSE 1 END AS PostHolderInfo,
CASE WHEN foh.FacultyOfficeHolderID IS NULL THEN 0 ELSE 1 END AS FacultyOfficeHolderInfo,
CASE WHEN ps.PersonSpecialismID IS NULL THEN 0 ELSE 1 END AS PersonSpecialismInfo,
CASE WHEN lb.PersonID IS NULL THEN 0 ELSE 1 END AS PersonLeaveInfo
FROM People.People p
LEFT JOIN Visitors.AcademicVisitors av ON p.PersonID = av.PersonID
LEFT JOIN AssociateMembers.AssociateMembershipDetails amd ON p.PersonID = amd.PersonID
LEFT JOIN Workload.PostHolderWorkload phw ON p.PersonID = phw.PersonID
LEFT JOIN Workload.LeaveBooked lb ON p.PersonID=lb.PersonID
LEFT JOIN Finance.FundingApplications fa ON p.PersonID = fa.ApplicantPersonID
LEFT JOIN HR.PostHolders ph ON p.PersonID = ph.PersonID
LEFT JOIN FacultyOffices.FacultyOfficeHolders foh ON p.PersonID = foh.PersonID
LEFT JOIN People.PersonSpecialisms ps ON p.PersonID = ps.PersonID
I've suddenly started getting performance problems with this query, which has until now performed fine. The tables are small - only around 600 rows in People, which is the largest of them.
The database is a SQL Azure database, and the recommendation from Microsoft (in their Azure portal performance analyser) was that the database is topping out the available DTUs, and that I should increase the capacity - which I've done, at least as a short term measure, and this has helped a bit.
But is there a more efficient way of achieving what I want by re-writing this query?
Here's the design of the tables including indexes:
CREATE TABLE [People].[People](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](100) NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](100) NULL,
[Sex] [varchar](50) NULL,
[DateOfBirth] [datetime] NULL,
[Location] [varchar](100) NULL,
[PersonType] [varchar](100) NULL,
[ShowFundingApplications] [bit] NOT NULL,
[TS] [timestamp] NOT NULL,
[WebFormID] [varchar](100) NULL,
[ShowAssociateMemberInformation] [bit] NOT NULL,
[ShowVisitorInformation] [bit] NOT NULL,
[ShowPostholdingDetails] [bit] NOT NULL,
[ShowWorkloadDetails] [bit] NOT NULL,
[SSO] [varchar](20) NULL,
[Active] [bit] NOT NULL,
[CustomisedShowAssociateInfo] [bit] NOT NULL,
[CustomisedShowVisitorInfo] [bit] NOT NULL,
[CustomisedShowPostholdingInfo] [bit] NOT NULL,
[CustomisedShowFundingInfo] [bit] NOT NULL,
[CustomisedShowWorkloadInfo] [bit] NOT NULL,
[OriginalID] [int] NULL,
[OriginalSource] [varchar](300) NULL,
[LocationDetails] [varchar](100) NULL,
[ShowFacultyOffices] [bit] NOT NULL,
[ShowSpecialisms] [bit] NOT NULL,
[CustomisedShowFacultyOffices] [bit] NOT NULL,
[CustomisedShowSpecialisms] [bit] NOT NULL,
[EmailAddress] [varchar](200) NULL,
[ShowLeaveDetails] [bit] NOT NULL,
[CustomisedShowLeaveDetails] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [ShowFundingApplications]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [ShowAssociateMemberInformation]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [ShowVisitorInformation]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [ShowPostholdingDetails]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [ShowWorkloadDetails]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [CustomisedShowAssociateInfo]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [CustomisedShowVisitorInfo]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [CustomisedShowPostholdingInfo]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [CustomisedShowFundingInfo]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [CustomisedShowWorkloadInfo]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [ShowFacultyOffices]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [ShowSpecialisms]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [CustomisedShowFacultyOffices]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [CustomisedShowSpecialisms]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [ShowLeaveDetails]
GO
ALTER TABLE [People].[People] ADD DEFAULT ((0)) FOR [CustomisedShowLeaveDetails]
GO
ALTER TABLE [People].[People] WITH NOCHECK ADD FOREIGN KEY([Location])
REFERENCES [Lookups].[Locations] ([LocationName])
ON UPDATE CASCADE
GO
ALTER TABLE [People].[People] WITH NOCHECK ADD FOREIGN KEY([PersonType])
REFERENCES [Lookups].[PersonTypes] ([PersonType])
ON UPDATE CASCADE
GO
CREATE TABLE [Visitors].[AcademicVisitors](
[VisitorID] [int] IDENTITY(1,1) NOT NULL,
[PersonID] [int] NULL,
[DateOfApplication] [datetime] NULL,
[HomeInstitution] [varchar](200) NULL,
[Position] [varchar](100) NULL,
[Address1] [varchar](100) NULL,
[Address2] [varchar](100) NULL,
[City] [varchar](100) NULL,
[PostCode] [varchar](20) NULL,
[Country] [varchar](100) NULL,
[EmailAddress] [varchar](150) NULL,
[SponsorName] [varchar](150) NULL,
[SponsorDocumentPath] [varchar](255) NULL,
[VisitStart] [datetime] NULL,
[VisitEnd] [datetime] NULL,
[AssociatedCollege] [varchar](200) NULL,
[IntendToCollaborateOnResearchProject] [bit] NULL,
[IntendToOrganiseAnEvent] [bit] NULL,
[IntendToWriteArticle] [bit] NULL,
[IntendToDevelopResearchProposal] [bit] NULL,
[IntendToProvideSpecialSeminar] [bit] NULL,
[PlannedActivitiesFurtherDetails] [varchar](max) NULL,
[RequireWorkstation] [bit] NULL,
[RequireUniversityCard] [bit] NULL,
[FundingDetails] [varchar](max) NULL,
[VisitorsFeeAmount] [decimal](9, 2) NULL,
[EquipmentFees] [decimal](9, 2) NULL,
[DatePaid] [datetime] NULL,
[PaymentReference] [varchar](100) NULL,
[OriginatingResearchCentre] [int] NULL,
[SignatureReceived] [bit] NULL,
[ApplicationStatus] [varchar](20) NULL,
[FundingDocumentPath] [varchar](255) NULL,
[ApplicationDecisionDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[VisitorID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT (getdate()) FOR [DateOfApplication]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT ((0)) FOR [IntendToCollaborateOnResearchProject]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT ((0)) FOR [IntendToOrganiseAnEvent]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT ((0)) FOR [IntendToWriteArticle]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT ((0)) FOR [IntendToDevelopResearchProposal]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT ((0)) FOR [IntendToProvideSpecialSeminar]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT ((0)) FOR [RequireWorkstation]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT ((0)) FOR [RequireUniversityCard]
GO
ALTER TABLE [Visitors].[AcademicVisitors] ADD DEFAULT ((0)) FOR [SignatureReceived]
GO
ALTER TABLE [Visitors].[AcademicVisitors] WITH CHECK ADD FOREIGN KEY([OriginatingResearchCentre])
REFERENCES [Lookups].[VisitorsResearchCentres] ([CentreID])
GO
ALTER TABLE [Visitors].[AcademicVisitors] WITH NOCHECK ADD CONSTRAINT [FK__AcademicV__Perso__1DB06A4F] FOREIGN KEY([PersonID])
REFERENCES [People].[People] ([PersonID])
ON DELETE CASCADE
GO
ALTER TABLE [Visitors].[AcademicVisitors] CHECK CONSTRAINT [FK__AcademicV__Perso__1DB06A4F]
GO
ALTER TABLE [Visitors].[AcademicVisitors] WITH CHECK ADD CONSTRAINT [fkVisitors_VisitorStatus] FOREIGN KEY([ApplicationStatus])
REFERENCES [Lookups].[VisitorApplicationStatuses] ([ApplicationStatus])
GO
ALTER TABLE [Visitors].[AcademicVisitors] CHECK CONSTRAINT [fkVisitors_VisitorStatus]
GO
/****** Object: Index [ix_AR_PersonVisitors] Script Date: 25/07/2023 13:01:51 ******/
CREATE NONCLUSTERED INDEX [ix_AR_PersonVisitors] ON [Visitors].[AcademicVisitors]
(
[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE TABLE [AssociateMembers].[AssociateMembershipDetails](
[AssociateMemberID] [int] IDENTITY(1,1) NOT NULL,
[PersonID] [int] NULL,
[HasWebProfile] [bit] NOT NULL,
[Published] [bit] NOT NULL,
[WebCategory] [varchar](50) NULL,
[Email] [varchar](255) NULL,
[FacultyEmployee] [bit] NOT NULL,
[Category] [varchar](50) NULL,
[OtherAffiliation] [varchar](100) NULL,
[YearOfDoctorate] [int] NULL,
[RefereeeName] [varchar](100) NULL,
[PathToCV] [varchar](255) NULL,
[PathToSupportingReference] [varchar](255) NULL,
[RationaleForFacultyAssociation] [varchar](500) NULL,
[RenewalApplication] [bit] NOT NULL,
[ExistingUniversityCardNumber] [varchar](30) NULL,
[BodCardEndDate] [datetime] NULL,
[FacultyMembership] [varchar](50) NULL,
[Congregation] [bit] NOT NULL,
[SubCategory] [varchar](100) NULL,
[MembershipEndDate] [datetime] NULL,
[HasLeft] [bit] NOT NULL,
[Notes] [varchar](500) NULL,
[TS] [timestamp] NOT NULL,
[MembershipDuration] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[AssociateMemberID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] ADD DEFAULT ((0)) FOR [HasWebProfile]
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] ADD DEFAULT ((0)) FOR [Published]
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] ADD DEFAULT ((0)) FOR [FacultyEmployee]
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] ADD DEFAULT ((0)) FOR [RenewalApplication]
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] ADD DEFAULT ((0)) FOR [Congregation]
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] ADD DEFAULT ((0)) FOR [HasLeft]
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] WITH CHECK ADD FOREIGN KEY([Category])
REFERENCES [Lookups].[AssociateMemberCategories] ([Category])
ON UPDATE CASCADE
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] WITH NOCHECK ADD CONSTRAINT [FK__Associate__Perso__7EF6D905] FOREIGN KEY([PersonID])
REFERENCES [People].[People] ([PersonID])
ON DELETE CASCADE
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] CHECK CONSTRAINT [FK__Associate__Perso__7EF6D905]
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] WITH CHECK ADD FOREIGN KEY([WebCategory])
REFERENCES [Lookups].[WebCategories] ([WebCategory])
ON UPDATE CASCADE
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] WITH CHECK ADD CONSTRAINT [fk_AssociateMembershipDetails_SubCategories] FOREIGN KEY([SubCategory])
REFERENCES [Lookups].[AssociateMemberSubCategories] ([SubCategory])
GO
ALTER TABLE [AssociateMembers].[AssociateMembershipDetails] CHECK CONSTRAINT [fk_AssociateMembershipDetails_SubCategories]
GO
/****** Object: Index [ix_AR_PersonAssociateMembers] Script Date: 25/07/2023 13:04:55 ******/
CREATE NONCLUSTERED INDEX [ix_AR_PersonAssociateMembers] ON [AssociateMembers].[AssociateMembershipDetails]
(
[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE TABLE [Workload].[PostHolderWorkload](
[PersonWorkloadID] [int] IDENTITY(1,1) NOT NULL,
[PersonID] [int] NULL,
[TermID] [int] NULL,
[WorkloadType] [int] NULL,
[Quantity] [decimal](5, 2) NULL,
[TotalWorkload] [decimal](5, 2) NULL,
[WorkloadDetails] [varchar](500) NULL,
[TS] [timestamp] NOT NULL,
PRIMARY KEY CLUSTERED
(
[PersonWorkloadID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Workload].[PostHolderWorkload] WITH NOCHECK ADD FOREIGN KEY([PersonID])
REFERENCES [People].[People] ([PersonID])
GO
ALTER TABLE [Workload].[PostHolderWorkload] WITH CHECK ADD FOREIGN KEY([TermID])
REFERENCES [Lookups].[Terms] ([TermID])
GO
ALTER TABLE [Workload].[PostHolderWorkload] WITH NOCHECK ADD FOREIGN KEY([WorkloadType])
REFERENCES [Workload].[WorkloadTypes] ([WorkloadTypeID])
GO
/****** Object: Index [ix_AR_PersonWorkload] Script Date: 25/07/2023 13:05:50 ******/
CREATE NONCLUSTERED INDEX [ix_AR_PersonWorkload] ON [Workload].[PostHolderWorkload]
(
[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE TABLE [Workload].[LeaveBooked](
[LeaveBookedID] [int] IDENTITY(1,1) NOT NULL,
[PersonID] [int] NULL,
[TypeOfLeave] [varchar](50) NULL,
[StartTerm] [int] NULL,
[EndTerm] [int] NULL,
[LeaveStatus] [varchar](50) NULL,
[OriginalWebID] [varchar](50) NULL,
[ApplicationNotes] [varchar](3000) NULL,
PRIMARY KEY CLUSTERED
(
[LeaveBookedID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Workload].[LeaveBooked] WITH CHECK ADD FOREIGN KEY([EndTerm])
REFERENCES [Lookups].[Terms] ([TermID])
GO
ALTER TABLE [Workload].[LeaveBooked] WITH CHECK ADD FOREIGN KEY([LeaveStatus])
REFERENCES [Lookups].[LeaveStatuses] ([LeaveStatus])
ON UPDATE CASCADE
GO
ALTER TABLE [Workload].[LeaveBooked] WITH NOCHECK ADD FOREIGN KEY([PersonID])
REFERENCES [People].[People] ([PersonID])
ON DELETE CASCADE
GO
ALTER TABLE [Workload].[LeaveBooked] WITH CHECK ADD FOREIGN KEY([StartTerm])
REFERENCES [Lookups].[Terms] ([TermID])
GO
ALTER TABLE [Workload].[LeaveBooked] WITH CHECK ADD FOREIGN KEY([TypeOfLeave])
REFERENCES [Lookups].[LeaveTypes] ([LeaveType])
ON UPDATE CASCADE
GO
/****** Object: Index [ix_AR_PersonLeaveBooked] Script Date: 25/07/2023 13:06:44 ******/
CREATE NONCLUSTERED INDEX [ix_AR_PersonLeaveBooked] ON [Workload].[LeaveBooked]
(
[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE TABLE [Finance].[FundingApplications](
[ApplicationID] [int] IDENTITY(1,1) NOT NULL,
[FacultyDeadline] [datetime] NULL,
[RSDeadline] [datetime] NULL,
[FunderDeadline] [datetime] NULL,
[ApplicantPersonID] [int] NULL,
[ProjectShortTitle] [varchar](255) NULL,
[ProjectFullTitle] [varchar](3000) NULL,
[FunderReference] [varchar](255) NULL,
[FundingBodyID] [int] NULL,
[SchemeOrAwardType] [int] NULL,
[InternalReferenceNum] [varchar](20) NULL,
[X5Reference] [varchar](30) NULL,
[AmountRequested] [decimal](12, 2) NULL,
[AmountForOverheads] [decimal](12, 2) NULL,
[AmountAwarded] [decimal](12, 2) NULL,
[LengthOfGrant] [varchar](20) NULL,
[StartDate] [datetime] NULL,
[RSInformed] [bit] NOT NULL,
[ChancesOfSuccess] [decimal](4, 3) NULL,
[ApplicationStatus] [varchar](30) NULL,
[Notes] [varchar](500) NULL,
[OracleProjectNumber] [varchar](50) NULL,
[DateOutcomeExpected] [datetime] NULL,
[SchemeClosingDate] [datetime] NULL,
[CollaboratorsExist] [bit] NOT NULL,
[LeadCollaborator] [bit] NOT NULL,
[DocumentFolder] [varchar](255) NULL,
[Withdrawn] [bit] NULL,
[ApplicationUnsuccessful] [bit] NULL,
[TS] [timestamp] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ApplicationID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Finance].[FundingApplications] ADD DEFAULT ((0)) FOR [RSInformed]
GO
ALTER TABLE [Finance].[FundingApplications] ADD DEFAULT ((0)) FOR [CollaboratorsExist]
GO
ALTER TABLE [Finance].[FundingApplications] ADD DEFAULT ((0)) FOR [LeadCollaborator]
GO
ALTER TABLE [Finance].[FundingApplications] ADD DEFAULT ((0)) FOR [Withdrawn]
GO
ALTER TABLE [Finance].[FundingApplications] ADD DEFAULT ((0)) FOR [ApplicationUnsuccessful]
GO
ALTER TABLE [Finance].[FundingApplications] WITH NOCHECK ADD FOREIGN KEY([ApplicantPersonID])
REFERENCES [People].[People] ([PersonID])
GO
ALTER TABLE [Finance].[FundingApplications] WITH CHECK ADD FOREIGN KEY([ApplicationStatus])
REFERENCES [Lookups].[FundingApplicationStatuses] ([ApplicationStatus])
ON UPDATE CASCADE
GO
ALTER TABLE [Finance].[FundingApplications] WITH CHECK ADD FOREIGN KEY([FundingBodyID])
REFERENCES [Lookups].[FundingBodies] ([FundingBodyID])
GO
ALTER TABLE [Finance].[FundingApplications] WITH CHECK ADD FOREIGN KEY([SchemeOrAwardType])
REFERENCES [Lookups].[FundingAwardTypes] ([AwardTypeID])
GO
/****** Object: Index [ix_AR_PersonReasearchFundingApplications] Script Date: 25/07/2023 13:07:32 ******/
CREATE NONCLUSTERED INDEX [ix_AR_PersonReasearchFundingApplications] ON [Finance].[FundingApplications]
(
[ApplicantPersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE TABLE [HR].[PostHolders](
[PostHolderID] [int] IDENTITY(1,1) NOT NULL,
[PersonID] [int] NULL,
[AppointmentID] [varchar](20) NULL,
[EmployeeStatus] [varchar](100) NULL,
[EmployeeSubstatus] [varchar](100) NULL,
[TargetEndDate] [datetime] NULL,
[ActualEndDate] [datetime] NULL,
[WorkgroupID] [int] NULL,
[College] [int] NULL,
[PostTitle] [varchar](100) NULL,
[PostText] [varchar](100) NULL,
[PostCategory] [int] NULL,
[HoursWorked] [decimal](4, 2) NULL,
[Grade] [varchar](10) NULL,
[Point] [varchar](3) NULL,
[TS] [timestamp] NOT NULL,
[ContractStartDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[PostHolderID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [HR].[PostHolders] WITH NOCHECK ADD FOREIGN KEY([College])
REFERENCES [Lookups].[Colleges] ([CollegeID])
GO
ALTER TABLE [HR].[PostHolders] WITH NOCHECK ADD FOREIGN KEY([EmployeeStatus])
REFERENCES [Lookups].[EmployeeStatuses] ([EmployeeStatusOption])
GO
ALTER TABLE [HR].[PostHolders] WITH NOCHECK ADD FOREIGN KEY([EmployeeSubstatus])
REFERENCES [Lookups].[EmployeeSubstatuses] ([SubstatusOption])
ON UPDATE CASCADE
GO
ALTER TABLE [HR].[PostHolders] WITH NOCHECK ADD FOREIGN KEY([PersonID])
REFERENCES [People].[People] ([PersonID])
GO
ALTER TABLE [HR].[PostHolders] WITH NOCHECK ADD FOREIGN KEY([PostCategory])
REFERENCES [Lookups].[PostCategories] ([PostCategoryID])
GO
ALTER TABLE [HR].[PostHolders] WITH NOCHECK ADD FOREIGN KEY([WorkgroupID])
REFERENCES [Lookups].[Workgroups] ([WorkgroupID])
GO
ALTER TABLE [HR].[PostHolders] WITH NOCHECK ADD CONSTRAINT [fk_PostHolders_JobTitles] FOREIGN KEY([PostTitle])
REFERENCES [Lookups].[PostTitles] ([PostTitle])
ON UPDATE CASCADE
GO
ALTER TABLE [HR].[PostHolders] CHECK CONSTRAINT [fk_PostHolders_JobTitles]
GO
/****** Object: Index [ix_AR_PersonReasearchFundingApplications] Script Date: 25/07/2023 13:07:32 ******/
CREATE NONCLUSTERED INDEX [ix_AR_PersonPostholders] ON [HR].[PostHolders]
(
[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE TABLE [FacultyOffices].[FacultyOfficeHolders](
[FacultyOfficeHolderID] [int] IDENTITY(1,1) NOT NULL,
[FacultyOfficeID] [int] NULL,
[PersonID] [int] NULL,
[TermID] [int] NULL,
[Status] [varchar](20) NULL,
[BuyoutTaken] [varchar](100) NULL,
[Notes] [varchar](500) NULL,
[TS] [timestamp] NOT NULL,
[GroupingID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[FacultyOfficeHolderID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [FacultyOffices].[FacultyOfficeHolders] WITH NOCHECK ADD FOREIGN KEY([FacultyOfficeID])
REFERENCES [Lookups].[FacultyOffices] ([FacultyOfficeID])
GO
ALTER TABLE [FacultyOffices].[FacultyOfficeHolders] WITH NOCHECK ADD FOREIGN KEY([PersonID])
REFERENCES [People].[People] ([PersonID])
GO
ALTER TABLE [FacultyOffices].[FacultyOfficeHolders] WITH NOCHECK ADD FOREIGN KEY([Status])
REFERENCES [Lookups].[FacultyOfficeHolderStatuses] ([Status])
ON UPDATE CASCADE
GO
ALTER TABLE [FacultyOffices].[FacultyOfficeHolders] WITH NOCHECK ADD FOREIGN KEY([TermID])
REFERENCES [Lookups].[Terms] ([TermID])
GO
/****** Object: Index [ix_AR_PersonFacultyOffices] Script Date: 25/07/2023 13:10:21 ******/
CREATE NONCLUSTERED INDEX [ix_AR_PersonFacultyOffices] ON [FacultyOffices].[FacultyOfficeHolders]
(
[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE TABLE [People].[PersonSpecialisms](
[PersonSpecialismID] [int] IDENTITY(1,1) NOT NULL,
[PersonID] [int] NULL,
[SpecialismID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[PersonSpecialismID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [People].[PersonSpecialisms] WITH NOCHECK ADD FOREIGN KEY([PersonID])
REFERENCES [People].[People] ([PersonID])
GO
ALTER TABLE [People].[PersonSpecialisms] WITH CHECK ADD FOREIGN KEY([SpecialismID])
REFERENCES [Lookups].[Specialisms] ([SpecialismID])
GO
/****** Object: Index [ix_AR_PersonSpecialisms] Script Date: 25/07/2023 13:11:04 ******/
CREATE NONCLUSTERED INDEX [ix_AR_PersonSpecialisms] ON [People].[PersonSpecialisms]
(
[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
And here's the query plan:
You might find it more efficient if you remove the DISTINCT and write the joins as CASE WHEN EXISTS or IIF(EXISTS, because the server can transform those into semi-joins with a pass-through existence column.
Every one of these tables must have an index on their PersonID join column for this to perform well.
SELECT
P.PersonID,
IIF(EXISTS (SELECT 1
FROM Visitors.AcademicVisitors av
WHERE p.PersonID = av.PersonID
), 1, 0) AS VisitorInfo,
IIF(EXISTS (SELECT 1
FROM AssociateMembers.AssociateMembershipDetails amd
WHERE p.PersonID = amd.PersonID
), 1, 0) AS AssociateMemberInfo,
IIF(EXISTS (SELECT 1
FROM Workload.PostHolderWorkload phw
WHERE p.PersonID = phw.PersonID
), 1, 0) AS WorkloadInfo,
IIF(EXISTS (SELECT 1
FROM Finance.FundingApplications fa
WHERE p.PersonID = fa.ApplicantPersonID
), 1, 0) AS FundingInfo,
IIF(EXISTS (SELECT 1
FROM HR.PostHolders ph
WHERE p.PersonID = ph.PersonID
), 1, 0) AS PostHolderInfo,
IIF(EXISTS (SELECT 1
FROM FacultyOffices.FacultyOfficeHolders foh
WHERE p.PersonID = foh.PersonID
), 1, 0) AS FacultyOfficeHolderInfo,
IIF(EXISTS (SELECT 1
FROM People.PersonSpecialisms ps
WHERE p.PersonID = ps.PersonID
), 1, 0) AS PersonSpecialismInfo,
IIF(EXISTS (SELECT 1
FROM Workload.LeaveBooked lb
WHERE p.PersonID = lb.PersonID
), 1, 0) AS PersonLeaveInfo
FROM People.People p;