>>For example check Thread #1011797
>>It's a sample where SQL is faster.

I made up some xBase code that is faster than the fastest SQL (as far as I understood) made up in that Thread.

I added an index to each table that might also speed up the sql, you may try that, modifying the WHERE clauses. Also it's unfair to first do the SQL and then let the xBase code profit from cached records. But if you turn it around and start each version once, the SQL took about 2-3 times longer than the xBase code. Nevertheless the two SQL statements are surely better maintainable and understandable than the xBase Scan-Loop.

Bye, Olaf.

Local lnFilterYear, lnFilterMonth
lnFilterYear = 2005
lnFilterMonth = 1

close data all
Clear

Local lnCounter
Create Cursor cMovs (iid i Autoinc, iAgentID i, yAmount Y, dDate d)
Rand(-1)
For lnCounter=1 To 10000000
   Insert Into cMovs (iAgentID, yAmount, dDate) ;
      VALUES ((Rand()*10000)+1, Rand()*1000000,Date(2001,01,01)+ (Rand()*1825))
Endfor
** Of course, the indexes...
Index On Year(dDate) Tag idxYDate
Index On Month(dDate) Tag idxMDate
Index On iAgentID Tag idxAgent
* new index for xBase code:
Index On iAgentID*120000+Year(dDate)*12+Month(dDate)-1 Tag idxAYM

Create Cursor cAgent (iid i Autoinc, iAgentID i, yMonth Y, nMonth i,  nYear i)
Rand(-1)
For lnYear = 2001 To 2005
   For lnMonth = 1 To 12
      For lnAgent = 1 To 10000
         Insert Into cAgent (iAgentID, yMonth, nMonth, nYear) ;
            VALUES (lnAgent,0,lnMonth,lnYear)
         lnCounter = lnCounter + 1
      Endfor
   Endfor
Endfor
*** Of curse, indexes
Index On iAgentID Tag idxAgent
Index On nMonth Tag idxnMonth
Index On nYear Tag idxnYear
* new index for xBase code:
Index On nYear*12+nMonth-1 Tag idxYearMon

* SQL to sum up the amounts each agent earned in a certain month
t1= Seconds()
Select iAgentID, Sum(yAmount) As YTot ;
   FROM cMovs ;
   GROUP By 1 ;
   WHERE Year(dDate)=lnFilterYear And Month(dDate) = lnFilterMonth ;
   into Cursor crsSums ;
   nofilter

Update cAgent ;
   SET yMonth = t2.YTot ;
   FROM crsSums t2 ;
   WHERE t2.iAgentID = cAgent.iAgentID ;
   AND cAgent.nYear = lnFilterYear ;
   AND cAgent.nMonth = lnFilterMonth
? "SQL",Seconds() - t1

* The same with xBase code:
t1= Seconds()
Select cMovs
Set Order To idxAYM
Select cAgent
Set Order To idxYearMon
Set Key To lnFilterYear*12+lnFilterYear-1
Set Relation To iAgentID*120000+nYear*12+nMonth-1 Into cMovs
Set Skip To cMovs
Local lnYMonth, lnAgentID
lnYMonth = 0.00
Locate
lnAgentID = cAgent.iAgentID
Scan
   If lnAgentID # cAgent.iAgentID
      lnAgentID = cAgent.iAgentID
      Skip -1
      Replace yMonth With lnYMonth
      Skip 1
      lnYMonth = 0.00
   Endif
   If !Eof("cMovs")
      lnYMonth = lnYMonth+cMovs.yAmount
   Endif
Endscan
Set Skip To
Set Relation To
Set Key To
? "xBase",Seconds() - t1