Where do you post questions about XBASIC?

[BozMan]

I am sorry if this is in the wrong forum section but it seems that the various versions of A5 are covered but not XBASIC.

I have written a script that reads a groups transaction table and whilst processing all filtered records creates and updates a snapshot group record table with the top 4 records in each group.

There are many thousands of transactions and the processing is very slow even though the main table is indexed and filtered.

Always Alpha complains at the top of the window that Alpha is not responding, and at first I cancelled out assuming that I had coded the script incorrectly only to discover that the snapshot table was being created OK, just slowly

Is there a way to improve the speed of DBF processing?

[Ted Giles]

Re: Where do you post questions about XBASIC?

Post the question (and you can upload your script) Paul to the Forum of your choice.
As for speed etc, depends on your installation. You might want to consider using calculated fields in the tables. This can work a bit like an OLAP Cube and speeds up process enormously as the calcs are done at data entry time so the user is unaware of the time it takes - which is hardly any time at all.

[BozMan]

Re: Where do you post questions about XBASIC?

dim tblin as P
tblin = table.open("examresults")
tblin.index_primary_put("ByTopPos")
dim tblout as P
tblout = table.open("examsnapshot")
tblout.index_primary_put("ByExamID")
dim qry as P

tblin.fetch_first()

while .not. table.fetch_eof()

if tblin.toppos>4
GOTO Nextrecord
else
if tblin.toppos>1
GOTO Modifyrecord
end if
end if
if tblin.toppos<>1
GOTO Nextrecord
end if
'Create an output record

tblout.enter_begin()

tblout.date = tblin.date

tblout.module = tblin.module

tblout.examid = tblin.examid

tblout.enter_end(.t.)

GOTO Nextrecord

'Modify the output record - BYPASSED at the moment
Modifyrecord:

'tblout.fetch_find(tblout.examid)
'tblout.change_begin()

'changes will go here

'tblout.change_end(.t.)

Nextrecord:

tblin.fetch_next()

end while

tblin.close()
tblout.close()

end

[Ted Giles]

Re: Where do you post questions about XBASIC?

Not here Paul, in the forum which applies to your version of Alpha.
Some users don't look at General Quaetions too often, and they are the ones you want to make suggestions.
Start a new thread.

The options are V12, V11, V10 etc.

[Stan Mathews]

Re: Where do you post questions about XBASIC?

Assuming the whole thread will be moved where it should.....

Paul,

It seems to me you're code is slow because you're not taking full benefit of the indexes. You say the main table is indexed and filtered but don't specify the index expression or filter. I'm not fond of filtered indexes when queries on indexed fields are generally very fast. Assuming you have an index on toppos I would do it this way.

dim tblin as P
tblin = table.open("examresults")
tblin.index_primary_put("ByTopPos")
dim tblout as P
tblout = table.open("examsnapshot")
tblout.index_primary_put("ByExamID")
dim qry as P
tblin.query_create("N","toppos<5")
'rest of code

This should run a query and loop through 4 records. Is that what your filtered index does?

As always, attaching a sample database for diagnosis leads to better answers. There is likely something in your database we're not seeing.

You can garner a bit of speed with

tblin = table.open("examresults",FILE_RW_EXCLUSIVE)
tblout = table.open("examsnapshot",FILE_RW_EXCLUSIVE)

[BozMan]

Re: Where do you post questions about XBASIC?

Thank you Stan, I'll change the code. The college data is personal info and it would take me some time to change names, etc.

I'll post the results of the changes. Thanks again.

[Stan Mathews]

Re: Where do you post questions about XBASIC?

I'm rather lost with the flow you desire. Under what circumstances does the 'Create an output record section need to be executed?

Always Alpha complains at the top of the window that Alpha is not responding

Alpha doesn't waste a lot of time telling you it is doing what it is supposed to do while it is doing what it is supposed to do.

[BozMan]

Re: Where do you post questions about XBASIC?

OK Stan,

The input table has sets of records grouped by unique exam id. These records are per examinee and record amongst many other things personal info and their score in the exam. These scores are ranked within the group of examinees for each unique exam id - first to last.
The input table is sorted (indexed) by rank, so all records ranked 1 will be processed first and for each of them I create a unique exam id record. Subsequent records ranked 2, 3 and 4 will be updated to the related unique exam id record. So there are in effect potentially four records on each snapshot record (depending on how many students sat the exam). The purpose of the snapshot file is to provide statistics on the top range of scores for each exam.
I have not added the updates to the script as I wanted to prove the accuracy of the process first. Unfortunately the improvement you suggested did not make a difference, so I can only assume my original code is flawed somewhere.

BTW I think I misled you about filtered - I do not have a filter on the index, I meant the if statements filtering withing the script.

[Stan Mathews]

Re: Where do you post questions about XBASIC?

So the pertinent fields are examid, examinee, and toppos.

For each examid you want to find the four highest ranked (toppos), highest being toppos 1 to 4. You want to create records (for the top 4 examinees) in the output table for the examid if it is not currently there. If the examid already exists, modify the existing records with the new data. (Probably easier just to delete any current records for the examid and add new ones?)

