Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Append or not to append, that is the Q!
Message
 
To
All
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Append or not to append, that is the Q!
Miscellaneous
Thread ID:
01313927
Message ID:
01313927
Views:
57
One of our customers is having some time problems with a procedure of closing the period of a particular module. We just identified the step where the function takes the longest which i am posting here
*:This is a temp cursor with xx,xxx amount of records, since I do not have access to the real data 
*:I just can't really now how many records there are, but most likely there are thousands
select CurIctfer
go top in CurIctfer

*:This is basically a wrapper routine for the SQLPrepare() VFP function
if !PrepareSql(pnHandle, "insert into mySQlTable (cuid,cbatcno,cjeid, cacctid,cdescript,creference," +; 
   "cyear, cpdno, cstatus, csource, dtrs, ntrsamt, ctrstype, ctrsno, cobjno) values (?CurIctfer.cuid, " +;    
   "?CurIctfer.cbatcno, " +;
   "?CurIctfer.cjeid, ?CurIctfer.cacctid, ?CurIctfer.cdescript, ?CurIctfer.creference," + ;
   "?CurIctfer.cyear, ?CurIctfer.cpdno, ?CurIctfer.cstatus, ?CurIctfer.csource," +;
   " ?CurIctfer.cTrs, ?CurIctfer.ntrsamt, ?CurIctfer.cTrsType, ?CurIctfer.cTrsNo, ?CurIctfer.cObjNo)")
    return .f.
endif

*:and here is where the inserts are made, goes record by record and inserts each one to the back end 
*:database
scan   
    if !SetSqlData(, pnHandle)
        return .f.
    endif
ENDSCAN

=UnprepareSql(pnHandle)
The customer claims this piece of code takes over 2 hours. so I was wondering if We change the Scan..Insert..Endscan and replace it with a append from kinda like this
*:I would create a Remote view, NoDataOnLoad, base on the mySQlTable, Table Buffering
Select mySQlTable
APPEND FROM CurIctfer
TABLEUPDATE()
do you guys think this would be faster, or slower?

maybe not with a append from, maybe the "CurIctfer" could be a remote view, and then just perfom the Tableupdate(). would this be helpfull?

Once again, i do not have the actual data to do the testing so this is basically Hypothetical

regards
.......
DO WHILE .T.
      ME.Work()
ENDDO
Next
Reply
Map
View

Click here to load this message in the networking platform