>The following select statement works fast from the command window, however it is noticably slower in my form. It is optimizing all the indexes.
>
>SELECT Scorder.ord_acct, Scorder.ord_order, scordln.orl_qty-scordln.orl_qtypicked-scordln.orl_qtyrec as qtyonorder;
>FROM scorder LEFT OUTER JOIN scordln ;
>ON Scorder.ord_order = Scordln.orl_order ;
>where scorder.ord_acct < 200 and upper(scordln.orl_sku) = upper(tcSku) and scordln.orl_dunn # "X";
>into cursor cTemp
>

First obvious question - is there an index on UPPER(scordln.orl_sku) and on scorder.ord_acct? If not on the second, that's hurting the SELECT performance.

Second - why not use the more precise == compare in the SELECT?

Third - why the OUTER JOIN - you don't process records that fail an INNER JOIN IAC in the SCAN.

>I changed my form to use the following scan and insert. It is a lot faster.
>
>select scordln
>scan for upper(scordln.orl_sku) == upper(tcSku) and scordln.orl_dunn # 'X'

You're processing fewer scordln records, so if the FOR is optimizable, the compare set is reduced

> =seek(scordln.orl_order,'scorder','ord_order')

You've assumed there's a find - you'll be ! FOUND() and at EOF() in scorder
I'd assume that with no scorder you have the equivalent of RIGHT OUTER JOIN rather than LEFT OUTER JOIN being implemented in the SCAN vs the SELECT

> if scorder.ord_acct < 200
> insert into cstockorder(cst_acct , cst_order , cst_qty) ;
> values (scorder.ord_acct, scorder.ord_order, scordln.orl_qty-scordln.orl_qtypicked-scordln.orl_qtyrec)

You risk entering null or empty values for the first two fields with orphan scordln records; at EOF(), scorder.ord_acct is probably 0, sitting on the phantom record beyond EOF

> endif
>endscan
>
>Is a scan & insert normally faster than a select into a cursor?
>
>Thanks
>
>Pat