David,

>>I certainly would agree if the values didn't change much from record to record. But they do.
It's a udf which converts a coded field into meaningful English, eg 1-Low 2-Medium 3-High. So I don't see a way around that issue. It's different on every record. Performance is pretty good actually.>>


Yes, but in this case, the UDF is performing the same functionality as a pair of tables that have been normalized to second normal form (???)

From my normalization rules, conveniently posted on my wall:

Second Normal Form - Eliminate Redundant Data
· Create separate tables for sets of values that apply to multiple records.
· Relate these tables with a foreign key.


MainDataTable

Field1 Field2 FldStatus
>data1> >data2> L
>data1> >data2> M
>data1> >data2> L
>data1> >data2> H
etc...

StatusTable

FldStatus Desc
L Low
M Medium
H High


USE StatusTable IN 0 ORDER FldStatus

SELECT MainDataTable
SET RELATION TO FldStatus INTO StatusTable



The above tables and code perform the same functionality as:

FUNCTION StatDesc(tcCode)
LOCAL lcRetCode

DO CASE
CASE tcCode = "M"
lcRetCode = "Medium"
CASE tcCode = "H"
lcRetCode = "High"
OTHERWISE
lcRetCode = "Low"
ENDCASE

RETURN(lcRetCode)


If this kind of stuff was being done in a fairly low-level language (i.e. C) the performance between the two methodolgies might be comparable and the effort required to write the translation function would be less than writing the data handing... But we're not talking about C. We're talking about VFP which is first and formost a DBMS. With VFP, setting the relationship between the two tables is going to yield performance thats two maybe three ORDERS OF MAGNITUDE (thats hundreds to thousands of times) faster. And while in my previous post I made the lookup table a TABLE, in actual practice, I would do something like:


SELECT APN AS Status, SPACE(25) AS FullStatus ;
FROM MyTable ;
GROUP BY Status ;
INTO CURSOR ShwSt

USE DBF() AGAIN IN 0 ALIAS ShowStatus
USE IN ShwSt

UPDATE ShowStatus ;
SET FullStatus = GetDesc(ShowStatus.Status, "APN")

Which makes the cursor read/write for the SQL UPDATE, but removes the cleanup overhead of getting rid of the temporary cursor "ShowStatus"


>>I certainly would agree if the values didn't change much from record to record. But they do.


Actually, this kind of scenario is one of the major reasons for table normalization.

It works really well. Promise :-)


Regards,
Thom C.