>Ok, here are my tests:
>
>First I create an cursor with 10 millions of records, with a suposed 10,000 agents in a period of 4 years.
>
>
>CREATE CURSOR cMovs (iid int autoinc, iAgentID int, 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
>
>
>Then, the cAgent cursors where I suposed to stored 4 years of records...
>
>CREATE CURSOR cAgent (iid int autoinc, iAgentID int,yMonth y, nMonth int, nYear int)
>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)
> ENDFOR
> ENDFOR
>ENDFOR
>*** Of curse, indexex
>INDEX ON iAgentID TAG idxAgent
>INDEX ON nMonth TAG idxnMonth
>INDEX ON nYear TAG idxnYear
>
>
>Test 1:
>
>UPDATE A ;
> SET yMonth = cMovs.yAmount ;
> FROM cAgent A;
> JOIN (SELECT iAgentID , MONTH(dDate) nMonth , YEAR(dDate) nYear ,
> SUM(yAmount) yAmount ;
> FROM cMovs group by 1,2,3) cMovs;
> ON cMovs.iAgentID = A.iAgentID;
> AND cMovs.nMonth = A.nMonth ;
> AND cMovs.nYear = A.nYear;
> WHERE A.nMonth = 1 AND A.nYear=2005
>
>
>As you can imagine it, it's the worst, basically for the fact we are going an full select of all cMovs records, not necesary :(
>
>Test 2:
>
> UPDATE A ;
> SET yMonth = cMovs.yAmount ;
> FROM (SELECT iAgentID, SUM(yAmount) yAmount ;
> FROM cAgent JOIN cMovs ;
> ON cMovs.iAgentID = cAgent.iAgentID ;
> AND MONTH(dDate)= cAgent.nMonth ;
> AND YEAR(dDate) = cAgent.nYear;
> WHERE cAgent.nMonth = 1 AND cAgent.nYear=2005 ;
> group by iAgentID) cMovs;
> JOIN cAgent A;
> ON cMovs.iAgentID = A.iAgentID
>
>
>Slow ... just Slow... My test was running with 300+ seconds!
>
>
>Test 3:
>
>UPDATE A ;
> SET yMonth = (SELECT SUM(yAmount) ;
> FROM cMovs ;
> WHERE cMovs.iAgentID = A.iAgentID AND ;
> MONTH(dDate)=1 AND YEAR(dDate)=2005);
> FROM cAgent A;
> WHERE A.nMonth = 1 AND A.nYear=2005
>
>
>The test could be completed, the inner select returns a null value, getting a error with yMonth assigment.
>
>Test 4:
>
>CLEAR
>UPDATE A ;
> SET yMonth = cMovs.yAmount ;
> FROM (SELECT iAgentID, SUM(yAmount) yAmount ;
> FROM cMovs ;
> WHERE MONTH(dDate)=1 AND YEAR(dDate)=2005;
> group by 1) cMovs;
> JOIN cAgent A;
> ON cMovs.iAgentID = A.iAgentID;
> WHERE A.nMonth = 1 AND A.nYear=2005
>BROWSE
>
>
>Full Optimization at inner query, none at intermediated , full at final query (update), acceptable: +-9 seconds.
>
>Test 5:
>
>Select iAgentId, Month(dDate) As nMonth, ;
> Year(dDate) As nYear, Sum(yAmount) As YTot ;
> FROM cMovs ;
> GROUP By 1,2,3 ;
> WHERE MONTH(dData) = 1 AND YEAR(dDate)=2005;
> into Cursor crsSums ;
> nofilter
>
>Update cAgent ;
> SET yMonth = t2.YTot ;
> FROM crsSums t2 ;
> WHERE t2.iAgentID = cAgent.iAgentID ;
> AND cAgent.nMonth = 1 And cAgent.nYear=2005 ;
> And t2.Month = cAgent.nMonth ;
> AND t2.Year = cAgent.nYear
>
>
>The Cetin's recomendation (with little modifications) has almost the same behavior as Test 4 (Fabio's), results with +- 9 seconds, I didn't see to much differences.
Esparta,
I touched mine version a little and it's slightly faster (but negligible - apprx. 0.5-0.6 secs when timings were 15.4 vs 16.0 ). I liked Fabio's:)
Select iAgentID, Sum(yAmount) As YTot ;
FROM cMovs ;
GROUP By 1 ;
WHERE Year(dDate)=2005 AND Month(dDate) = 1 ;
into Cursor crsSums ;
nofilter
Update cAgent ;
SET yMonth = t2.YTot ;
FROM crsSums t2 ;
WHERE t2.iAgentID = cAgent.iAgentID ;
AND cAgent.nYear=2005 ;
AND cAgent.nMonth = 1
One way or the other the time is spent mostly in summing. Maybe you could consider a trigger on cmovs updates for your frequently needed sums and keep them in a support table ready to be used.
Cetin
