Fix this bug:
Set Key To lnFilterYear*12+lnFilterMonth -1
and run.
>>>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
>
