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

>
>
>.
I suggest:

Select  Machines.mach_num, Machines.mach_name, ;
	CAST('(' + CAST(Jobs_Count as Varchar(5)) + ')  ' AS C(8)) As job_count;
	From Machines;
		Left Join ;
			(Select count(Schedule.job_num) as Jobs_Count, 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';
                                                              GROUP BY Schedule.match_num ;
                                                             ) MachineTasks ;
			On Machines.mach_num = MachineTasks.sched_mach_num;
	Where Machines.Status = 'A';
	And Machines.Class = lcMachineClass;
	And Machines.Type = tcMachineType;	
	Into Cursor (tcCursor) Readwrite

However, I suggest to not return the JobsCount as character column, but return is as numeric instead. It will be much easier to do all operations on numeric value and also you can always format results the way you want when you output them to something else.