View RSS Feed

Development Team Blog

Constraints and Local Variables

Rate this Entry
Constraints and local variables is a frequent question in the forums, let's see if we can sort this out. The basics is that you set it up once, and then the runtime uses the constraints when finding records thereafter. If you want to change your constraints, you rebuild the whole set of constraints, and then the runtime uses that thereafter.

The way constraints are set up is complicated a little with DDs and I won't go into the details, but essentially you augment the OnConstrain event to set up the constraints, and send Rebuild_Constraints to instruct the DD to reset the constraints and thereby triggering OnConstrain again. The key thing to remember is that OnConstrain is not triggered for every record, but just once when setting up the constraints, and then the runtime uses those constraints internally when finding records.

There are basically two main types of constraints. The most common type is used with Constrain X EQ Y, where EQ can be replaced with LT/NE and so on. The second type of constraint is Constrain X As, referred to as Constrain As. There's also a third type of Constrain Relates To, but for the purpose of this discussion it falls into the bigger category of the first type of constraint.

Simple Constraints
Remember that the constraints are set up once in OnConstrain, and the first type of constraint lets you constraint with a fixed set of comparison criteria such as "equals this value" or "equals the value of this field". The runtime knows all the details of what values to compare and how to perform the comparison for each record. At this point the runtime also makes the decision about the most optimal index to use and how to perform jump-in and jump-out, I won't go into details about that, just know that it's there. When a record is found and the constraints are evaluated, it's performed in the most optimal way and completely internally without executing DataFlex code.

There are essentially no limits to how you can specify the criteria for the constraints. You can fully utilize local variables in the first type of constraint. If you do, the value of the variable is evaluated during OnConstrain, and the runtime will perform the constraint against that value, not the variable itself. Same thing if you specify an expression, it's evaluated immediately and the constraint is performed against that value. However, if you constraint against another field, the runtime will store the information about the field and perform the evaluation against the value of that field dynamically.

The key thing about this type of constraint is that all the criteria is known in advance, even though the actual values may not be known in advance.

Constrain As
The second type of constraint, Constrain As, is completely different. You constrain against an expression, and the expression can be anything at all, including function calls executing DataFlex code and everything. This means that the runtime knows nothing about the criteria for the constraint. The values are evaluated dynamically, but not only that, the runtime doesn't even know how the values are calculated, it's just handed an expression. The expression is like a black box with a button, the runtime pushes the button for each record found, and out pops an answer, True/False.

This means two things; the runtime cannot perform any kinds of index optimizations based on the expression since it knows nothing about the expression, and the expression must be fully evaluated for each candidate record (including executing DataFlex code).

Herein lies the secret answer. Remember that in the first type of constraint, the runtime will use the actual value of local variables at the time, rather than evaluating the local variable dynamically. Since this is an expression that must be fully evaluated for each record, it cannot pre-compute the value, and when it's time to evaluate the expression, the local variable is long gone and doesn't exist anymore. Remember, the local variable exists within the scope of OnConstrain, but the expression is evaluated outside the scope of OnConstrain, and evaluating an out of scope variable results in really weird, and certainly not the expected, behavior. That's why local variables cannot be used with Constrain As, while they can certainly be used with other constraints.

The other conclusion we can draw from this is that Constrain As cannot be used to optimize the constraints and perform index jump-in/jump-out. The careful reader will have noticed that I didn't say it removes all optimizations, just that it cannot be used to optimize the constraint. If there are other constraints present that can be used for optimizations, those optimizations still apply. Thus Constrain As can still be very fast when used together with other optimized constraints.

Comments

  1. Bob Worsley's Avatar
    The other conclusion we can draw from this is that Constrain As cannot be used to optimize the constraints and perform index jump-in/jump-out. The careful reader will have noticed that I didn't say it removes all optimizations, just that it cannot be used to optimize the constraint. If there are other constraints present that can be used for optimizations, those optimizations still apply. Thus Constrain As can still be very fast when used together with other optimized constraints.
    So are you saying that when you have some "regular" constraints that would optimize the constraint, a "Constrain As" would act as a sub constraint?

    Thanks for the clarification on this, I sort of understood it going in but his last paragraph is an eye opener. - Bob
  2. Mk@p3rfect's Avatar
    the runtime also makes the decision about the most optimal index to use
    Does the order in which the constraints are declared affect this?
    Eg:
    Customer has two indexes. Type and name
    You have 10000 customers who are type Credit, but only 100 Customers who are called smith and could be any type.
    Would
    Constrain Customer.type eq 'Credit'
    Constrain Customer.name eq 'Smith'
    run slower than
    Constrain Customer.name eq 'Smith'
    Constrain Customer.type eq 'Credit'
    or does the declared order make no difference
  3. Sonny Falk's Avatar
    Quote Originally Posted by Bob Worsley
    So are you saying that when you have some "regular" constraints that would optimize the constraint, a "Constrain As" would act as a sub constraint?
    Yes, exactly. The Constrain As expression is only evaluated for the subset of records that already satisfy the criteria of other jump-in/jump-out-optimized constraints.
  4. Garret Mott's Avatar
    It does make things clearer.

    Is there any speed difference between these 2 constrains?

    Code:
    Procedure OnConstrain
        Integer iOrderNo
    
         Get piOrderNo to iOrderNo
         Constrain OrdItem.OrderNo EQ iOrderNo
    End_Procedure
    and

    Code:
    Procedure OnConstrain
         Constrain OrdItem.OrderNo EQ (piOrderNo(Self))
    End_Procedure
    I ask because I make a habit of never using local variables so I don't get bitten by accidently using one in a Constrain As.

    Regards -

    Garret
    Updated 10-Aug-2009 at 09:48 PM by Garret Mott (forgot ()
  5. Knut Sparhell's Avatar
    Does the order of wich you specify the constraints have anything to say? Are "Constrain AS" statements always executed on just the subset of records obtained by any "Constrain Table.Column" statements, regardless of their order of appearance (line) in the OnConstrain event?
  6. Peter Crook's Avatar
    It seems that the restriction on using local variables in Constrain As doesn't apply to a conditional execution of a constraint. i.e. If (bcond = True) Constrain file.field AS (<expression>).

    Am I right in this?

    Peter
  7. Knut Sparhell's Avatar
    Quote Originally Posted by Peter Crook
    It seems that the restriction on using local variables in Constrain As doesn't apply to a conditional execution of a constraint. i.e. If (bcond = True) Constrain file.field AS (<expression>).

    Am I right in this?
    Yes. This local variable is evaluated in the OnConstrain event, not when individual records are tested. It's the use of local variables inside the Constrain As expression itself that's unpredictable.