Dear All,

I am sorry to ask this question again.
I have already rewritten the part of code and used SQL statements as suggested.
However, speed is not improved significantly.

I hae traced the execution of the codes, and found it take quite a long time
to refresh the form. I think the main problem occured when the program tries
to show the filtered data onto the grids.

Did anyone encounter this sort of problems before? How did you solve it.
I will be grateful to hear any suggestion about this.

Many thanks for all your help.

Nest regards,
Ron.


>I just scanned your code quickly and it seems to me that given the size of the files that you are talking about ( and I can't tell if the 30,000 records relate to rep_samp) but you are resetting filters several times throughout the code. I don't know what the memory is in your Windows 95 client to your Novell server but my experience is that resetting filters uses a lot of disk grinding if the memory is low. Also, (and this is not a biggy), consolidate your consecutive REPLACE statements into one, such as
>
>REPLACE ITEMA WITH X, ITEMB WITH Y, ITEMC WITH Z....ETC.
>
>On the filters with do loops to update, you might want to consider using a SQL UPDATE statement to increase speed also.
>
>HTH,
>
>Bill
>
>
>
>
>
>>Dear All,
>>
>>I have written a form which consists of a list, 2 grids, and some command buttons. Which is used to
>>1. count the numbers of some inspections, and show them in the list,
>>2. As the inspection item in the list is selected, the sample codes associated with this inspection are shown in the Grid.
>>3. After the user assigns the sample codes to be inspected to a staff, then a command button issue the actions of change the states of the data in the table.
>>
>>At the list init stage, a sql statement is executed to show show the inspect items in the list. the code associated follows,
>>SET DELETED ON
>>SET SAFETY OFF
>>
>>**set step on
>>** ­pºâ©Ò¦³ªº¥i¬£¤u¼Ë«~¼Æ¨Ã±N¨ä¦s©óANA_SUM¤¤(ºâ¥X¨C¤@­ÓÀË´ú¶µ¥Ø¦³¦h¤Ö¼Ë«~¶·­n¬£¤u)
>>public dimension tmp(3,30)
>>
>>select spitem.ins_id, count(sampnum),inspect.Eng_nam;
>>from spitem , inspect ;
>>into array tmp;
>>where spitem.pre_stat="5";
>>and spitem.ins_id=inspect.ins_id;
>>group by spitem.ins_id;
>>order by spitem.ins_id
>>
>>origdim=_tally
>>dimension tmp(origdim+2,3)
>>SELECT count(SAMPNUM) into array num1 from rep_samp where state="1" && HAVE TO BE "µÑ¨ú"ED
>>SELECT count(SAMPNUM) into array num2 from rep_samp where state="3" && HAVE TO BE "®ø¤Æ"ED
>>tmp(origdim+1,1)="PP01"
>>tmp(origdim+1,2)=num1(1)
>>tmp(origdim+1,3)="µÑ¨ú"
>>tmp(origdim+2,1)="PP02"
>>tmp(origdim+2,2)=num2(1)
>>tmp(origdim+2,3)="®ø¤Æ"
>>
>>thisform.list1.rowsource="tmp"
>>
>>
>>2. The interactiveChange event of the list execute the following codes to show the samples codes in two grids,
>>Because the sample codes come from two tables, therefore, one grid one table is arranged. Depending on which inspect item is selected,
>>the related grid is set to visible.
>>public ins_idr
>>store this.value to ins_idron
>>if ins_idron="PP01 "
>> select rep_samp
>> set filter to state="1"
>> go top
>>
>> else
>> if ins_idron="PP02 "
>> select rep_samp
>> set filter to state="3"
>> go top
>> thisform.grid1.visible=.T.
>> else
>> select spitem
>> set filter to ins_id=ins_idron .and. pre_stat="5"
>> go top
>> thisform.grid1.visible=.F.
>> endif
>>endif
>>thisform.refresh()
>>
>>
>>3. the user assign work to staff. Items in the grid with a field set to 1 by a check box to indicate being assigned.
>>And the states of the records with the sample code and inspect item then is changed to a number indicated the situation,
>>******************************************************************************************************************************************
>>****** BEGIN OF MAIN PROCEDURE ***** BEGIN OF MAIN PROCEDURE***** BEGIN OF MAIN PROCEDURE***************
>>******************************************************************************************************************************************
>>**==This block of code is for idea briefing=============== && Ron's modification , 30/5/1997, overall re-writing, focus on structurising
>>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³æ¸¹
>>
>> SELECT ANA_ASS && Appending new assignment to ANA_ASS
>> APPEND BLANK
>> replace ana_id with newanaid
>> replace ana_id_abs with newabsanaid
>> REPLACE EMP_ID WITH THISFORM.Text1.Value
>> REPLACE ASS_DAY WITH THISFORM.Text2.Value
>> replace ins_id with ins_idron
>> REPLACE QC_MEMO WITH THISFORM.Edit1.Value
>>
>> TABLE_OPENED=OPEN_ITEM_TABLES(ins_idron) && for explording items
>> IF TABLE_OPENED=.T.
>> DO CASE
>> CASE ins_idron="PP01"
>> SELECT rep_samp
>> set filter to state="1" .and. ana_ass=1 && filter out for only those assigned
>> go top
>> K=0
>> do while !eof() && find out the ins_id related to the sample code, FROM THE FIRST SAMPLE CODE
>> replace state with "2"
>> select spitem
>> set filter to sampnum=rep_samp.sampnum .and. pre_stat="1" &&»Ý­nµÑ¨úªÌ
>> go top
>> do while ! eof() && process from the first ins_id after filtering out, until the last one.
>> if inlist(ins_id, "C015","C006","CS06","C012","C013","CS01","CS02","CS03",;
>> "CS04","CS05","CS06","C001","C002","C003","C004","C005")
>> replace pre_stat with "2" && 2 stands for µÑ¨ú¤¤
>> endif
>> skip
>> enddo
>> k=k+1
>> pag=ceiling(k/10)
>> =append_to_item_table(newanaid,rep_samp.sampnum,PAG) select anaid_sa
>> append blank
>> replace ana_id with ana_ass.ana_id
>> replace sampcode with rep_samp.sampnum
>> select rep_samp
>> skip
>> enddo
>>
>> CASE ins_idron="PP02"
>> SELECT rep_samp
>> set filter to state="3" .and. ana_ass=1
>> K=0
>> do while !eof()
>> go top
>> replace state with "4"
>> select spitem
>> set filter to sampnum=rep_samp.sampnum .and. pre_stat="3" &&»Ý­n®ø¤ÆªÌ
>> go top
>> do while ! eof() && (SPITEM) process from the first ins_id after filtering out, until the last one.
>> if INLIST(ins_id,"A012","A013","A014","A015","B037","B043","B044","B045","B047",;
>> "B050","B053","B054","B058","B066","B071","B072","C012","C013") ;
>> .or. inlist(ins_id,"CS01","CS02","CS03","CS04","CS05","CS06","C001","C002","C003",;
>> "C004","C005")
>> replace pre_stat with "4" && 4 stands for ®ø¤Æ¤¤
>> endif
>> skip
>> enddo
>> k=k+1
>> pag=ceiling(k/10)
>> =append_to_item_table(newanaid,rep_samp.sampnum,PAG)
>> select anaid_sa
>> append blank
>> replace ana_id with ana_ass.ana_id
>> replace sampcode with rep_samp.sampnum
>> select rep_samp
>> skip
>> enddo
>> OTHERWISE
>> select spitem
>> set order to ans_state
>> set filter to ins_id=ins_idron and ana_ass=1 and pre_stat="5" &&¥iª½±µ¬£¤u, ©Î¤w§¹¦¨®ø¤ÆorµÑ¨ú«á¥i¬£¤uªÌ, ¦Ó¥B¤w³Q¬£¤u(ana_ass=1)ªÌ
>> go top
>> k=0 && K¥Î¥H°O¿ý¨ì©³¦³¦h¤Öµ§°O¿ý³Q·s¼W
>> do while !eof()
>>** scan for ins_id=left(ana_sum.ins_id,4) and ana_ass=1 and pre_stat="5"
>> IF INLIST(ins_id,"A002","A003","A004","A005","A006","A007","A008","A009","A010","A012")
>> FOR M=1 TO 4
>> k=k+1
>> pag=ceiling(k/12)
>> =append_to_A_item_table(newanaid, spitem.ass_id, spitem.sampnum,pag)
>> NEXT
>> ELSE
>> k=k+1
>> pag=ceiling(k/10)
>> =append_to_item_table(newanaid, spitem.sampnum,pag)
>> ENDIF
>>
>> select anaid_sa && Appending to ana_sa
>> append blank
>> replace ana_id with ana_ass.ana_id
>> replace sampcode with spitem.sampnum
>> select spitem
>> replace pre_stat with "6"
>> skip
>> enddo
>> **endscan
>> ENDCASE
>>
>> ** APPEND TABLES *MAIN AND *PAGE
>> SELECT MAINT
>> APPEND BLANK
>> REPLACE ANA_ID WITH NEWANAID
>> REPLACE ANA_EMP WITH THISFORM.TEXT1.VALUE
>> replace ana_dayb with date()+1
>> REPLACE PAG_SUM WITH CEILING(K/10)
>>
>> ** APPEND TABLES *PAGE
>> SELECT PAGET
>> FOR I=1 TO CEILING(K/10)
>> APPEND BLANK
>> REPLACE ANA_ID WITH NEWANAID
>> REPLACE PAG_NO WITH I
>> NEXT I
>>
>>
>> **SELECT Ana_Sum
>> THISFORM.List1.Init()
>> THISFORM.Refresh()
>>
>> 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**********************
>>******************************************************************************************************************************************
>>
>>
>>However, as I executed these functions, the speed was quite slow. It even takes about 5 minutes to complete a cycle of assignment. The platform for execution are windows 95 based PC, and a novel server to keep the data. The size of table spitem is around 10 fields, and with near 30,000 records.
>>
>>I need some suggestion to restructure the source codes, so that the speed can be improved. Many thanks for your time to read this through!
>>
>>One additional question, does Novell affect the execution speed? Will it be better to port the database to a 95 machine?
>>
>>Many thanks in advance!