Dear all,

We have a system developed in VFP 3.0a. Weeks after weeks, there are
problems reported from the users about the slowness of the system.

The system works in a networked environment. The win95 client terminals, which are on the desks of each users, hold a copy of executible. While the database are held in a windows 95 platform.

In fact, there is no any DBMS to manage the requests from each client and the data tables. Each client just connects to the server machine, and makes it as a network driver. When developing the system, we just added the data in the networked driver into project manager.

Frequently, our customers report that the speed of the system have
become very slow. We noticed that sometimes a transaction could took
up to around 15 minutes. (But there was once, among the days of being
very slow, a transaction turned out take around 20 seconds. Surprisingly!)However, it took around 20 seconds for a transaction if the executible and database are both placed on a local machine.

For me, this seems that the network are the main reason for the slowness. On the contrary, copying a big file from the server to a client terminal is quite speedy. This seems conflict to my assumption.

I am wondering what would be the reasons for the slowness of the system? Could anyone suggest me some? Also, how can we speed up the execution of the system?

We have considered ways to speed up the execution. One way we could take is to put both executible and the database on the same server. This way, we would execute the program from terminals by a shortcut, which points to the program through neighborhood. However, speed was not improved.

Again, I attach part of the program, and I wish someone could give me
suggestions on the coding.

Any suggestion would be greatly appreciated, and many thanks in advance!


if messagebox("½T©w«ü¬£¦¹¶µ¤u§@¶Ü?",1,"¬£¤u½T©w")=1
newabsanaid=get_new_abs_ana_id()
newanaid=get_new_ana_id(ins_idron) && ¦¹¬q¤D¬°¦¹¦¸¬£¤u¨ú±o¬£¤u³æ¸¹
wait windows newanaid nowait
select count(*) INTO ARRAY ASSNM from spitem where ins_id=ins_idron and pre_stat='5' and ana_ass=1 && GROUP BY INS_ID &&­pºâ¥X¨ì©³¦³¦h¤Ö¼Ë«~³Q¬£¤u
ASSNUM=ASSNM
TABLE_OPENED=OPEN_ITEM_TABLES(ins_idron) && for exporting items

IF TABLE_OPENED=.T.
=CURSORSETPROP('BUFFERING',5,'PAGET')
=CURSORSETPROP('BUFFERING',5,'ITEMT')
=CURSORSETPROP('BUFFERING',5,'MAINT')
=CURSORSETPROP('BUFFERING',5,'SPITEM')
=CURSORSETPROP('BUFFERING',5,'ANAID_SA')
=CURSORSETPROP('BUFFERING',5,'ANA_ASS')
=CURSORSETPROP('BUFFERING',5,'PAPER')

** This is for controlling the whole process, preventing updating only part of what is supposed to!!!
BEGIN TRANSACTION

insert into ana_ass (ana_id, ana_id_abs,EMP_ID, ASS_DAY, ins_id, QC_MEMO);
values (newanaid,newabsanaid,ThisfORM.Text1.Value,THISFORM.Text2.Value, ins_idron, THISFORM.Edit1.Value)

select spitem
set filter to ins_id=ins_idron and pre_stat="5" and ana_ass=1
go top
k=0 && K¥Î¥H°O¿ý¨ì©³¦³¦h¤Öµ§°O¿ý³Q·s¼W
PAGSUM=0 && USED FOR INDICATING THE TOTAL PAGE NUMBER FOR A SPECIFIC ASSIGNMENT
do while !eof()
DO CASE
CASE INLIST(ins_idron,"A002","A003","A004","A005","A006","A007","A008","A009","A010","A012")

