VFP9 has a lot of new features, some of them are at embebed SQL language, one of these features was added in UPDATE-SQL, where VFPT add the FROM and JOIN clases.
Ok, here are my tests:
Lets supose I have a table with every entry in the system , first I create a 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()*100000, 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
And I have an entity where I would like to get the sumary of each agent by month and year , 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 not be completed, the inner select returns a null value, getting a error with yMonth assigment.
Test 4:
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
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 above codes were the result of a thread in UniversalThread where I was asking for HOW TO improve this features. Thanks to Cetin Bazos, Fabio Lunardoni and Sergey Berezniker.
----------------------------
Espartaco Palma
SysOp www.PortalFox.com
México D.F.