Hi Kenneth

>The simplest fix is to ditch the ALLTRIM() function in the WHERE clause. ALLTRIM will force a table scan since an expression that includes the 'ALLTRIM' function is inherently unoptimizable.

Can you explain that? If I build an INDEX ON ALLTRIM(somefield) TAG sometag, this shows full optimization SELECT * FROM sometable WHERE ALLTRIM(somefield) = "TEST" INTO CURSOR somecursor. FoxPro pads the index key to a fixed length.

The rule of thumb for your indexes is to have the index expression match EXACTLY the WHERE clause expression. The statement that creates your index should look something like this:
>INDEX ON UPPER( cSTATUS ) TAG ixTagName
>INDEX ON cWorkOrderID TAG ixTagName
>

Which is true even with ALLTRIM().

>If you're going to use the "==" operator, you should pad the value being searched to match the length of the field, like this: " ACCEPTED" or this "ACCEPTED ", depending on how they are stored in the field, rather than trim the value in the field to match the search value.

If you pad the values, you don't need the ==. If you're going to ALLTRIM() use PAD() to fix the key length to a value of your choosing.

INDEX ON PADR(ALLTRIM(cSTATUS),15) TAG ixTag1

then ...

SELECT * FROM sometable WHERE PADR(ALLTRIM(cSTATUS),15) = "ACCEPTED "

will be an exact match, will be fully optimized and will be under your control.

>
>>I have such statements when adding production items to my table (ROLLS). These statements undoubtedly slow down the operation which varies from computer to computer, depending on it's speed, but is even relatively slow with a Pentium IV.
>>
>>Is there anything I can do to really speed up the execution of these SQL statements and also not jeopordize theintegrity of the results?
>>
>>SELECT * FROM ROLLS WHERE UPPER(ALLTRIM(cSTATUS))=="REJECTED" AND ROLLS.cWorkOrderID == SALEORDER_ITEMS.cWorkOrderNo ORDER BY nRoll INTO CURSOR tmpRejected
>>
>>SELECT * FROM ROLLS WHERE UPPER(ALLTRIM(cSTATUS))=="ACCEPTED" AND ROLLS.cWorkOrderID == SALEORDER_ITEMS.cWorkOrderNo ORDER BY nRoll INTO CURSOR tmpAccepted
>>
>>SELECT nWeight_Roll, ;
>>SUM(nWeight_Roll) AS nWeight_Skid, ;
>>SUM(nWeight_Roll_Metric) AS nWeight_Skid_Metric, ;
>>SUM(nUPerRoll) AS nUnits_Skid, ;
>>SUM(nFootage_Roll) AS nFootage_Skid, ;
>>cSkidID ;
>>FROM ROLLS ;
>>WHERE ROLLS.cWorkOrderID = SALEORDER_ITEMS.cWorkOrderNo ;
>>GROUP BY 6 ;
>>ORDER BY 6 ;
>>INTO CURSOR tmpSkid