>I am having a problem with a UDF call from SQL SELECT. It is painfully slow in the form but the UDF has a fairly complicated dependency on dates and # of active child records based upon these dates.
>
>The best way to overcome the speed problem, I have discovered, is to get rid of the UDF call and replace the logic with IIF()s and other native Foxpro functions imbedded in SQL statements.
>
>Problem: In order for this to work I have to be able to do a conditional count of records grouped by parent id and the COUNT(field_name) SQL function isn't working properly even with the correct GROUP BY and HAVING clauses.
>
>Question: Is there a native Foxpro function that anyone out there knows about that returns the # of records which meet a conditional parameter, kind of like:
>
>COUNT(Id, startdate >= DATE() AND enddate <= DATE()) AS numberactive,
>
>or maybe a trick you can play with the CALCULATE or COUNT TO commands to get SQL to interpret the AS clause correctly.
>
>Help, Anyone....

When you use a UDF in SQL, the UDF is called for each record processed, hence the performance hit.

Try something like:

select count(id) as Kount from mytable where ;
       not_sure_about_your_date_conditions_above ;
       into cursor MyCursor