What is wrong with this Code?

[JetLi]

I have a code which summarizes aging of receivables, it is working very very very fast on 3 records, I thought it is fine and proceded to demonstrate the power of alpha five to a potential client, when i demonstrated the application, the client is amazed and agreed to but the app, after a few days, the client is withdrawing the contract because it took forever for the code to display the aging of receivables with just 20 records. the code goes this way, it is working fine, no errors, It was a slap to my face when the client decided not to continue to buy the app.the code goes this way:
option strict
dim invoice as p
dim aged as p
dim age as n
dim inv_date as d
dim query.order as c
dim query.description as c
dim query.options as c
dim query.filter as c

aged = table.open("aged_inv",FILE_RW_EXCLUSIVE)
aged.zap(.t.)
aged.close()
aged = table.open("aged_inv",FILE_RW_SHARED)
invoice = table.open("invoiceflow",FILE_RO_SHARED)
query.description="Balances greater than 0"
query.order=""
query.options=""
query.filter="Inv_Bal > 0.01"
invoice.query_create()

invoice.fetch_first()
while .not. invoice.fetch_eof()
aged.enter_begin()
age = date() - invoice.due_date
select
case age >=90
aged.ninety = invoice.inv_bal
case age >=60
aged.sixty = invoice.inv_bal
case age >=30
aged.thirty = invoice.inv_bal
case else
aged.current = invoice.inv_bal
end select
aged.inv_no = invoice.refnum
aged.custid = invoice.custid
aged.due_date=invoice.due_date
aged.enter_end(.t.)
invoice.fetch_next()
end while
invoice.close()
aged.close()
preview_report("aging_report")
end

[Stan Mathews]

Re: What is wrong with this Code?

Sine you are opening, emptying, then filling the aged_inv table there is no need to close and reopen it as shared. The script will run faster if left open exclusive.

aged.close()
aged = table.open("aged_inv",FILE_RW_SHARED)

Also check the documentation for .batch_begin() and .batch_end().

[JetLi]

Re: What is wrong with this Code?

I see, Thank you so much Stan, I'll try and update this thread for results.

[Tom Henkel]

Re: What is wrong with this Code?

Besides what Stan said, you need to see where it is hanging up. the code to build the file should take, literally, seconds (if that) for 20 records. How is the report set up, and is that where it is hanging up?

[JetLi]

Re: What is wrong with this Code?

I tried to remove the close and open and set it as file exclusive but just the same it is responding maybe 20 sec, i tried to add more records like a hundred but at least it is still the same as 20 sec. could that be the fastest way for the code to execute? please experts, do you more suggestions?Thanks

[Tom Cone Jr]

Re: What is wrong with this Code?

Suggestions:

a) show us your latest code, but use code tags to preserve indents, please. (Haven't we talked about that before?)

b) try "commenting out" the report preview command. Replace it with a msg box so you'll know when the script is finished.
This will help you decide if the script is spending its time building the necessary records, or if its spending its time getting the report ready.

c) do you have a permanent index on the Inv_Bal field? If not, prepare one, then read through the help file discussion of "lightening query optimizationi".

[JetLi]

Re: What is wrong with this Code?

option strict
dim invoice as p
dim aged as p
dim age as n
dim inv_date as d
dim query.order as c
dim query.description as c
dim query.options as c
Still the same time spent in just at least a hundred records and not 5 records
the code goes this way based on suggestions of alpha experts,I also created an index on inv_bal, I cannot upload the database as it contains data and it is not accepted in the maximum MB capacity of uplads

dim query.filter as c

aged = table.open("aged_inv",FILE_RW_EXCLUSIVE)
aged.zap(.t.)
'aged.close()
'aged = table.open("aged_inv",FILE_RW_SHARED)
'invoice = table.open("invoiceflow",FILE_RO_SHARED)
invoice = table.open("invoiceflow",FILE_RW_EXCLUSIVE)
query.description="Balances greater than 0"
query.order=""
query.options=""
query.filter="Inv_Bal > 0.01"
invoice.query_create()

