Results 1 to 10 of 12

Thread: SQL Filters and Constraints

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009
    Location
    Rotterdam, Holland
    Posts
    2,025

    Default SQL Filters and Constraints

    I have question about the use of SQL Filters.
    We have a view in which one can select a parent and then child records are being shown.

    I'm using SQLfilters to filter the child records:

    Code:
    Object oPatient_DD Is a Patient_DataDictionary
    End_Object
    
    Object PatVerr_DD is a PatVerr_DataDictionary
       Set DDO_Server to Patient_DD
          Set Constrain_File to Patient.File_Number
       Set Ordering to 7
    
       Set pbUseDDSQLFilters to True
    
       Procedure OnConstrain
          String sSQLFilter
    
          Move <dynamic sqlstatement here> to sSqlFilters
                E.g.: Move "(PatVerr.SoortRegel = '' Or PatVerr.SoortRegel = 'AV' or PatVerr.SoortRegel = 'AF' or PatVerr.SoortRegel = 'AZ')" to sSqlFilters
    
          Set psSQLFilter to sSQLFilter
       End_Procedure
    End_Object
    Now, what I thought was that the SQL filter came ON TOP of the existing constraint.
    But the Help says this:
    SQL filters can be used alongside DataFlex constraints. When used with DataFlex constraints, the SQL filter will perform the filtered find on the server and, possibly, return a record. That record will then be tested by the DataFlex constraints.

    We noticed that in some situations adding an extra condition to the SQLFilter, the finding of the child records became very slow. As if a table scan is being done.

    As an example: Suppose this filter is slow: Move "(PatVerr.SoortRegel = '' Or PatVerr.SoortRegel = 'AV' or PatVerr.SoortRegel = 'AF' or PatVerr.SoortRegel = 'AZ')"
    The constraint in the DDO obviously is an extra condition.
    But by adding this same condition to the SQLFilter the view becomes a lot faster:

    Get File_Field_Current_Value File_Field Patient.PatientNr to iPatientNr
    Move "((PatVerr.PatientNr = iPatientNr) AND (PatVerr.SoortRegel = '' Or PatVerr.SoortRegel = 'AV' or PatVerr.SoortRegel = 'AF' or PatVerr.SoortRegel = 'AZ'))" to sSqlFilters

    My question is: In case of a constraint and SQL filter, should we always add the constraint to the SQLFilter as well? But his would mean we have to send a Rebuild_Constraint before each find.
    Or might this result in some unwanted behaviour?
    Last edited by Pieter van Dieren; 21-Feb-2024 at 09:16 AM.
    Regards,

    Pieter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •