Need 4 Speed

[TariqMehmod]

Sir, I have following codes

[pre]select cr_sum
index on acc_code to myindex
set order to myindex
go top

scan
scatter memvar
lnamt = m.payable
mydate=date
mycode=m.acc_code

vouchersx="crs"+sys(2015)
select date,vou_no,acc_code,balance,vou_type;
from vouchers;
where acc_code=alltrim(m.acc_code) and date=>m.date;
order by date,vou_type,vou_no;
into cursor vouchersx readwrite

select vouchersx
scan for balance > 0 and acc_code=alltrim(m.acc_code) and date=>m.date

do case

case lnamt > nvl(vouchersx.balance,0)
lnamt = lnamt - nvl(vouchersx.balance,0)

replace cr_sum.paid with nvl(cr_sum.paid,0) +nvl(vouchersx.balance,0)
replace vouchers.balance with 0
replace cr_sum.pdate with vouchersx.date

case lnamt =< nvl(vouchersx.balance,0)

replace cr_sum.paid with nvl(cr_sum.paid,0) + nvl(vouchers.balance,0)
replace vouchersx.balance with nvl(vouchersx.balance,0) - lnamt
replace cr_sum.pdate with vouchersx.date

lnamt = 0
endcase

if lnamt = 0
exit
endif

endscan

endscan
[/pre]

Table cr_sum has 7000 records.
The above codes takes 20 Minutes to complete.

Is it possible to make codes more faster?

Please

 

[GriffMG]

The most obvious improvement would probably be have an index on your vouchers table that matched acc_code, but I can't imagine you don't already.

If you had that index, you could skip the processing (extracting or trying to extract) into the vouchersx cursor if there was no match (doing a quick seek (m.acc_code))

SELECT cr_sum
INDEX ON acc_code TO myindex
SET ORDER TO myindex
GO TOP

SCAN
	SCATTER MEMVAR
	lnamt = m.payable
	mydate=DATE
	mycode=m.acc_code

	SELECT Vouchers
	SET ORDER TO AccCodeIndex
	SEEK (TRIM(m.acc_code))
	IF FOUND()
		vouchersx="crs"+SYS(2015)
		SELECT DATE,vou_no,acc_code,balance,vou_type;
			FROM vouchers;
			WHERE acc_code=ALLTRIM(m.acc_code) AND DATE=>m.date;
			ORDER BY DATE,vou_type,vou_no;
			INTO CURSOR vouchersx READWRITE

		SELECT vouchersx
		SCAN FOR balance > 0 AND acc_code=ALLTRIM(m.acc_code) AND DATE=>m.date

			DO CASE

				CASE lnamt > NVL(vouchersx.balance,0)
					lnamt = lnamt - NVL(vouchersx.balance,0)

					REPLACE cr_sum.paid WITH NVL(cr_sum.paid,0) +NVL(vouchersx.balance,0)
					REPLACE vouchers.balance WITH 0
					REPLACE cr_sum.pdate WITH vouchersx.DATE

				CASE lnamt =< NVL(vouchersx.balance,0)

					REPLACE cr_sum.paid WITH NVL(cr_sum.paid,0) + NVL(vouchers.balance,0)
					REPLACE vouchersx.balance WITH NVL(vouchersx.balance,0) - lnamt
					REPLACE cr_sum.pdate WITH vouchersx.DATE

					lnamt = 0
			ENDCASE

			IF lnamt = 0
				EXIT
			ENDIF

		ENDSCAN
	ENDIF
ENDSCAN

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

You could also, perhaps, do away with the SCAN FOR Clause, as it is already performed by your Select.
You might make a difference by using one line replace statements instead of three separate ones (you way is far more readable though.
If your account code index for vouchers also included the date and balance that could make a difference too

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

For a start, you can eliminate your second line of code ([tt]index on acc_code to myindex[/tt]). You only need to create the index once, when you first create your table. After that, it will remain in place, and will get updated automatically, so re-creating it each time you run the code is a waste of time.

Next, you should ensure you have indexes on date, vou_type and vou_no, since your are sorting and filtering on those fields.

Finally, replace each of the three separate REPLACE statements (within each of the CASEs) with a single REPLACE.

I suspect that the whole thing could be rewritten to run faster, but the above three ideas, off the top of my head, should get you started.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads