Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Proper index for date field
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement