Hi,

I have the following SQL, which  the plan states is unoptimized. I can't
figure out how to optimize this, and was hoping someone could help please.

The SQL:
~~~~~~~~~~~~~~~~~~~~~~~~~
select ac.item, ac.TYPE, ac.ID as AppCourseID, ac.AppID, c.Claim, C.ID as
ClaimID, C.Claim_Description, AC.CourseID as
CourseID,c.Amount,AC.CourseStartDate, AC.EndDate, AC.CourseItem
Into Memory\TmpClaimALL
from apprenticecourse ac, claims c
where
(c.auto = true)
and ((c.Min_Start is null) or (ac.coursestartdate >= c.Min_start))
and ((c.Max_Start is null) or (ac.coursestartdate <= c.Max_start))
and ((c.Min_end is null) or (ac.enddate >= c.Min_end))
and ((c.Max_end is null) or (ac.enddate <= c.Max_end))
and ((c.state is null) or (ac.CourseState = c.state))
and ((c.EmployerID is null) or (ac.EmployerID = c.EmployerID))
and ((c."Type" is null) or (ac."Type" = c."Type"))
and ((c."RTOID" is null) or (ac."RTOID" = c."RTOID"))
and ((c.Req_Cancellation = FALSE) or (ac.ActiveState = 'CANCELLED'))
and ((c.Req_Completion = FALSE) or (ac.ActiveState = 'COMPLETED'))
and ((c.Req_Progression = FALSE) or (ac.Progression = TRUE))
and ((c.Req_Training_End_Date = FALSE) or (ac.TrainingEndDate is not null))
JOINOPTIMIZECOSTS
~~~~~~~~~~~~~~~~~~~~~~~~~



The expression:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
c.Min_Start is null  or ac.coursestartdate >= c.Min_start AND c.Max_Start is
null  or ac.coursestartdate <= c.Max_start AND c.Min_end is null  or
ac.enddate
>= c.Min_end AND c.Max_end is null  or ac.enddate <= c.Max_end AND c.state
>is
null  or ac.CourseState = c.state AND c.EmployerID is null  or ac.EmployerID
=
c.EmployerID AND c."Type" is null  or ac."Type" = c."Type" AND c."RTOID" is
null  or ac."RTOID" = c."RTOID" AND c.Req_Cancellation = FALSE or
ac.ActiveState = 'CANCELLED' AND c.Req_Completion = FALSE or ac.ActiveState
=
'COMPLETED' AND c.Req_Progression = FALSE or ac.Progression = TRUE AND
c.Req_Training_End_Date = FALSE or ac.TrainingEndDate is not null

is UN-OPTIMIZED and will be applied to each candidate row in the result set
as
the result set is generated
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Can someone please tell me why it won't treat each line (such as "and
((c.Min_end is null) or (ac.enddate >= c.Min_end))") as a seperate
expression, and/or how to optimize this sql?

Thanks & Regards

Adam.