ALLTRIM(movehist.destjob) is NOT a valid index key, since it will not give a fixed length.
>Do you have an index on these expression?:
>- dieactualdata.jobnum
>- dieactualdata.asq
>- Dieparams.jobnum
>- ALLTRIM(movehist.destjob)
>- movehist.destitem
>
>The GO TOP command isn't necessary:
>
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
Did you use the Coverage Profiler to locate the bottlenecks?
>
>>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
>>
