How can I speed this up?

[StevenMcLean]

How can I speed this up?

'Set up instance variables

DIM i as n
for i = 1 to 25
	
	'Open the table that you want to update...
	dim tbl as p 
	tbl = table.open("directory",FILE_RW_SHARED)
	'Select a random record to check out
	query.filter = "random_records(1,\"(Confirmedid = 1  )  .and. (Checkout = .f.  )\")"
	query.order = ""
	query.options = ""
	
	DIM itbl as p 
	itbl = tbl.query_create()
	DIM recs as n 
	recs = itbl.records_get()   'get the number of records found by the query
	if recs = 0 then 
		ui_msg_box("Error","Record to update not found. Could not find record that matches filter expression: '" + query.filter+"'.",UI_STOP_SYMBOL)
		tbl.close()
		end 
	else if recs > 1 then 
		ui_msg_box("Error","More than one record was found that matches the filter expression. No records were updated.",UI_STOP_SYMBOL)
		tbl.close()
		end 
	end if
	
	if tbl.is_record_locked() = .t. then 
		ui_msg_box("Error","Cannot edit record because it is locked by another user, or in another session.",UI_STOP_SYMBOL)
		tbl.close()
		end 
	end if 
	
	tbl.change_begin()
	tbl.CHECKOUT = .T.
	tbl.CHECKOUTDATE = date()
	tbl.CHECKOUTBY = User_Name()
	tbl.change_end(.t.)
next
	
tbl.close()

Thanks

[jaryder]

Re: How can I speed this up?

Steven,

The issue I see is that you are opening the table 25 times inside the for loop and closing the table only once outside the for loop. Need to move your open statement to before the for loop begins. Hope this helps.

[Steve Workings]

Re: How can I speed this up?

I'm not sure why you're doing what you're doing. But,

The main reason this takes so long is because it has to query the table 25 times.

Instead, I'd maybe look for a way to query once, then find 25 random records within that query.

Or, not query at all, and find 25 records using a randomizing routine.

Edit:
Just saw Jeff's response. How many records are in that table?

[StevenMcLean]

Re: How can I speed this up?

Hi Steve, what I an trying to do is select 25 random records and set a logical field (flag) to True

Steve

[jaryder]

Re: How can I speed this up?

Steven,

It did not even sink in that that the query was running 25 times also. Saw the first issue and stopped. I think Steve is on the right track for you. Set up your query using ramdom_records like in your example but do it once and request 25 records. Then loop through the resultant queried records and do your update.

[G Gabriel]

Re: How can I speed this up?

I read your question..
I read the first line of your script..
And didn't read anything beyond that..

Any time you use a For..Next loop, it will be SLOW.

Regardless of what the rest of the script does, if you could use any alternative (and most of the time there is one) to For Next..by all means use it.

Later..
Quick read of the script:
You opened the table 25 times, and closed it once!
There is 25 instances of the table hanging out there.
Open the table, once, before the For next loop.

Again..
You queried the table 25 times!
I am in the middle of a lot of business transactions right now and I might be reading this way too quick, BUT couple important notes:
If your intention is to get a 25 random records from the first table, you may not. Random is not random. It follows a specific "random" sequence. And if your intention is to have 25 random and unique records, you may not. You might stumble on the same record more than once.

Secondly, If the second table has one record, it will be overwritten 25 times..You might as well just write it once with the last loop.

I hope I am not confusing things with this quick response, but I think I got it right.

[Stan Mathews]

Re: How can I speed this up?

It won't make much difference when you are working with 1-25 records but you should be aware that wrapping change or enter code within batch_begin() batch_end() will also speed things up.

tbl.batch_begin()
        tbl.change_begin()
	tbl.CHECKOUT = .T.
	tbl.CHECKOUTDATE = date()
	tbl.CHECKOUTBY = User_Name()
	tbl.change_end(.t.)
tbl.batch_end()

[Garry Flanigan]

Re: How can I speed this up?

You say what you want to do is:
"... select 25 random records and set a logical field (flag) to True..", why not:
open the table, select 25 random records then update them. If concerned about some records being locked, select 30 or whatever records and count the successful updates and stop when 25 are updated.

Below is code written but not debugged or checked for syntax so I hope it at least conveys what I would do:

'Open the table that you want to update...
dim tbl as p
tbl = table.open("directory",FILE_RW_SHARED)
'Select a random record to check out
query.filter = "random_records(25,\"(Confirmedid = 1 ) .and. (Checkout = .f. )\")"
query.order = ""
query.options = ""

DIM itbl as p
itbl = tbl.query_create()
DIM recs as n
recs = itbl.records_get() 'get the number of records found by the query
if recs < 25 then
ui_msg_box("Error","Insufficient records to reset, no changes made",UI_STOP_SYMBOL)
tbl.close()
end
end if

tbl.fetch_first()
tbl.batch_begin()
while .not. tbl.fetch_eof()
if tbl.is_record_locked() = .t. then
ui_msg_box("Error","Cannot edit record because it is locked by another user, or in another session.",UI_STOP_SYMBOL)
else
tbl.change_begin()
tbl.CHECKOUT = .T.
tbl.CHECKOUTDATE = date()
tbl.CHECKOUTBY = User_Name()
tbl.change_end(.t.)
end if
tbl.fetch_next()
end while
tbl.batch_end()
tbl.close()

[StevenMcLean]

Re: How can I speed this up?

Gary, Thanks this is a huge improvement.

G. Gabriel, you expressed concern as to the potential for the same record to be selected more than once. The "checked out" records are further processed and marked "confirmed" The random selection excludes the "confirmed" records. Theoretically this should ensure the random integrity of the selection.

Stan, the batch begin / end made a substantial difference on 100/200 records. I could not find this in the help file. Is there any documentation on this?

Thanks for the help!

[Stan Mathews]

Re: How can I speed this up?

<tbl>.batch_begin()

[StevenMcLean]

Re: How can I speed this up?

Stan, if I search the help I can find it, but not the index? I wonder what else is not in the index.

Is there any good "Language Reference" text available.

[Stan Mathews]

Re: How can I speed this up?

You can find it in the index with <tbl>.batch_begin() or plain .batch_begin(), <tbl> being the commonly used designation for a table pointer variable.

Check the Contents section for the language references.

[MikeC]

Re: How can I speed this up?

Steven (and others I imagine),

When I am searching for functions or methods I almost always use the help file index and usually can find whatever in a matter of seconds or at most minutes...I do not always find ALL the various functions that can be used but usually enough to do what I need to.

When searching for a function try this way....

first with the name (even if you do not know if such a function exists--try various names then).

Then do the same but have a period in front of it....like Stan mentioned.
Then try an asterisk in front
Then try a5.name
Then try a5_name

Then go to the various listings of functions for the field types...such as type in character and then scroll down to functions...and start looking through them....actually goes quite fast as these listings are very organized.

There are others also but most times if the above do not find what you are looking for using the index after a few minutes, move onto the Contents or here on the messageboard.

The messageboard has a different method for searching but is just a variation of the index search really.

FWIW really---the above is what I have developed, in part, to find whatever more easily...should be of use----but then have said it many times and still users do not seem to pick up on it. I swear, if I could teach users how to search the help/messageboard and a few other basics, I think the learning curve for them would be drastically reduced.