Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Datewise Vouchers Summary
Message
 
To
20/07/2008 20:51:04
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP SP2
Network:
Windows XP
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01332684
Message ID:
01332687
Views:
6
You have a couple of possible problems in your code. Using RECCOUNT() without including NOFILTER in your selects is a potential source of incorrect data as the filterd view will give a RECCOUNT() of the whole table. I also recommend that you rename some things so you are not using VFP keywords for the names of things like tables and fields.

First make sure that all of your FROM tables have the needed indexes on them for optimization, for example crsalp needs index tags on date and vou_type. Try modfying your code as follows;
SELECT comm
fdate1=bdate
ldate1=edate
SELECT master1
ZAP  && zap before index on so thewre are less records to index.
INDEX ON date TAG master11 & Use a structural index file instead of an IDX file

DO WHILE fdate1<=ldate1

	SELECT DISTINCT vou_no FROM crsalp WHERE date=fdate1 AND vou_type='SR' INTO CURSOR vf NOFILTER
	sr1=RECCOUNT("vf")

	SELECT DISTINCT vou_no FROM cashsalp WHERE date=fdate1 AND vou_type='SR' INTO CURSOR fg NOFILTER
	sc1=RECCOUNT("fg")

	SELECT DISTINCT vou_no FROM crprp WHERE date=fdate1 AND vou_type='SR' INTO CURSOR vg NOFILTER
	pr1=RECCOUNT("vg")

	SELECT DISTINCT vou_no FROM cashprp WHERE date=fdate1 AND vou_type='SR' INTO CURSOR wg NOFILTER
	pc1=RECCOUNT("wg")

	SELECT distinct vou_type, vou_no ;
                             FROM vouchers ;
                          WHERE date=fdate1 AND (vou_type = "CR" OR vou_type = "CP" OR vou_type = "JV") ;
                     GROUP BY vou_type, vou_no INTO CURSOR va

	SELECT va
	COUNT FOR vou_type = "CR" TO cr1
	COUNT FOR vou_type = "CP" TO cp1
	COUNT FOR vou_type = "JV" TO jv1

	totals = cr1 + cp1 + jv1 + sr1 + sc1 + pr1 + pc1

	IF totals > 0
		INSERT INTO Master1 (date, open_dr, open_cr, debit, credit, close_dr, close_cr, wt, balance) ;
                                                   VALUES (fdate1, cr1, cp1, jv1, sr1, sc1, pr1, pc1, totals)
	ENDIF
	fdate1 = fdate1+1
ENDDO
I don't know how much faster this will be but it will give you accurate results. The speed will be most affect4ed by the indexes being created.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform