PDA

View Full Version : Optimising Constraints for DD and SL use



Michael Fenton
21-Feb-2005, 12:08 PM
What are the exact processes to be followed for optimising the
performance of an on_constrain procedure in a DbView DDO when that DDO
is serving a selection list ?

Does one have to send establish_find_direction within the on_constrain
procedure or outside of it ?

Does setting suggested_ordering within the DDO give one a definate
index for optimisation jump in/out ? Does one put it in or ourside of
the procedure ?

Does the order of constrain file.file eq xxxx statements control the
process sequence, or are they taken in some internal order, eg field
number within filenumber.

Should all constrain AS statements be put at the end of the procedure to
(hopefully) only perform a read-em-all on the set of records reduced by
higher-up filters ?

Should one treat modes of NE and CONTAINS as if they are AS statements ?

Are BETWEEN statements handled as jump in/out processes ?

If one uses a specific index for DDO constraining in a view, and has an
SL with auto_server_state true, does letting the auto_index_state remain
true mean that the DDO extraction/filter process will be repeated on an
inappropriate index, or can the SL sort be confined to sorting what the
invoking DDO passed to it ?

Constraints in DDos and SLs have been one of the most common newsgroup
topics over the years, can DAW do a comprehensive white paper to tell us
how to get the very best results from what are really very usefull but
headstrong servants !!

Michael Fenton

Vincent Oorsprong
22-Feb-2005, 02:49 AM
Michael,

Some answers;

1. The order in the constrain statements makes no difference
2. As soon as there is an AS constrain a jump into and jump out of index is
not possible anymore
3. Ordering property in the DDO can be used to force the use of an index for
finding
4. Between is just a helper, it codes in a GE and LE command pair

Suggestion: Read about the Constrain command in the help.It has some nice
explanations.

--
Kind Regards,
Vincent Oorsprong
Data Access Europe BV
Lansinkesweg 4
7553 AE Hengelo
The Netherlands
Telephone: +31 (0)74 - 255 56 09
Fax: +31 (0)74 - 250 34 66
http://www.dataaccess.nl

David Martinko
22-Feb-2005, 08:49 AM
I created my own subclass of the DBList for my selectionlists because too
often the selectionlists chose the wrong index (resulting is slow displaying
of records).

I don't know _exactly_ what your needs are, so read on if you like. If the
solution below suits your needs, I'll send you my subclass so you can try
it.

What I did was to override the index selection for each field.

If there are no constraints...
For each field that appears on the selectionlist...
Check that field's primary index to see if it is listed as the first
segment of that index
If not, try and find an index that this field is the first
segment of.
If there are constraints...
A list is made of each field used in the constraint
All indexes are checked and ALL constrained fields MUST be at the
beginning of that index.
(If there are 5 constraints, then the first 5 segments MUST be
those constrained fields...)
From that list of "valid" indexes...
For each field that appears on the selectionlist...
If it is a constrained field, it must be the first
segment of the index, otherwise it must be the first segment AFTER the
constraints (6th segment from the example)

So each field's index is saved into an array so only the array is referenced
to get the index when resorting a selectionlist. Columns with no index will
be disabled and thus, not searchable.

Unfortunately, (and at the same time fortunately) DD constraints cannot be
read at runtime. This means you are forced to tell the code what the current
contraints are. The reason this is good, is it saves you from having to make
indexes all the time. Sometimes a file may be so small that it is not needed
to index a constraint (maybe 100 records or so). Another reason is that your
first constraints may have reduced the search time so _now_ only 100 records
are found.

Exmaple:
Procedure OnConstrain
Constrain Employee.State eq "OH"
End_Procedure
// This may be fast enough as long as you don't have thousands of employees.

Procedure OnConstrain
Constrain Employee.State eq "OH"
Send Constrain_Field "State" of oSelList
End_Procedure
// Now it will only use indexes starting with the Employee.State... not a
good idea because State probably isn't indexed to start with and if it is,
it'll probably be only one index. Which means that the user can't sort the
selectionlist any other way...

Let's look at one more example:
Procedure OnConstrain
Constrain Job.Status eq "A"
Constrain Job.Type eq "PE"
Constrain Job.Qty_Remain GT 0
Send Constrain_Field "Status" of oSelList
Send Constrain_Field "Type" of oSelList
//Send Constrain_Field "Qty_Remain" of oSelList <<< NO >>>
End_Procedure
// Indexes must have Status and Type (in any order) as the first 2 fields of
the index. This is a good thing. I bet those fields are indexed. Next is the
Qty_Remain. Why would someone index that field? Well, someone might but I
have better needs for my limited 15 indexes <g>. I don't have many
Jobs.Status="A", so that significantly cuts the number of records found. If
that isn't enough, we also want Job.Type="PE". I imagine we'll only have a
hand full of records (if any) so we won't need to force an index with the
Qty_Remain. Depending on how many records we are dealing with, and how my
indexes are setup, I probably wouldn't even Constrain_Field "Type" either
because there won't be more than 50 open jobs at any one time.

Procedure OnConstrain
Constrain Job.Status eq "A"
Constrain Job.Type eq "PE"
Constrain Job.Qty_Remain GT 0
Send Constrain_Field "Status" of oSelList
End_Procedure
// The result is this. My code is not controlling the constraints, it's only
controlling the indexes used... and in a smarter way that they are normally
handled.

--
David Martinko
Redeemed Software
248-535-7495
RedeemedSoftware(SHIFT+2)Hotmail(PERIOD)com


"Michael Fenton" <mrmjf@tiscali.co.uk> wrote in message
news:oyPReeDGFHA.1248@dacmail.dataaccess.com...
>
> What are the exact processes to be followed for optimising the performance
> of an on_constrain procedure in a DbView DDO when that DDO is serving a
> selection list ?
>
> Does one have to send establish_find_direction within the on_constrain
> procedure or outside of it ?
>
> Does setting suggested_ordering within the DDO give one a definate index
> for optimisation jump in/out ? Does one put it in or ourside of the
> procedure ?
>
> Does the order of constrain file.file eq xxxx statements control the
> process sequence, or are they taken in some internal order, eg field
> number within filenumber.
>
> Should all constrain AS statements be put at the end of the procedure to
> (hopefully) only perform a read-em-all on the set of records reduced by
> higher-up filters ?
>
> Should one treat modes of NE and CONTAINS as if they are AS statements ?
>
> Are BETWEEN statements handled as jump in/out processes ?
>
> If one uses a specific index for DDO constraining in a view, and has an SL
> with auto_server_state true, does letting the auto_index_state remain true
> mean that the DDO extraction/filter process will be repeated on an
> inappropriate index, or can the SL sort be confined to sorting what the
> invoking DDO passed to it ?
>
> Constraints in DDos and SLs have been one of the most common newsgroup
> topics over the years, can DAW do a comprehensive white paper to tell us
> how to get the very best results from what are really very usefull but
> headstrong servants !!
>
> Michael Fenton
>
>

Michael Fenton
22-Feb-2005, 11:42 AM
Vincent,

Thanks for your reply.

Am I right in concluding that as the order of the statements is
irrelevant, and as soon as one uses an AS, jump in jump out is not
possible, then any AS kills ALL jump in jump out possibliities ?

Also does NE kill jump in jump out or is it a helper that makes a LT or
GT one a single record.

Michael Fenton

Ian Telfer
22-Feb-2005, 06:17 PM
Vincent,

I have optimised finding code that uses a find as function, and inserted
that after constraints that are indexed based and the performance
increased dramatically.

It certainly appeared that the Constrain As was being executed on the
lesser quantity of records to me, this is a file with 180,000 records so
the difference was huge..

Ian

Vincent Oorsprong wrote:
> Michael,
>
> Some answers;
>
> 1. The order in the constrain statements makes no difference
> 2. As soon as there is an AS constrain a jump into and jump out of index is
> not possible anymore
> 3. Ordering property in the DDO can be used to force the use of an index for
> finding
> 4. Between is just a helper, it codes in a GE and LE command pair
>
> Suggestion: Read about the Constrain command in the help.It has some nice
> explanations.
>

Vincent Oorsprong
23-Feb-2005, 02:52 AM
Michael,

A Constrain As makes it impossible to use jump in jump out. So if you
need/want that feature you should not use constrain as.

A NE operator is - and cannot - use jump in and jump out since all records
except the given value are correct. Every record must be tested to see if it
does match.

--
Kind Regards,
Vincent Oorsprong
Data Access Europe BV
Lansinkesweg 4
7553 AE Hengelo
The Netherlands
Telephone: +31 (0)74 - 255 56 09
Fax: +31 (0)74 - 250 34 66
http://www.dataaccess.nl

John Tuohy
24-Feb-2005, 01:53 PM
I'll elaborate a bit on Vincent's remarks.

1. The order in the constrain statements makes no difference

When a constraint is built the runtime looks at all of the contraints and
tries to come up with the best strategy for dealing with the find when it is
asked to find on a particular index. The runtime tries to figure out it if
can jump in and if and when it can jump out of the index.

2. As soon as there is an AS constrain a jump into and jump out of index is
not possible anymore

This is partially accurate. When a constrain AS is encountered the runtime
says "well I have no idea what this does". For every record that is found
whatever is in the AS expression is evaluated and this has to be tested on
all applicable records. What the runtime can do is recognize that there are
other optimizing constraints it can apply first and reduce the number of
applicable records. In such a case the AS evaluation is only applied to a
sub-set of records. Therefore, using AS can be quite acceptable if you are
first reducing the applicable records by applying another optimizing
constraint.

3. Ordering property in the DDO can be used to force the use of an index for
finding

This can be very useful. If you are applying a constraint that only works
well with one particular index (and this is often going to be the case) you
do not want to be finding on other indexes. For example, a constraint on
customer.name will be quite fast using the customer name index and slow
using the customer_number index (all records may be searched). By setting
the Ordering property in the DDO you end up restricting all finds to that
one index. [I seem to remember that at one point the SLs managed to find by
a non-optimal index. This was fixed at some point, but I cannot remember
what version. I'd guess 9-10).

4. You can test all of this using the dd_debug.dg helper.

The runtime may not always optimize as you expect. The best way to check is
to test things. There debug helper makes it easy to track records searched
vs. records accepted. If the number gets too big, you might have problems.

Often the best way to test constraints is by asking for something that does
not exist. That creates a worst case search. Test by doing a find first and
a find last.

5. Understand when constraints are built

The process of the runtime looking at your constraints and coming up with a
strategy is referred to as rebuilding constraints. This happens when a view
is activated. If you change you constraints by changing the results of the
OnConstrain method, you must send rebuild_constraints to that DDO. When you
send rebuild_constraints it makes all the decisions about evaluations. The
only time you do not need to send rebuild_constraints is when the rules with
an AS expression change. The AS expression is evaluated every time a record
is found.

6. Selection lists can get you into trouble. By default they try to re-order
when you change columns. There are various ways to control this.

-John

Ian Telfer
24-Feb-2005, 05:03 PM
Hi John,

Thanks for the clarification, I remembered your presentation at DISD
'03. That has helped me a lot in the use of constraints & performance
ever since.

Cheers

Ian



John Tuohy wrote:
> I'll elaborate a bit on Vincent's remarks.
>
> 1. The order in the constrain statements makes no difference
>
> When a constraint is built the runtime looks at all of the contraints and
> tries to come up with the best strategy for dealing with the find when it is
> asked to find on a particular index. The runtime tries to figure out it if
> can jump in and if and when it can jump out of the index.
>
> 2. As soon as there is an AS constrain a jump into and jump out of index is
> not possible anymore
>
> This is partially accurate. When a constrain AS is encountered the runtime
> says "well I have no idea what this does". For every record that is found
> whatever is in the AS expression is evaluated and this has to be tested on
> all applicable records. What the runtime can do is recognize that there are
> other optimizing constraints it can apply first and reduce the number of
> applicable records. In such a case the AS evaluation is only applied to a
> sub-set of records. Therefore, using AS can be quite acceptable if you are
> first reducing the applicable records by applying another optimizing
> constraint.
>
> 3. Ordering property in the DDO can be used to force the use of an index for
> finding
>
> This can be very useful. If you are applying a constraint that only works
> well with one particular index (and this is often going to be the case) you
> do not want to be finding on other indexes. For example, a constraint on
> customer.name will be quite fast using the customer name index and slow
> using the customer_number index (all records may be searched). By setting
> the Ordering property in the DDO you end up restricting all finds to that
> one index. [I seem to remember that at one point the SLs managed to find by
> a non-optimal index. This was fixed at some point, but I cannot remember
> what version. I'd guess 9-10).
>
> 4. You can test all of this using the dd_debug.dg helper.
>
> The runtime may not always optimize as you expect. The best way to check is
> to test things. There debug helper makes it easy to track records searched
> vs. records accepted. If the number gets too big, you might have problems.
>
> Often the best way to test constraints is by asking for something that does
> not exist. That creates a worst case search. Test by doing a find first and
> a find last.
>
> 5. Understand when constraints are built
>
> The process of the runtime looking at your constraints and coming up with a
> strategy is referred to as rebuilding constraints. This happens when a view
> is activated. If you change you constraints by changing the results of the
> OnConstrain method, you must send rebuild_constraints to that DDO. When you
> send rebuild_constraints it makes all the decisions about evaluations. The
> only time you do not need to send rebuild_constraints is when the rules with
> an AS expression change. The AS expression is evaluated every time a record
> is found.
>
> 6. Selection lists can get you into trouble. By default they try to re-order
> when you change columns. There are various ways to control this.
>
> -John
>
>