Hi Joe,

it's not amazing, it's logical.

First of all, to get a select statement optimized, you must have an index tag on the expression AFTER the equal sign, in this case attrim(cusip). It is not "legal" to create an index on alltrim() since alltrim() gives variable length strings, and an index have to be fixed length. If you try to create an index on alltrim(something), VFP tries to calculate the necessary index key length, and if the first records will create a short length for alltrim(), the calculated index key will be too short for the later records which should need a longer index key length. So an index on alltrim() will not give any error, but can easily give you wrong/unexpected results.

Secondly, for your original select statement, VFP must evaluate the alltrim() twice for each record in the parent table, namely alltrim(Custody.Cusip) and alltrim(Cusip.Cusip). With the == comparison, VFP simply compares the "raw" values, no conversion needed.

So alltrim(cusip) can not be optimized, ==cusip can. My personal rule of thumb is to try to avoid alltrim() at all cost, I almost never use it, and NEVER in select statements.

>Amazing! Removing the replacing the ALLTRIM's with == works great. FYI SYS(3054,11) reports 'none' in both VFP7 & VFP9
>
>>Try to change

>>ON ALLTRIM(Custody.Cusip) = ALLTRIM(Cusip.Cusip) ;
>>* to
>>ON Custody.Cusip == Cusip.Cusip ;
>>

Use SYS(3054,11) to see Rushmore optimization levels for the queries.
>>
>>>
>>>I’m a contractor upgrading many of my client’s legacy applications from FP2.6, VFP5 and VFP7 to VFP9 Service Pack 1, and I’ve run into a real speed problem with the legacy queries in VFP9. Consider the following existing query which is really a local view (i.e. “CREATE VIEW AS ;”). In VFP5 or VFP7 it’s almost instantaneous. In VFP9 it takes 35 minutes!!!. I’ve converted the legacy tables from 2.6 to 9.0 and have re-indexed them. CPCURRENT() and CPDBF() on all tables are both 1252. Does anyone have any ideas?
>>>
>>>Thanks
>>>
>>>Joseph
>>>

>>>SELECT PADR(LEFT(ALLTRIM(Corp.corpname),89) + " (" + LEFT(Custody.corpnbr, 9) + ")", 100) AS corpnamnbr,;
>>>LEFT(Custody.corpnbr,9) AS corpnbr,;
>>>PADR(LEFT(ALLTRIM(Corp.corpname),100),100) AS corpname,;
>>>PADR(LEFT(ALLTRIM(Cu.cuname),100),100) AS cuname,;
>>>LEFT(Custody.cunbr,9) AS cunbr,;
>>>PADR(IIF(LEFT(Cusip.poolnbr,2) =" TC", "Transferable Certificates"," Security Holdings"),50) AS holdingtype,;
>>>LEFT(Custody.secnbr,3) + "-" + SUBSTR(Custody.secnbr,4,4) + "-" + RIGHT (Custody.secnbr,3) AS secnbrprt,;
>>>PADR(LEFT(Cusip.fedwiredes,100),100) AS fedwiredes,;
>>>Custody.face AS face, Cusip.maturedte AS maturedate,;
>>>Custody.setdte AS setdate, Cusip.coupon AS coupon,;
>>>PADR(LEFT(Cusip.Cusip,50),50) AS Cusip,;
>>>LEFT(ALLTRIM(Cusip.poolnbr),6) AS poolnbr,;
>>>PADR(Custody.FORM,50) AS secform,;
>>>PADR(Custody.location,50) AS location,;
>>>PADR(Custody.arbit,50) AS arbit,;
>>>IIF(Cusip.sectype => "060" AND Cusip.sectype <= "099",.T.,.F.) AS mbs,;
>>>PADR(Cusip.sectype,50) AS sectype,;
>>>PADR(Custody.Cusip,50) AS custodycusip,;
>>>IIF(Custody.sold = .T.,.T.,.F.) AS sold, Custody.tradenbr AS tradenbr;
>>>FROM ;
>>>c:\PROD\si\DATA\custdy Custody ;
>>>INNER JOIN c:\PROD\si\DATA\cusip Cusip ;
>>>ON ALLTRIM(Custody.Cusip) = ALLTRIM(Cusip.Cusip) ;
>>>INNER JOIN c:\PROD\si\DATA\cu Cu ;
>>>ON Custody.cunbr = Cu.cunbr ;
>>>INNER JOIN c:\PROD\si\DATA\corp Corp ;
>>>ON Custody.corpnbr = Corp.corpnbr;
>>>WHERE LEN(Custody.tradenbr) > ( 0 )
>>>