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)
for lncounter=1 to 10000000
   insert into cmovs (iagentid, yamount, ddate) ;
       values ((rand()*10000)+1, rand()*100000, date(2001,01,01)+ (rand()*1825))
*** 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)
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)
*** 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 ;

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 *