i belive the fields in the index should be in proper
order.
In case (in table inventTrans) you are using the
Index StatusItemIdx then you need to include in your
select statement first statusReceipt, then
StatusIssue, then ItemId and then DateStatus. If you
dont want to use any of these in the select statement
then you can exclude it but have it in a proper order.
This is the reason we see multiple Indexes
created on the table (take ex InventTrans). The
ItemIdx should be used when you are going to have Item
first in the index.
Take another example of SalesLine.
If you are making a search by SalesId and SalesStatus
we use SalesStatusIdx (see the method
calcCreditBalance in the salesLine table and see the
order of fields used in the statement).
Also should consider no of times the
record/combination of records are available.
If we see the "find" methods we usually see that
the index used is the one which is unique or least nos
of lines. This is also because there would be only
one/least record with the combination.
Ex; SalesLine table SalesLineIdx.
May be all my assumptions/findings may not be right.
but we are here to learn.
cheers,
Girish
--- "Harry (Harshawardhan Deshpande"
<[EMAIL PROTECTED]> wrote:
> hi
>
> 1. the order of where clause statement is not
> important
> 2. I would recommend not putting any index hint to
> begin with and check the sql trace to see which
> index is being selected.
> 3. If you are not happy with the performance then
> try things like #placeholder etc. Depends on how
> many times this statement is being called.
>
> regards
>
> harry
>
> James Flavell <[EMAIL PROTECTED]> wrote:
> Hi can somebody tell me the field 'DateStatus' on
> inventtrans is set at what points?
>
> I am just trying to improve performance on
> inventtrans searchs and can see the index
> 'StatusItemIdx' is useful to me. Basically I want
> to find all the inventtrans cost for sales invoices
> for a period of time and hence why any index that
> has a date in it would be very useful.
>
> My thinking is if date financial exists on
> inventtrans can I assume date status will ALWAYS be
> the same date as financial for that particular
> inventtrans record? And therfore replace in my
> select using datefinancial with datastatus...
>
> Part of my select originally would be (basically the
> std select to calc cost for an custinvoicetrans but
> with date range added):
>
> && inventTrans.dateFinancial
> >= _runStart
> && inventTrans.dateFinancial
> <= _runEnd
> && inventTrans.ItemId
> == 'ABC'
> && inventTrans.qty
> != 0
> &&
> inventTrans.packingSlipReturned == 0
> && inventTrans.statusReceipt
> <= StatusReceipt::Purchased
> && inventTrans.statusIssue
> <= StatusIssue::Sold
> && _invoiceTrans.InventTransId
> != ""
>
> but I am wondering can I change it to:
>
> && inventTrans.dateStatus
> >= _runStart
> && inventTrans.dateStatus
> <= _runEnd
> && inventTrans.ItemId
> == 'ABC'
> && inventTrans.qty
> != 0
> &&
> inventTrans.packingSlipReturned == 0
> && inventTrans.statusReceipt
> <= StatusReceipt::Purchased
> && inventTrans.statusIssue
> <= StatusIssue::Sold
> && _invoiceTrans.InventTransId
> != ""
>
>
> and be sure of always getting the same result (i.e.
> once data finanical then the inventtrans datastatus
> will not change)?
>
> Also I am just wondering the order of my where
> clause statements is important or not? I know in
> XAL previously that supposedly it was best to put
> them in the same order as the fields used in the
> index but not sure if any such 'feature' is still
> present in Axapta????
>
> If I can use dateStatus would you recommend I use
> this StatusItemIdx as an index hint? I am just
> wondering since ItemId is not the first component
> then to me the search will have to go through ALL
> inventtrans that are sold or purchased. If I use an
> index with itemid as the first component then the
> search will immediately be narrowed down to the item
>
>
> Thanks
> James
>
>
>
> Sharing the knowledge on Axapta.
>
>
>
> SPONSORED LINKS
> Business finance course Business to business finance
> Small business finance Business finance consultant
> Business finance magazine Business finance uk
>
> ---------------------------------
> YAHOO! GROUPS LINKS
>
>
> Visit your group "Axapta-Knowledge-Village" on
> the web.
>
> To unsubscribe from this group, send an email
> to:
>
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the
> Yahoo! Terms of Service.
>
>
> ---------------------------------
>
>
>
>
> ---------------------------------
> Yahoo! for Good
> Click here to donate to the Hurricane Katrina
> relief effort.
___________________________________________________________
To help you stay safe and secure online, we've developed the all new Yahoo!
Security Centre. http://uk.security.yahoo.com
------------------------ Yahoo! Groups Sponsor --------------------~-->
Help Sudanese refugees rebuild their lives through GlobalGiving.
http://us.click.yahoo.com/hjNroD/EbOLAA/cosFAA/kGEolB/TM
--------------------------------------------------------------------~->
Sharing the knowledge on Axapta.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/Axapta-Knowledge-Village/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
