Sorry. Hit save without typing...

Are you opening the view with "NODATA". That can speed things up.

Do you have an index on DELETED() AND only few if any deleted records? If yes, try removing that TAG.

I'd stick with TTOC(xxx,1) or DTOS().

good luck


>I am confused about something very basic to VFP.
>
>I have a view:
>
>CREATE SQL VIEW "VL_OI_CART_PURCHASESLEFT" ;
> AS SELECT Account_debits.dtxndatetm,;
> Account_debits.cwithdrawal_number,;
> Purchases.yline_total,;
> Purchases.cid as cpurchases_id,;
> IIF(EMPTY(NVL(Account_debits.cexports_id," ")),.F.,.T.) AS lexported,;
> Purchases.cid,;
> Purchases.yprior_balance,;
> Purchases.ytax,;
> Purchases.cuserid,;
> Account_debits.cid as caccount_debits_id,;
> IIF(EMPTY(NVL(Carts.dtreturned,{})),.f.,.t.) as lemailed;
> ;
> FROM inmatetrustfund!purchases;
> LEFT OUTER JOIN inmatetrustfund!account_debits;
> ON Purchases.cid = Account_debits.cpurchases_id;
> LEFT OUTER JOIN inmatetrustfund!carts;
> ON Purchases.ccarts_id = Carts.cid;
> WHERE Account_debits.cinmates_id = ?VP_cinmates_id AND;
> Account_debits.ctype = "Cart" AND;
> Account_debits.dtxndatetm >= DATETIME()-432000;
> ORDER BY Account_debits.dtxndatetm DESC
>
>Pulls the last 5 days of 'Cart' purchases for a particular inmate.
>
>My Account_Debits table has a regular index on cinmates_id.
>My Account_Debits table has a regular index on ctype.
>It does NOT have any index on dtxndatetm.
>
>The joined to tables have the necessary indexes.
>
>My question: Do I just add a regular index on Account_Debits.dtxndatetm where expression is dtxndatetm OR do I need to make the expression TTOC(dtxndatetm,1)?
>
>I am trying to optimize because building this view is slow.
>
>To ensure I am understood: If I selected a bunch of records from a table into a temporary cursor and then wanted to sort that temporary cursor by datetime, I know I would build the index on TTOC(datetimefield,1). My question is not the same problem. I am trying to reduce network traffic. I am pretty sure the index expression should be just Account_Debits.dtxndatetm but I need to be sure.
>
>Thanks,
>John