Hi Aleksey,
I am not sure the following is a bug or a feature <g>.
I have read the parts in the help file warning about using a UDF inside a index expression, for instance in "Considerations for Creating Index Expressions".
I have a situation with a large free tables (.dbf and .fpt both >1GB, .cdx 300 MB) and a very stable procedure file always loaded when working with the table.
When I join on index expressions based on pure dbf-table fields or index expressions using only vfp functions on dbf/fpt fields (padded to stable length in case of fpt-fields) sys(3054) shows the already created index is used directly in the join. If index expressions involving UDF's are used, a temporary index is calculated, even if the UDF is certain to be of fixed length: either returning a PADR() or wrapping the udf in the index expression like PADR(myUdf(field1), 20).
I do think that functions in a .fll don't show that annoying behaviour, but haven't verified - perhaps I am mixing it up with the level Type() can check, as type() will return the type of a fll-function but not of a vfp-udf.
What I would like to do is something similar to
set proc to myPrc additive
select newData
index on Padr(myUdf1(Field1), 20) + Padr(myUdf2(Field2), 15) tag F_1_2_new
select oldData
index on Padr(myUdf1(Field1), 20) + Padr(myUdf2(Field2), 15) tag F_1_2_Old
Select ;
Nw.Field3, Ol.Field4 ;
from newData Nw ;
join oldData Ol ;
on Padr(myUdf1(ol.Field1), 20) + Padr(myUdf2(ol.Field2), 15) ;
= Padr(myUdf1(ne.Field1), 20) + Padr(myUdf2(ne.Field2), 15)
select newData
local lcSearchMe
lcSearchme = Padr("....", 35)
Locate for Padr(myUdf1(Field1), 20) + Padr(myUdf2(Field2), 15) = m.lcSearchMe
as there is ample space in the .cdx left without balloning up either dbf or fpt with redundant/calculable data. Since the .cdx could be reused instead of always creating a temp index, it would be a real time saver, as always creating a temp index wit a udf on a table ths size takes time. I also have doubts if such an index would be used in xBase-situations by rushmore. So any xBase code involving/using udf-indices is mostly seek()-scan rest in my code and not even trying to use rushmore.
I currently see the following alternatives:
create another field in the dbf/fpt, fill it using the udf()'s and index on that field (I hesitate because of table size)
perhaps port the code into a fll (as it is mostly simple string massage, probably faster executing but I have no dire need otherwise)
check if moving to stored procedures makes indices with vfp-udf accessible for rushmore
hope there is some switch/syntax to force an existing index to be used directly<g>
Any thoughts / hints from your side ? Is this a bug, a feature or should I formulate a feature request ?
If anything is unclear, pls. don't hesitate to ask for clarification. If you say an index created with a vfp-udf is expected to be reused in joins, I'll try to extract some example code showing that this is (at least not always) the case.
regards
thomas