k=k+1
pag=ceiling(k/12)
PAGSUM=IIF(PAGSUM if mod(k,4)=0 and spitem.ass_id<>tmpassid
insert into itemt (ana_id,ana_id_abs,sampcode,pag_no) values (newanaid,tmpassid,' ',ceiling(k/12))
k=k+1
endif
insert into itemt (ana_id,ana_id_abs,sampcode,pag_no) values (newanaid,spitem.ass_id,spitem.sampnum,ceiling(k/12))
tmpassid=spitem.ass_id


CASE INLIST(ins_idron,"B010","B011")
FOR M=1 TO 2
k=k+1
pag=ceiling(k/20)
PAGSUM=IIF(PAGSUM insert into itemt (ana_id,PAG_NO,sampcode,SMP_ORDER) values (newanaid,PAG,spitem.sampnum,M)
NEXT

CASE INLIST(ins_idron,"B019")
FOR M=1 TO 2
k=k+1
pag=ceiling(k/20)
PAGSUM=IIF(PAGSUM insert into itemt (ana_id,PAG_NO,sampcode) values (newanaid,PAG,spitem.sampnum)
NEXT

CASE INS_IDRON="B018"
FOR M=1 TO 3
K=K+1
PAG=CEILING(K/30)
INSERT INTO ITEMT (ANA_ID,SAMPCODE,PAG_NO,ITEM_NO) VALUES (NEWANAID,SPITEM.SAMPNUM,PAG,k)
PAGSUM=IIF(PAGSUM NEXT
OTHERWISE
k=k+1
pag=ceiling(k/10)
PAGSUM=IIF(PAGSUM insert into itemt (ana_id,sampcode,pag_no) values (newanaid,spitem.sampnum,PAG)
ENDCASE
insert into anaid_sa (ana_id,sampcode) values (ana_ass.ana_id,spitem.sampnum)
select spitem
replace pre_stat with "6"
skip
enddo

do case && §ó·sªÅ­«¬£¤u«á©Ò¥Î±¼ªº©M©Òµ²¦sªº¼Æ¥Ø
case ins_idron='B011'
update paper set now_qty=now_qty-assnum where ins_id='Y002'
case ins_idron='B010'
update paper set now_qty=now_qty-assnum where ins_id='Y001'
case ins_idron='B013'
update paper set now_qty=now_qty-assnum where ins_id='Y003'
case ins_idron='B015'
update paper set now_qty=now_qty-assnum where ins_id='Y006'
case ins_idron='A001'
update paper set now_qty=now_qty-assnum where ins_id='Y004'
case ins_idron='A009'
update paper set now_qty=now_qty-assnum where ins_id='Y005'
endcase


** APPEND TABLES *MAIN AND *PAGE
SELECT MAINT
APPEND BLANK
REPLACE ANA_ID WITH NEWANAID, ANA_EMP WITH THISFORM.TEXT1.VALUE,ana_dayb with date()+1, PAG_SUM WITH PAGSUM

** APPEND TABLES *PAGE
SELECT PAGET
FOR I=1 TO PAGSUM
APPEND BLANK
REPLACE ANA_ID WITH NEWANAID, PAG_NO WITH I
NEXT I

SELECT MAINT
MSUCCESS=TABLEUPDATE(.T.)
SELECT PAGET
PSUCCESS=TABLEUPDATE(.T.)
SELECT ITEMT
ISUCCESS=TABLEUPDATE(.T.)
SELECT ANAID_SA
A1SUCCESS=TABLEUPDATE(.T.)
SELECT ANA_ASS
A2SUCCESS=TABLEUPDATE(.T.)
SELECT SPITEM
SSUCCESS=TABLEUPDATE(.T.)
SELECT PAPER
P2SUCCESS=TABLEUPDATE(.T.)

IF MSUCCESS=.F. OR PSUCCESS=.F. OR ISUCCESS=.F. OR A1SUCCESS=.F. OR A2SUCCESS=.F. OR SSUCCESS=.F. OR P2SUCCESS=.F.
ROLLBACK
ELSE
END TRANSACTION
ENDIF

=CURSORSETPROP('BUFFERING',1,'PAGET')
=CURSORSETPROP('BUFFERING',1,'ITEMT')
=CURSORSETPROP('BUFFERING',1,'MAINT')
=CURSORSETPROP('BUFFERING',1,'SPITEM')
=CURSORSETPROP('BUFFERING',1,'ANAID_SA')
=CURSORSETPROP('BUFFERING',1,'ANA_ASS')
=CURSORSETPROP('BUFFERING',1,'PAPER')

thisform.lockscreen=.T.

THISFORM.grid3.Init()
THISFORM.Refresh()

Thisform.lockscreen=.F.
ELSE && WHEN NO TABLE ARE OPENED.
=MESSAGEBOX("¦¹¶µ¥Ø¤£¾A¥Î©ó¦¹¬£¤u")
ENDIF
ENDIF && IF MESSAGEBOX()=.T.

******************************************************************************************************************************************
****** END OF MAIN PROCEDURE ***** END OF MAIN PROCEDURE***** END OF MAIN PROCEDURE**********************
******************************************************************************************************************************************