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

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.

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

+++++++++++++++++++++++++++++++++++++++++++++++
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.
+++++++++++++++++++++++++++++++++++++++++++++++

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

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.