invoice.fetch_first()
while .not. invoice.fetch_eof()
aged.enter_begin()
age = date() - invoice.due_date
select
case age >=90
aged.ninety = invoice.inv_bal
case age >=60
aged.sixty = invoice.inv_bal
case age >=30
aged.thirty = invoice.inv_bal
case else
aged.current = invoice.inv_bal
end select
aged.inv_no = invoice.refnum
aged.custid = invoice.custid
aged.due_date=invoice.due_date
aged.datetran=invoice.datetran
aged.enter_end(.t.)
invoice.fetch_next()
end while
invoice.close()
aged.close()
ui_msg_box("Update","Update Finished",UI_OK)
'preview_report("aging_report")
end

[JetLi]

Re: What is wrong with this Code?

option strict
dim invoice as p
dim aged as p
dim age as n
dim inv_date as d
dim query.order as c
dim query.description as c
dim query.options as c
Still the same time spent in just at least a hundred records and not 5 records
the code goes this way based on suggestions of alpha experts,I also created an index on inv_bal, I cannot upload the database as it contains data and it is not accepted in the maximum MB capacity of uplads

dim query.filter as c

aged = table.open("aged_inv",FILE_RW_EXCLUSIVE)
aged.zap(.t.)
'aged.close()
'aged = table.open("aged_inv",FILE_RW_SHARED)
'invoice = table.open("invoiceflow",FILE_RO_SHARED)
invoice = table.open("invoiceflow",FILE_RW_EXCLUSIVE)
query.description="Balances greater than 0"
query.order=""
query.options=""
query.filter="Inv_Bal > 0.01"
invoice.query_create()

invoice.fetch_first()
while .not. invoice.fetch_eof()
aged.enter_begin()
age = date() - invoice.due_date
select
case age >=90
aged.ninety = invoice.inv_bal
case age >=60
aged.sixty = invoice.inv_bal
case age >=30
aged.thirty = invoice.inv_bal
case else
aged.current = invoice.inv_bal
end select
aged.inv_no = invoice.refnum
aged.custid = invoice.custid
aged.due_date=invoice.due_date
aged.datetran=invoice.datetran
aged.enter_end(.t.)
invoice.fetch_next()
end while
invoice.close()
aged.close()
ui_msg_box("Update","Update Finished",UI_OK)
'preview_report("aging_report")
end

[Tom Cone Jr]

Re: What is wrong with this Code?

Zip the two tables, their indexes, and the FPT files, if any. Attach the zip here.

You have not deployed batch_begin and batch_end as Stan recommended previously.

You have not specified options in the query to force Alpha to use the permanent index.

Maybe we can help you if you supply the data.

-- tom

ps You also have not preserved indents in your code. Your script is made more difficult to understand because everything is left justified. Pls surround your code with CODE taqs. If you don't know hos to do that let us know. ( Though I feel certain this has been covered before. )

[martinwcole]

Re: What is wrong with this Code?

you didn't add the batch begin and end

[JetLi]

Re: What is wrong with this Code?

File attached, The execution is improved but don't know if the records will be 1 thousand, as of now i have put 217 records and it takes 20 seconds to execute.

[Ray in Capetown]

Re: What is wrong with this Code?

I get an error
Invoice flow - defined record is too long
I cant test your code ..
Just a comment - it may save a bit if the record is not committed after every entry

[Tom Cone Jr]

Re: What is wrong with this Code?

Jet, are your tables encrypted? Can't load or open them here because they're bound to your database.

[Ray in Capetown]

Re: What is wrong with this Code?

Same here Tom, so I disconnnected the two tables and attached to another host DB - which would work for the purpose, but don't.
To Jet - you know the drill, you have put working DBs on this board before, don't make it more difficult. Thanks.
=Ray

[JetLi]

Re: What is wrong with this Code?

So sorry. i forgot to remove the password, I attached another with no password. Thank you