Hi!

You can receive partical optimization because SET DELETED is ON and you do not have index on DELETED() in your table. SET DELETED OFF and you will see.

GROUP BY is not optimizable at all because it uses the result of query for it instead of source tables with indexes. COUNT(*) and all other aggregate functions - the same. Maybe SQL Server have better logic for this - I do not know, in VFP for sure optimizable parts are only JOIN and WHERE conditions. Well, to sort and group VFP might decide to make a temporary index or similar...

HTH.

>I am seeing something unexpected here (well, at least for me [grin]).
>I created a 3.2-million row table with two integer columns (I inserted Sys(3) values into the first column and 0 into the second column and let it run all weekend). Then I copied the table to a new one but made the first column type character. Then I created simple indexes on the first column in each table.
>
>
>I ran the program below and got these results:
>

>?
>tTime1= Seconds()	
>*Select Count(*) from UniqI Group by iUniq into cursor intcount nowait		&& 20.828 secs no optimiz
>*Select Count(*) from UniqI into cursor intcount nowait		&& 4.391 secs no optimiz
>*Select * from UniqI where iUniq = 79421453 into cursor intcount nowait	&& .016 secs partial optimiz
>tTime2 = Seconds()
>? "Integer SQL results - " + Str( tTime2 - tTime1 ,8,3 )
>? Sys(3054,1)
>?
>tTime1= Seconds()	
>*Select Count(*) from UniqI Group by iUniq into cursor charcount nowait	&& 21.219 secs no optimiz
>*Select Count(*) from UniqC into cursor charcount nowait	&& 7.156 secs no optimiz
>*Select * from UniqC where cUniq ="79421453" into cursor charcount nowait	&& .016 secs partial optimiz
>tTime2 = Seconds()
>? "Char SQL results - " + Str( tTime2 - tTime1 ,8,3 )
>? Sys(3054,1)	
>

>
>I understand that the Group By is not optimized (and why isn't it in VFP7?), but why is Count(*) not optimized? And why am I only getting partial optimization when I am querying for a single record only? I also noticed that while the UniqI table is 2/3 the size of UniqC, it is not singificantly faster on the query times.
>
>Can some more knowledgeable folks straighten me out on these issues?
>
>This is all run under Win2K Pro SP2 and VFP7 on a 733MHz 256mb RAM system, nothing special (no antivir, etc) running while VFP is running.
>
>Thanks.