Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    5

    Unanswered: Creating a View using UDF's help/slow.

    I have created a UDF using the maximizer database. I know this is not directly related to pervasive but i'm just wondering if anyone could clarify why my view using UDF's is taking so so long to process?


    Select C.Client_Id, C.Contact_Number, SexUDF.AlphaNumericCol As Sex
    From
    AMGR_Client_Tbl C

    LEFT OUTER JOIN AMGR_User_Fields_Tbl SexUDF
    On
    C.Client_Id = SexUDF.Client_ID and
    C.Contact_Number = SexUDF.Contact_Number and
    SexUDF.Type_Id = 43

    Thanks

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Considering Pervasive doesn't directly support UDF (if they are User Defined Functions), you're right that this isn't related to Pervasive.
    Are you able to run the query in the PCC directly? If so, is it slow? If not, then there's something else causing the problem.
    One thing you might try doing (since you're using AND clauses) is rearrange the order of the restrictions. Make sure that it's searching on the one with the fewest matches first. Also, make sure that the fields are indexes and the DDF/Data files pass a consistency check.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Nov 2004
    Posts
    5

    Ok.

    By UDF I mean user defined fields - maximizer emulates UDF's as new tables etc.

    When I try to run the query directly on the table it is still very very slow. Nothing changes at all.

    Also it seems to have failed all tests :

    ===============================================
    Database consistency test results.
    Test was run on: Saturday, November 20, 2004 07:42:28 PM

    DSN: MAP_EsconaTutorial

    Database name: EsconaTutorial

    Database Location: \\CRM-SERVER\MXDATA\SAMPLES\TUTOR.820\
    ===============================================


    ===============================================
    Database Check Statistics
    ===============================================
    Pervasive.SQL Compatibility Mode = 8
    Tables Checked = 35
    Tables Passed = 13
    Tables Failed = 22

    Illegal Object Names = 1
    Record Length Inconsistencies = 36
    Variable Length Column Inconsistencies = 0
    Data Type Inconsistencies = 0
    Column Overlaps = 0
    Full Path (instead of Relative) Table Locations = 0
    Table Files Don't Exist = 0
    Index Inconsistencies = 71
    System Table Inconsistencies = 0
    General Dictionary Inconsistencies = 0
    Last edited by ryammagic; 11-20-04 at 20:40.

  4. #4
    Join Date
    Nov 2004
    Posts
    5

    Tables that i'm trying to create the view -> Errors.

    +++++++++++++++++++++++++++++++++++++++++++++++
    Table Name = AMGR_User_Fields_Tbl

    Fixed Record Length Mismatch.
    Dictionary indicates length 459
    Data file indicates length 244.
    Variable Data Type Column Mismatch.
    The Data file indicates that a variable length column exists.
    The Dictionary does not.
    The Data File Index is Not Defined in the Dictionary.
    The data file defines index 0 seg 0
    The dictionary does not.
    The Data File Index is Not Defined in the Dictionary.
    The data file defines index 0 seg 1
    The dictionary does not.
    The Data File Index is Not Defined in the Dictionary.
    The data file defines index 0 seg 2
    The dictionary does not.
    The Data File Index is Not Defined in the Dictionary.
    The data file defines index 0 seg 3
    The dictionary does not.
    +++++++++++++++++++++++++++++++++++++++++++++++
    Table Name = AMGR_UsrFldDefsTbl



    Table Name = AMGR_Client_Tbl

    Fixed Record Length Mismatch.
    Dictionary indicates length 1773
    Data file indicates length 2038.
    Variable Data Type Column Mismatch.
    The Data file indicates that a variable length column exists.
    The Dictionary does not.
    The Data File Index is Not Defined in the Dictionary.
    The data file defines index 0 seg 0
    The dictionary does not.
    The Data File Index is Not Defined in the Dictionary.
    The data file defines index 0 seg 1
    The dictionary does not.
    +++++++++++++++++++++++++++++++++++++++++++++++

  5. #5
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    That's right. Maximizer stores a fixed record structure in a variable length field. This will keep Pervasive from optimizing because Pervasive has to create a temp file to sort. Ideally Maximizer would change the format of their UDFs to be a real field in a table rather than store a fixed record in a variable field..
    I'm not sure there's anything that can be done to speed things up..
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  6. #6
    Join Date
    Nov 2004
    Posts
    5
    Can you explain to me what you mean by a few of the terms you're using. I've only been playing with maximizer / pervasive for the past few days.


    * Maximizer stores a [fixed record structure in a variable length field] Do you just means it stores its virtual table information in another table? Which is my understanding.

    * Ideally Maximizer would change the format of their UDFs to be a real field in a table rather than store a fixed record in a variable field - ?

    Thanks
    Ryan.

Posting Permissions

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