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