JOIN Limit

D

Dave Cousineau

im trying to do a really complex join (the most complex ive ever done) and
ive started having some problems

i have 4 INNER JOINs followed by 18 LEFT JOINs

the whole SQL block works as long as im only grabbing < ~2000 records but if
i try to grab around 2500+ the program hangs.

is there a limit to how much i can join?
if i drop a couple joins it works again but goes very slow. if i drop the 6
joins i just added, i can grab any number of records and it loads in less
than a second.

heres my humongous SQL statement:

Code: 
SELECT *
FROM ((((((((((((((((((((( Employee

INNER JOIN EmployeeStatus
ON Employee.EmployeeStatusID = EmployeeStatus.ID)

INNER JOIN Source
ON Employee.SourceID = Source.ID)

INNER JOIN Application
ON Employee.ID = Application.EmployeeID)

INNER JOIN ApplicationStatus
ON Application.ApplicationStatusID = ApplicationStatus.ID)

LEFT JOIN Quadrant
ON Employee.QuadrantID = Quadrant.ID)

LEFT JOIN City
ON Employee.CityID = City.ID)

LEFT JOIN Province
ON Employee.ProvinceID = Province.ID)

LEFT JOIN Position
ON Employee.PositionID = Position.ID)

LEFT JOIN School
ON Employee.SchoolID = School.ID)

LEFT JOIN ConfirmationLetterReason
ON Employee.ConfirmationLetterReasonID = ConfirmationLetterReason.ID)

LEFT JOIN Interviewer
ON Employee.HiredBy = Interviewer.ID)

LEFT JOIN TerminationReason
ON Employee.TerminationReasonID = TerminationReason.ID)

LEFT JOIN LetterToParent
ON Employee.LetterToParentID = LetterToParent.ID)

LEFT JOIN Payroll
ON Employee.PayrollID = Payroll.ID)

LEFT JOIN TerminationType
ON Employee.TerminationTypeID = TerminationType.ID)

LEFT JOIN PositionAppliedFor
ON Application.PositionAppliedForID = PositionAppliedFor.ID)

LEFT JOIN Department
ON Employee.DepartmentID = Department.ID)

LEFT JOIN ApplicationType
ON Application.ApplicationTypeID = ApplicationType.ID)

LEFT JOIN Community
ON Employee.CommunityID = Community.ID)

LEFT JOIN ApplicationFormat
ON Application.ApplicationFormatID = ApplicationFormat.ID)

LEFT JOIN HowHeard
ON Application.HowHeardID = HowHeard.ID)

LEFT JOIN RejectedReason
ON Application.RejectedReasonID = RejectedReason.ID

WHERE SourceID = 1
ORDER BY Employee.FirstName, Employee.LastName;
 
S

Squirrel

Hi Dave,

Suggest using some intermediate level queries instead of doing all the joins
in one query.
e.g. one query with the four inner joins, call it Query1. Then use Query1
as the source for
the next level, perhaps add in 4 left joins, call it Query2. Then Query2 is
the source for the next level, etc.etc.

HTH -Linda
 
Top