It often helps to describe how you would perform the task manually and translate those steps into code.

[Stan Mathews]

Re: Where do you post questions about XBASIC?

Since you're still in the proving the concept stage try this after creating an index for both tables, "IdRank" with the expression

EXAMID +STR(TOPPOS)

dim tblin as P
tblin = table.open("examresults",FILE_RW_EXCLUSIVE)
tblin.index_primary_put("IdRank")
dim tblout as P
tblout = table.open("examsnapshot")
tblout.index_primary_put("IdRank")
tblin.fetch_first()
vexamid = tblin.examid
tblout.batch_begin()
while .not. tblin.fetch_eof()
	while tblin.examid = vexamid .and. .not. tblin.fetch_eof()
		if tblout.fetch_find(tblin.examid+str(tblin.toppos))> 0 .and. tblin.toppos < 5
			tblout.change_begin()
			tblout.examinee = tblin.examinee
			tblout.score = tblin.score
			tblout.change_end(.T.)
		else if tblin.toppos < 5
			tblout.enter_begin()
			tblout.examid = tblin.examid
			tblout.examinee = tblin.examinee
			tblout.toppos = tblin.toppos
			tblout.score = tblin.score
			tblout.enter_end(.T.)
		end if
		tblin.fetch_next()
	end while
	if tblin.examid > vexamid 'next exam
		vexamid = tblin.examid
	end if
end while
tblout.batch_end()
tblin.close()
tblout.close()
end

[BozMan]

Re: Where do you post questions about XBASIC?

Stan, thank you so much, I just caught your reply. Wow, what a friendly gesture to take time to reply in such a detailed way. Unfortunately, I have to go out, so I won't be able to let you know how I get on until tomorrow.

Thanks again :)

[BozMan]

Re: Where do you post questions about XBASIC?

Stan,

I can see your idea but could not understand your:
if tblout.fetch_find(tblin.examid+str(tblin.toppos))> 0 .and. tblin.toppos < 5
statement as toppos is not part of the snapshot tables index

I have my version of your code below which needs to be run twice for some reason, the first time it is run it creates the toppos 1 records and the subsequent run populates the toppos 2 to 4 fields in the fetched records. I have checked the new input table index and it is in the right sequence of exam id + str(toppos)
so I don't understand why the subsequent record fetches do not find a matching examid record in the first run - baffled...

dim tblin as P
tblin = table.open("examresults",FILE_RW_EXCLUSIVE)
tblin.index_primary_put("Byidtopposrank")
dim tblout as P
tblout = table.open("examsnapshot")
tblout.index_primary_put("IdRank")
tblin.fetch_first()
vexamid = tblin.examid
tblout.batch_begin()
while .not. tblin.fetch_eof()
while tblin.examid = vexamid .and. .not. tblin.fetch_eof()
if tblout.fetch_find(tblin.examid)
tblout.change_begin()
if tblin.toppos=2
tblout.bexaminee = tblin.examinee
tblout.bmark = tblin.mark
if tblin.funding="S"
tblout.scholarship = 2
end if
end if
if tblin.toppos=3
tblout.cexaminee = tblin.examinee
tblout.cmark = tblin.mark
if tblin.funding="S"
tblout.scholarship = 3
end if
end if
if tblin.toppos=4
tblout.dexaminee = tblin.examinee
tblout.dmark = tblin.mark
if tblin.funding="S"
tblout.scholarship = 4
end if
end if
tblout.change_end(.T.)
else if tblin.toppos = 1
tblout.enter_begin()
tblout.edate = tblin.examdate
tblout.examid = tblin.examid
tblout.module = tblin.module
tblout.aexaminee = tblin.examinee
tblout.amark = tblin.mark
if tblin.funding="S"
tblout.scholarship = 1
end if
tblout.enter_end(.T.)
end if
tblin.fetch_next()
end while
if tblin.examid > vexamid 'next exam
vexamid = tblin.examid
end if
end while
tblout.batch_end()
tblin.close()
tblout.close()
end

[BozMan]

Re: Where do you post questions about XBASIC?

By the way, filtering the input table index to only include toppos 1 to 4 records and even running the script twice, it is now lightening fast :)

[Tom Cone Jr]

Re: Where do you post questions about XBASIC?

Paul,

Welcome to the message board.

Two tips for you.

1) questions about an xbasic script will get the best relevant exposure if you post them to the desktop forum corresponding to the version of Alpha Five that you are using.

2) as you've discovered the message board left justifies new lines, making it very difficult to read lengthy scripts. If you surround your script with CODE tags the message board will preserve your formatting ( indents, etc.). This is easily done by beginning your post, then choosing the "Go Advanced" button to get to a more complete editor. From there enter your script with indents, then select all the script and click the CODE icon on the editor toolbar. This will encapsulate your script in the desired CODE tags.

[Stan Mathews]

Re: Where do you post questions about XBASIC?

toppos is not part of the snapshot tables index

try this after creating an index for both tables, "IdRank" with the expression

EXAMID +STR(TOPPOS)