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.

Adam


The simple rule for optimisation is make sure you have an index for every field in a WHERE, ORDER BY or JOIN clause.

It gets a bit more complex because ORDER BY indices can be compound and all fields will be used (if in the right order etc) but for the WHERE clause only the first field in an index is used.

If you want case insensitive make sure you have UPPER on both the field and test.

Even when you think you've got everything right DBISAM can still decide to rearrange things (as I found out) to make the query faster but reports only partially optimised.

As to why it doesn't report on each element - I'm sure Tim will tell you

Roy Lambert

Hi Roy,

> The simple rule for optimisation is make sure you have an index for every
> field in a WHERE, ORDER BY or JOIN clause.
>
> It gets a bit more complex because ORDER BY indices can be compound and
> all fields will be used (if in the right order etc) but for the WHERE
> clause only the first field in an index is used.

Thanks for your tips. In this scenario, I'm of the impression that I do have
an index for each field. (Although each field just has a single index, ie -
no index for multiple fields). Is there any benefit by having more than one
field in an index?

> If you want case insensitive make sure you have UPPER on both the field
> and test.

In this case, their should not be any reason for case insencitivities, but
thanks for the tip.

> Even when you think you've got everything right DBISAM can still decide to
> rearrange things (as I found out) to make the query faster but reports
> only partially optimised.
>
> As to why it doesn't report on each element - I'm sure Tim will tell you
>

Here's hoping, anyway.

Cheers

Adam.

Adam


Another thought - take each element in the sql in turn, comment out the others and see what happens. If they all come out as optimised then DBISAM is making a decision based on its logic as to which will provide the fastest result.

It might also be worth coding with a JOIN clause - I'm not sure if the two syntaxes are equivalent or what differences there might be

Roy Lambert

Adam,

<< 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? >>

You're ORing together predicates against two different tables, hence there's
no way for DBISAM to optimize that and it has to punt.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> << 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? >>
>
> You're ORing together predicates against two different tables, hence
> there's no way for DBISAM to optimize that and it has to punt.

Thanks - that makes sence.

I may have to look at another approach to try and speed things up.

Cheers

Adam.