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.

One Response to VFP9 SQL Features, Correlated UPDATE-SQL

  • Anonymous says:

    [ Rounding up some good Fox-related links my rss reader has accumulated for almost a month. If you regularly update those foxpro feeds you might have visited some of these links already. …]

Leave a Reply

Your email address will not be published. Required fields are marked *