×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need 4 Speed

Need 4 Speed

Need 4 Speed

(OP)
Sir, I have following codes

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
 

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

Is it possible to make codes more faster?

Please

RE: Need 4 Speed

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))

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 Smileing

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.

RE: Need 4 Speed

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 Smileing

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.

RE: Need 4 Speed

For a start, you can eliminate your second line of code (index on acc_code to myindex). 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close