Get a Jumpstart with our Sample Apps
Results 1 to 11 of 11

Thread: How long is too long for a script to run?

  1. #1
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,211

    Default How long is too long for a script to run?

    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:

    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
    tbl_data.jpg
    Last edited by MoGrace; 11-19-2015 at 10:13 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  2. #2
    Member
    Real Name
    Jon Moody
    Join Date
    Sep 2010
    Location
    Brackley, UK
    Posts
    222

    Default Re: How long is too long for a script to run?

    Robin,

    My first thought would be to move some of the code above the for/next loop. vfile and vshift only need to be assigned once per record.
    Alternatively, just apply the input values directly to the temp table - tmp.shift = tbl.shift and tmp.file_no = tbl.file_no.

    Might also be worth experimenting by removing the table pack to see if this is slowing things down.

  3. #3
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,211

    Default Re: How long is too long for a script to run?

    Hi Jon,
    That's a good point about not mapping the fields to variables. I was initially going to use enter_record() instead of enter_begin(). Also I can pack the table later, it just seemed good to do it with the delete. I'll try it and let you know.

    Edit:
    I added the batch_begin() back, tried without the pack() then decided to keep it but removed the 2nd xbasic_wait_for_idle() and mapped the fields to fields and got the script down to 14 secs - which seems tolerable. If I remove 2 of the confirmation prompts to the trace window instead, it should be even better.
    Thanks!

    Addendum: Changing the 2 confirmation prompts brought the time down to 8 secs for deleting and re-adding 87 records. I probably don't even need the PleaseWait() now!
    Last edited by MoGrace; 11-20-2015 at 01:13 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  4. #4
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,383

    Default Re: How long is too long for a script to run?

    Can you make a set that defines the relationship and then loop through the set?
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,211

    Default Re: How long is too long for a script to run?

    Hi Al,
    I don't understand how a set would help if the records in the tmp table don't exist yet.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  6. #6
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,383

    Default Re: How long is too long for a script to run?

    Quote Originally Posted by MoGrace View Post
    Hi Al,
    I don't understand how a set would help if the records in the tmp table don't exist yet.
    It's difficult to follow the process. A lot of looping going on.

    I try to minimize looping of possible.

    Also I use operations when I can to get better performance than xbasic read writes.

    Operations use C++ to run and are much faster if they can do what you want.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  7. #7
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,830

    Default Re: How long is too long for a script to run?

    Using operations on a 950,000 record table
    can delete 200 records and add 400 more from another table in just a very few seconds like Al alluded to.

    Alpha is built on c++ and some of their stuff is built using c++ to make some things just plain faster by many times.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  8. #8
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,211

    Default Re: How long is too long for a script to run?

    Re using an operation: would it matter timewise if I add the xbasic for the op to the script or run as a saved op? For the xbasic - would my variables have to be dimmed as shared instead of local?
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  9. #9
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,383

    Default Re: How long is too long for a script to run?

    Quote Originally Posted by MoGrace View Post
    Re using an operation: would it matter timewise if I add the xbasic for the op to the script or run as a saved op? For the xbasic - would my variables have to be dimmed as shared instead of local?
    Probably more flexible to put the Xbasic in the script. Local or shared should be ok with local.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  10. #10
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,830

    Default Re: How long is too long for a script to run?

    Robin,
    I create a lot of operations and once they are like I need them, I view the xbasic for each to be used in a udf. I generally use a script to run the udf's in the order desired. Sometimes the script does other things as well.
    Hope that helps you. Appending 68 blank tables with up to 950,000 records each from tables on a server takes about 1.07 minutes and that includes a number of other operations at the same time.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  11. #11
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,211

    Default Re: How long is too long for a script to run?

    Writing the append in Xbasic does work better when variables are involved and it runs very fast now.

    Now all I need to do to make this routine perfect is anticipate the user errors that might occur. Mostly from entering the wrong data from either selecting the wrong date in the beginning or entering a wrong schedule altogether. Or perhaps not running the routine in the right order. This is no doubt the hardest part, so I am trying to break my own script to see what might go wrong...and figure out a recovery.

    My goal is to make the data entry as automatic as possible since the data is gathered from manual records that themselves may be wrong. The user has to be able to find those errors easily and make corrections else the analytical reports will be useless.

    However 'easy' to me is probably not so easy to my user...whose main 'computer' experience is from using a tablet or a smartphone!
    Last edited by MoGrace; 11-21-2015 at 04:09 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

Similar Threads

  1. Dealing with Long URLs/long words/wrapping.
    By carlbsmith in forum Application Server Version 11 - Web/Browser Applications
    Replies: 4
    Last Post: 04-17-2013, 09:22 AM
  2. You know you've been at this too long when ...
    By 2ninerniner2 in forum General Questions
    Replies: 3
    Last Post: 02-28-2013, 04:16 PM
  3. Expression is too long??
    By Rich Hartnett in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 09-01-2011, 09:41 AM
  4. Long Searches
    By njguy in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 08-18-2010, 06:37 PM
  5. long documents
    By SmartMLMer in forum Alpha Five Version 9 - Desktop Applications
    Replies: 8
    Last Post: 09-27-2009, 02:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •