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!
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only