Help to overcome this problem . Slow processing... 2

[gerry03]

I use two dbf for all types of transactions as given below:

Main.Dbf contains Bill No., Voucher No and Date etc. while I store all types of transaction into another dbf Trans.Dbf. I usually use the following method to assign new voucher no./Billno when a new voucher is selected.


Store 0 to m_Bill_No
store date() to m_Date

Chk_NewBillNo() \\assigns new val to bill no when New voucher is selected

@row, col say "Date " get m_date
@row, col say "Bill No" gey m_Bill_No Valid Chk_Duplicate()

Read




Return



Function Chk_NewBillNo()
*----------------------*
sele Main
set order to 1 \\indexed: str(bill_no,7) + str(V_code,7)

set filt to bill_no = m_Bill_no .And. V_Code = m_V_Code
Go Bott
Store Bill_no to m_Bill_no
set filt to
Return .t.

Function Chk_Duplicate()
*-----------------------*
chk if m_bill_no exist ?

if exists then go for editing/deleting...
.....
.....
.....

return .t.


Now the problem is, when we select a new voucher it takes time to assign new bill no. my database contain appx. 8000 record and they will grow day by day. I know the filter command is very slow but I don't know the alternative methods. Please help how to overcome this problem. Or how can i make my program more efficient.

Thanks for your help

 

[HotEye]

I would suggest you use a counter dbf to hold the current or next number to use.

Counter.dbf:
Bill, n, 7, 0
Invoice, n, 7, 0

then use a function to return the next number and update the field...

function Get_Next(cField)
local nField, nArea:=select()

select 0
use Counter
while !reclock()
end
nField:=fieldpos(cField) && get field number
fieldput(fieldget(nField)+1) && increment by 1
nField:=fieldget(nField) && save new value for return
dbclosearea()
dbselectarea(nArea)
return nField


Or you could have Counter contain multiple records:
Counter.dbf
ID, c, 10, 0
Counter, n, 7, 0
index on ID

function Get_Next(cField)
local nField, nArea:=select()

select 0
use Counter
seek cField
if !found()

while !reclock()
end
nField:=fieldpos(cField) && get field number
fieldput(fieldget(nField)+1) && increment by 1
nField:=fieldget(nField) && save new value for return
dbclosearea()
dbselectarea(nArea)
return nField

 

[HotEye]

I would suggest you use a counter dbf to hold the current or next number to use.

Counter.dbf
ID, c, 10, 0
Counter, n, 7, 0

index on ID

function Get_Next(cID)
local nCount, nArea:=select()

select 0
use Counter
seek cID
if !found()
append blank
replace id with cID
else
while !reclock()
end
end
replace Counter with Counter+1 && increment by 1
nField:=Counter && save new value for return
dbclosearea()
dbselectarea(nArea)
return nField


This will return your next number in a very short time!

Sorry for my last post, pressed TAB to indent the code

Hope this one helps.

 

[Bozz]

Gerry,

Filtering is usually not good. If all that you want is an incremental number then use a .mem file.

Function add_1()
if file("bill_no.mem&quot
restore from bill_no additive
else
m_bill_no := 9999 // a seed to start off with
endif
m_bill_no++ // add 1
save all like m_bill_no to bill_no.mem
return(m_bill_no)

As bill_no is the index key simply Main->(dbgobottom()) and add 1 to the number that you find.

What does:
set filt to bill_no = m_Bill_no .And. V_Code = m_V_Code
where m_Bill_no is zero and m_V_Code is ??? actually do? Would not just going to the bottom of the database do? You are setting the filter to empty bill numbers and then going bottom and I'm not sure what this achieves.

As a general thing, try to use the alias instead of changing area, it's much more readable and saves you remembering where you are when you are using several dbf's. Use seeks or go bottom without filters and things should be faster.




Ian Boys
DTE Systems Ltd