>>Hi everybody,
>>
>>Does anyone know whether VFP Select-SQL with Group By option creates a temp result set without group by first and then performs GROUP BY? Or how exactly GROUP BY works?
>>
>>Here is the reason I'm asking:
>>
>>I had select sql that returned data as a temp cursor, then I performed Group By on it to obtain results I wanted. I then figured out, that I don't need this intermediate cursor and I better would do Group By in this cursor right away. I was expecting a performance gain by doing so. However, I don't seem to have this performance gain. I haven't measured the times (my mistake, since I can not revert the code that easily), but I have an impression that speed is the same or even worse.
>>
>>Thanks in advance for your input.
>
>GROUP BY is not optimizable
Ok, what is better then:
select cTrans_pk, cUserID, cUsGrLink_pk, yBalance from ... where ... into cursor curTemp
select count(cTrans_pk) as iAccounts, sum(Balance) as Balance, cUserID from curTremp group by cUserID
or
select count(cTrans_pk) as iAccounts, sum(Balance) as Balance, cUserID from ... where ... group by cUserID
E.g. do it in two steps or in 1 step?
If it's not broken, fix it until it is.
My Blog