Its relatively easy to prove one way or the other. Write a
job with a select statement whose where clause fields are in the order of an
index on the table. Turn on SQL trace (sent to database is best) then run the
job.
Now change the order of the fields in the job's select
statement and re-run the job.
Finally examine the captured SQL trace and look at the
query plan used, it should be the same in both cases.
Malcolm.
From: Axapta-Knowledge-Village@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Jens Strandberg
Sent: 28 September 2005 21:50
To: Axapta-Knowledge-Village@yahoogroups.com
Subject: RE: [Axapta-Knowledge-Village] DataStatus on inventtrans
Hmm,
I have
also been told by a legacy Axapta programmer, that the field order in the select
statements should follow the index order ...
I
would like to know too, if this is not correct.
Thanks
in advance !
/Jens
-----Oprindelig meddelelse-----
Fra: Axapta-Knowledge-Village@yahoogroups.com [mailto:[EMAIL PROTECTED]På vegne af Malcolm Burtt
Sendt: 28. september 2005 18:19
Til: Axapta-Knowledge-Village@yahoogroups.com
Emne: RE: [Axapta-Knowledge-Village] DataStatus on inventtransASFAIK it shouldn't make any difference which order the fields in a where clause are specified as the SQL query optimiser will sort it all out when it calculates the query plan.RegardsMalcolm
Hi James,
From: Axapta-Knowledge-Village@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Girish Bhatkal
Sent: 28 September 2005 16:47
To: Axapta-Knowledge-Village@yahoogroups.com
Subject: RE: [Axapta-Knowledge-Village] DataStatus on inventtrans
during one of my previous implementation (2 yrs back)
we had some performance issues and a Consultant from
MS had visited us. he asked me to change the order of
the select statements in order to improve the
performance. This is because of the way the data is
read. Since then i have been following this.
If anyone feels what i have mentioned is wrong,
then please do let us know.
cheers,
Girish
--- James Flavell <[EMAIL PROTECTED]> wrote:
---------------------------------
Thanks Grish,
The part about fields being in order I was thinking
about the where clauses
in my select statments...whether they needed to come
in the same order in my
select statement as the order of the fields in the
index. TO clarify, are
you saying the order of the clauses in the select
statement have an impact
on performance?
In terms of order of fields in the index I fully
understand the importance
of getting those right.
Thanks
James
-----Original Message-----
From: Axapta-Knowledge-Village@yahoogroups.com
[mailto:[EMAIL PROTECTED] On
Behalf Of Girish
Bhatkal
Sent: 28 September 2005 18:31
To: Axapta-Knowledge-Village@yahoogroups.com
Subject: Re: [Axapta-Knowledge-Village] DataStatus on
inventtrans
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
Sharing the knowledge on Axapta.
Yahoo! Groups Links
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.
---------------------------------
___________________________________________________________
How much free photo storage do you get? Store your holiday
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
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.
