Do you know which part is the bottleneck? Have you tried using the Coverage Profiler to narrow down the slow parts?
One thing that I can suggest is to do a REPLACE command on all fields at once and not do them individually in the scan loop. For example,
replace all inspection.passfail WITH thisform.zpassfail(), ;
inspection.jobhist WITH thisform.zjobhist(inspection.jobnum, inspection.itemnum), ;
inspection.itemhist WITH thisform.zitemhist(inspection.jobnum, inspection.itemnum)
Mike
>Any Ideas on speeding up this code. The largest table involved has 594,000 records(dieactualdata.dbf). The supporting tables have around 5000 records. I would really rather not have to start archiving the data. We access it frequently.
>
>
>thisform.setall('mousepointer',11)
>SET decimals to 5
>
>SELECT dieactualdata
>replace dieactualdata.visual WITH 'FAIL' FOR dieactualdata.jobnum==UPPER(ALLTRIM(dieheader.jobnum)) AND dieactualdata.asq>dieparams.square
>
>SELECT dieheader
>replace dieheader.printed WITH (DATE())
>thisform.saveallchanges()
>LOCAL lnRecord, lcRecords, lnTL1, lnTD1, lnTL2, lnTD2, lnTheta
>PUBLIC gnHod, gnLod, gnMod, gnHol, gnLol, gnMol, gnHid, gnLid, gnMid,;
> gnHodidc, gnLodidc, gnModidc, gcPonum, gcSteel, gcHardness,;
> gcCustomer, gcReport, gcJobnum, gcPartNo, gcGenNotes
>
>SET DECIMALS TO 5
>lnTD1=dieparams.td1
>lnTD2=dieparams.td2
>lnTL1=dieparams.tl1
>lnTL2=dieparams.tl2
>IF thisform.chkmy1.Value=1
> **adjust taper parameters for cmm measurement used in select below
> IF dieparams.td1>0
> lnTD1=( (dieparams.td1-dieparams.id)/2 +dieparams.id )
> lnTL1=dieparams.tl1/2
> endif
> IF dieparams.td2>0
> lnTD2=( (dieparams.td2-dieparams.id)/2 +dieparams.id )
> lnTL2=dieparams.tl2/2
> endif
>endif
>DO case
>CASE thisform.option.value=1
> gcReport='dierep'
>CASE thisform.option.value=2
> gcReport='diemetric'
>ENDCASE
>
>do case
> case thisform.ogtaper.value=1
>
> case thisform.ogtaper.value=2 and thisform.option.value=1
> gcReport='dietaper1'
> case thisform.ogtaper.value=3 and thisform.option.value=1
> gcReport='dietaper2'
> case thisform.ogtaper.value=2 and thisform.option.value=2
> gcReport='dietaper1metric'
> case thisform.ogtaper.value=3 and thisform.option.value=2
> gcReport='dietaper2metric'
>ENDCASE
>DO case
> CASE thisform.summaryonly.Value=1 AND thisform.option.Value=1
> gcReport='dierepsumonly'
> CASE thisform.summaryonly.Value=1 AND thisform.option.Value=2
> gcReport='dierepsumonlymetric'
>ENDCASE
>IF thisform.chkmy2.Value=1
> gcReport='dierep4'
>ENDIF
>
>gcJobnum=upper(alltrim(dieheader.jobnum))
>gcCustomer=upper(alltrim(thisform.txtcustomer.value))
>gcPonum=Upper(alltrim(thisform.txtponumber.value))
>gcSteel=upper(alltrim(thisform.txtsteel.value))
>gcHardness=upper(alltrim(thisform.txthardness.value))
>gcPartNo=(alltrim(thisform.txtDrawingnum.value))
>
>
>SELECT Dieparams.drawingnum, Dieparams.ol, Dieparams.od, Dieparams.id,;
> Dieparams.odidc, Dieparams.olplus, Dieparams.olminus, Dieparams.odplus,;
> Dieparams.odminus, Dieparams.idplus, Dieparams.idminus,;
> Dieparams.numofparts, lnTD1 as td1, Dieparams.td1plus,;
> Dieparams.td1minus, lnTD2 as td2, Dieparams.td2plus, Dieparams.td2minus,;
> lnTL1 as tl1, Dieparams.tl1plus, Dieparams.tl1minus, lnTL2 as tL2,;
> Dieparams.tl2plus, Dieparams.tl2minus, Dieactualdata.*,;
> 'P' AS passfail, 0.00 as itemhist, '000000000000000' as jobhist;
> FROM ecnc!dieparams LEFT OUTER JOIN ecnc!dieactualdata ;
> ON Dieparams.jobnum == Dieactualdata.jobnum;
> WHERE Dieparams.jobnum == (gcJobNum);
> ORDER BY Dieactualdata.itemnum ;
> INTO CURSOR INSPECTION readwrite
>
>gcJobnum='Job Number: '+(gcJobnum)
>gcCustomer='Customer: '+(gcCustomer)
>gcPonum='PO Number: '+(gcPonum)
>gcSteel='Steel: '+(gcSteel)
>gcHardness='Hardness: '+(gcHardness)+' R`C`'
>gcPartNo='Part Number: '+(gcPartNo)
>gcGenNotes=ALLTRIM(thisform.edtGennotes.getvalue())
>gcGenNotes=STRTRAN(gcGenNotes,CHR(13),'')
>gcGenNotes=STRTRAN(gcGenNotes,CHR(10),'')
>gcGenNotes=STRTRAN(gcGenNotes,' ',' ')
>
>
>select inspection
>GO top
>SCAN
> replace inspection.passfail WITH thisform.zpassfail()
> replace inspection.jobhist WITH thisform.zjobhist(inspection.jobnum, inspection.itemnum)
> replace inspection.itemhist WITH thisform.zitemhist(inspection.jobnum, inspection.itemnum)
>endscan
>
>thisform.setall('mousepointer',0)
>
>*BROWSE last
>
>DO form dierepshipped
>
>>
>supporting methods
>zpassfail
>
>Do Case
> Case ((inspection.atd1)>(inspection.td1)+(inspection.td1plus) Or (inspection.atd1)<(inspection.td1)-(inspection.td1minus)) AND thisform.ogtaper.Value>1
> Return 'F'
> Case ((inspection.atl1)>(inspection.tl1)+(inspection.tl1plus) Or (inspection.atl1)<(inspection.tl1)-(inspection.tl1minus)) AND thisform.ogtaper.Value>1
> Return 'F'
> Case ((inspection.atd2)>(inspection.td2)+(inspection.td2plus) Or (inspection.atd2)<(inspection.td2)-(inspection.td2minus)) AND thisform.ogtaper.Value>2
> Return 'F'
> Case ((inspection.atl2)>(inspection.tl2)+(inspection.tl2plus) Or (inspection.atl2)<(inspection.tl2)-(inspection.tl2minus)) AND thisform.ogtaper.Value>2
> Return 'F'
> Case (inspection.aol)>(inspection.ol)+(inspection.olplus) Or (inspection.aol)<(inspection.ol)-(inspection.olminus)
> Return 'F'
> Case ((inspection.aod)>(inspection.od)+(inspection.odplus) Or (inspection.aod)<(inspection.od)-(inspection.odminus))
> Return 'F'
> Case (inspection.aid)>(inspection.Id)+(inspection.idplus) Or (inspection.aid)<(inspection.Id)-(inspection.idminus)
> Return 'F'
> Case (inspection.aodidc)>(inspection.odidc) Or (inspection.aodidc)<0.00000
> Return 'F'
> Case (inspection.aodidc2)>(inspection.odidc) Or (inspection.aodidc2)<0.00000
> Return 'F'
> Case (inspection.Visual)!='PASS'
> Return 'F'
> Otherwise
> Return 'P'
>Endcase
>
>
>method zjobhistory
>
>PARAMETERS cJobNum, nItemNum
>SELECT movehist
>LOCATE FOR ALLTRIM(movehist.destjob)=ALLTRIM(m.cjobnum) AND movehist.destitem=m.nitemnum
>IF FOUND()
> RETURN (strtran(movehist.sourcejob, '-0000',''))
>ELSE
> RETURN ' '
>endif
>
