FWIW - when doing complex queries against VFP data I find it MUCH faster to prequery the main tables on simple rushmore optimizable expressions and then use those cursors in my "real" query
select fieldnames from maintable1 where somenum=x into cursor c_maintable1 readwrite nofilter
select fieldnames from maintable2 where somenum=x into cursor c_maintable2 readwrite nofilter
select c_maintable1.fieldnames,c_maintable2.fieldnames
from c_maintable1 inner/left join on c_maintable2 on somecriteria blah blah blah
Using this technique I have sped up queries by a factor of 10 or more.
>Can a SELECT statement with Transform() or Count() functions be optimized?
>
>I'm finding that what shoudl be a pretty simple SQL query runs kind of slow, and I must call it 4 times with different value to build 4 separate cursors. I'm looking at ways to make it faster. I have index on all the fields, but I suspect use of Transform and/or Count() is slowing it down since VFP cannot optimize it. The query is running against FoxPro DBF tables, not SQL server.
>
>Function:
>
>
>Lparameters lcMachineClass, tcMachineType, tcCursor
>
>Select Machines.mach_num, Machines.mach_name, ;
> '(' + Transform(Count(MachineTasks.job_num)) + ') ' As job_count;
> From Machines;
> Left Join ;
> (Select Schedule.job_num, Schedule.mach_num As sched_mach_num ;
> From Schedule ;
> Left Join Jobs On Schedule.job_num = Jobs.job_num ;
> Where Jobs.Status = 'A' And Schedule.Status != 'C') MachineTasks ;
> On Machines.mach_num = MachineTasks.sched_mach_num;
> Where Machines.Status = 'A';
> And Machines.Class = lcMachineClass;
> And Machines.Type = tcMachineType;
> Group By Machines.mach_num, mach_name;
> Into Cursor (tcCursor) Readwrite
>
>Replace job_count With '' For job_count = '(0)' In (tcCursor)
>Goto Top In (tcCursor)
>
>
>
>
>
>
>.
Brandon Harker
Sebae Data Solutions