I am working on a function that optionally deletes and adds records from one table to another. Running it from the IW took 27 secs to delete & pack 87 records and then add about 120. Does this seem within range? Adding tbl.batch_begin / end only shaved off about 4 secs. I haven't tried the function from a layout where the source table is already open - can I expect that might make a difference? Even 23 secs seems too long for so few records...
Here's the script so far:
tbl_data.jpg
Here's the script so far:
Code:
'Date Created: 12-Nov-2015 08:22:26 PM 'Last Updated: 19-Nov-2015 02:59:36 PM 'Created By : Robin Q 'Updated By : Robin Q FUNCTION Sched_LU AS C (vdate as d = {}) 'DESCRIPTION: Populates a temp table with m_sched to get fileno for machno & shift in production template dim tbl as p dim tmp as p dim idx as p dim i as n = 1 dim recs as n dim list as c = "" dim msg as c = "" dim ordr as c = "" dim tblname as c dim x as c dim vfile as c dim vmach as c dim vshift as c dim fltr as c t1 = toseconds(time()) '***NOTE: could limit lookup records to current prodate only? ' zap_it("tmpsched") ' xbasic_wait_for_idle() GETDATE: if vdate = {} then vdate = ctod(popup.calendar(dtoc(date()) ) ) if vdate = {} then 'user cancelled goto ENDIT end if end if fltr = convert_expression("schedate = "+s_quote(var->vdate),"V") tbl = table.open("m_sched") tmp = table.open("tmpsched") 'Check that m_sched records exist with machines assigned if .not. exist(cdate(vdate),"m_sched","Mach1_Blnk") msg = "No Production Schedule was found for this date"+crlf() msg = msg+"or machines have not been assigned to employees."+crlf(2) msg = msg+"Please check the Prod Schedule for this date and correct." msgbox("Add Shift Schedule: "+dtoc(vdate),msg,16) tblname = "m_sched" ordr = "cdate(schedate)+shift+mach1" goto CLOSEIT end if 'Prompt for user selection if schedule date already exists in tmpsched if exist(vdate,"tmpsched","SCHEDATE") then msg = "A schedule already exists for this date."+crlf() msg = msg+"Would you like to update the existing schedule"+crlf() msg = msg+"or select another date to add?"+crlf(2) msg = msg+"Select YES to select another date and NO to update." response = msgbox("Create Shift Schedule Lookup",msg,547) if response = 2 then 'user cancelled tblname = "tmpsched" ordr = "cdate(schedate)+machno" goto CLOSEIT elseif response = 6 then vdate = {} goto GETDATE else 'user selected NO goto UPDATEIT end if end if ADDIT: ' t1 = toseconds(time()) 'm_sched has matching records with machines assigned idx = tbl.query_create("NT",fltr,"file_no") recs = idx.records_get() response = msgbox("Confirm m_sched filter","records found: "+str(recs),33) if response = 2 then tblname = "m_sched" ordr = "cdate(schedate)+shift+mach1" goto CLOSEIT end if tbl.batch_begin() PleaseWait(.t.,"While the records are being added") tbl.fetch_first() on error goto CLOSEIT while .not. tbl.fetch_eof() i=1 for i = 1 to 6 x = alltrim(str(i)) vfile = tbl.file_no vshift = tbl.shift vmach = EVAL("tbl.mach"+x) if vmach <> "" then tmp.enter_begin() tmp.schedate = vdate tmp.machno = vmach tmp.shift = vshift tmp.file_no = vfile tmp.enter_end(.t.) end if next i tbl.fetch_next() end while tbl.batch_end() PleaseWait() ' t2 = toseconds(time()) tblname = "tmpsched" ordr = "cdate(schedate)+machno" goto CLOSEIT UPDATEIT: 'delete the tmpsched records then re add - easier than changing! tmp.delete_range(fltr) recs = tmp.records_deleted() xbasic_wait_for_idle(.15) tmp.pack() xbasic_wait_for_idle(.15) msgbox("Deleted Records","Removed deleted records: "+str(recs),0) goto ADDIT CLOSEIT: tbl.close() tmp.close() response = msgbox("View Records","Would you like to see the table?",292) if response = 7 then goto ENDIT else A5_OPEN_DEFAULT_BROWSE(tblname,"",ordr) end if ENDIT: t2 = toseconds(time()) Tln("Sched_LU Seconds elapsed - ADDIT",str(t2-t1)) Sched_LU=dtoc(vdate) end END FUNCTION
Comment