>>>>>>>>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
>>>>>>>
>>>>>>>Why not make this cursor updatable and send all updates directly?
>>>>>>
>>>>>>acctually that is what I implied on the note above
>>>>>>
>>>>>>>>maybe not with a append from, maybe the "CurIctfer" could be a remote view, and then just perfom the Tableupdate()
>>>>>>
>>>>>>would it be faster?
>>>>>
>>>>>How many records?
>>>>>BTW, From where your CurIctfer cursor comes from?, Can't you do its creation directly on your BackEnd?
>>>>
>>>>From where your CurIctfer cursor comes from?,
>>>>

>>>>>>>>*: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
>>>>

>>>>
>>>>Can't you do its creation directly on your BackEnd?
>>>>
>>>>not really, I mean , I migth, but this represents a huge change on coding, i am just trying to figure out how to do it a bit faster with out changing a lot of code.
>>>
>>>
>>>What I meant was how CurIctfer is created?
>>>Some SPT command?
>>>Import from some external file?
>>
>>acctually this cursor is created using a select statement to bring some data. then stays
>>
>>select .... from.... into cursor curictfer
>

>
>
>And I hope that SELECT is from your backEnd database, Isn't it?


Glad you asked, look what I found out, and listen to my theory

acctually the "curictfer" cursor is created using a SELECT statement against a REMOTE VIEW like a direct select within the Visual foxpro environment, and not as a SQLEXEC command against the Back end.

I guess the first is slower than the second choice, right?

after getting the Data then the code i just posted is performed using a SQLEXEC command for each one of the records on the cursor. even describing the procedure feels slow, doesn't it? LOL

My theory is that if I change this behavior and instead of using a SELECT statement against a REMOTE VIEW like a direct select within the Visual foxpro environment, I change this and send a SQLEXEC to the backend and store the data on a TEMPDB table, then the second step, instead of using the SCAN and SQLEXEC command for each one of the records on the cursor to insert it to the back end i use a BULK insert using a INSERT INTO theTable SELECT * FROM #TempTable this would improve the speed a lot

what do you think? am i right? or like we say in my country , I am so lost as a Dog in a 4th of July Parade?