Re: [Axapta-Knowledge-Village] when to use Index and index hint
Hi Malcolm, Thanks for great explanation :) On Thu, Aug 19, 2010 at 4:53 PM, Malcolm Burtt < malcolm.bu...@touchstone.co.uk> wrote: > > > Hi > > > > There’s probably not much to choose between them but… > > > > If there are lots of records with the same value for field1 then the > database will need to scan through lots of index leaf nodes to reach all of > the records that you want to select. Since the leaf nodes for itemIdx will > be smaller than the leaf nodes for numIdx there will be more leaf nodes per > page which means that the database might need to read fewer index pages as > it scans through all the nodes with the field1 value that you’re selecting > and so will generate less I/O using this index. > > > > Having said all that, the optimiser is almost certainly smart enough to > know this and will pick itemIdx anyhow and if that’s the case then it makes > little since to add the index hint. The only caveat on this is that AX is > probably parameterising this query to make it reusable (i.e. allow different > values of _field1 to use the same query plan). This means that the optimiser > will take an “on average” view of the number of leaf nodes that have the > same value for field1 which is fine if you have a roughly even spread of > values for field1 but if the field1 value distribution is actually skewed > towards one or two of the possible values then the “on average” view might > not produce the best plan and this is the point where you would step in and > use a query hint to push the optimiser into picking a specific plan. > > > > Malcolm > > > > *From:* Axapta-Knowledge-Village@yahoogroups.com [mailto: > axapta-knowledge-vill...@yahoogroups.com] *On Behalf Of *thomas 'znal' > ramdhan > *Sent:* 19 August 2010 03:12 > *To:* Axapta-Knowledge-Village@yahoogroups.com > *Subject:* Re: [Axapta-Knowledge-Village] when to use Index and index hint > > > > > > Hi Malcolm, > > From above question, I'd like to ask another question which related to > indexhint. > Let's say I need to query to tableA which field criteria is field1. > > select * from tableA where tableA.field1 = _field1 > > indexs for table A are below > > 1. NumIdx > field1 > field2 > field3 > > 2. ItemIdx > field1 > field3 > > In best practice, which index should I use ? > > Thanks and Regards, > Afin > > On Wed, Aug 18, 2010 at 6:53 PM, Malcolm Burtt < > malcolm.bu...@touchstone.co.uk> wrote: > > > > Hi > > > > For most queries, the answer is to use neither. > > > > “Index Hint” overrides the SQL Query optimiser and forces the use of the > specified index – which is ok if you’re sure that the optimiser is picking a > bad query plan and that using “index hint” allows your query to run faster > but most of the time the optimiser is better at picking the right index than > you are so you should let it do its job. When you do need to tune the query > you should consider other hints (e.g. forcenestedloop, etc) alongside “index > hint” to get the most effective query plan for your data. > > > > I can’t think of a good reason to use “Index” at all. It simply ensures > that the data is returned sorted in the index order which you can already > achieve with the “Order By” clause with the important advantage that the > sort order would not change if someone altered the index definition in some > way. > > > > Regards > > > > > > Malcolm Burtt > > Touchstone Group > > People - Partnership - Solutions > > > > > > > > > -- > ne faites pas une limite pour votre competence > - > bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit > - > non faccia un limite per la vostra abilita > - > --(-@ > > > -- ne faites pas une limite pour votre competence - bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit - non faccia un limite per la vostra abilita - --(-@
RE: [Axapta-Knowledge-Village] when to use Index and index hint
Hi There's probably not much to choose between them but... If there are lots of records with the same value for field1 then the database will need to scan through lots of index leaf nodes to reach all of the records that you want to select. Since the leaf nodes for itemIdx will be smaller than the leaf nodes for numIdx there will be more leaf nodes per page which means that the database might need to read fewer index pages as it scans through all the nodes with the field1 value that you're selecting and so will generate less I/O using this index. Having said all that, the optimiser is almost certainly smart enough to know this and will pick itemIdx anyhow and if that's the case then it makes little since to add the index hint. The only caveat on this is that AX is probably parameterising this query to make it reusable (i.e. allow different values of _field1 to use the same query plan). This means that the optimiser will take an "on average" view of the number of leaf nodes that have the same value for field1 which is fine if you have a roughly even spread of values for field1 but if the field1 value distribution is actually skewed towards one or two of the possible values then the "on average" view might not produce the best plan and this is the point where you would step in and use a query hint to push the optimiser into picking a specific plan. Malcolm From: Axapta-Knowledge-Village@yahoogroups.com [mailto:axapta-knowledge-vill...@yahoogroups.com] On Behalf Of thomas 'znal' ramdhan Sent: 19 August 2010 03:12 To: Axapta-Knowledge-Village@yahoogroups.com Subject: Re: [Axapta-Knowledge-Village] when to use Index and index hint Hi Malcolm, >From above question, I'd like to ask another question which related to >indexhint. Let's say I need to query to tableA which field criteria is field1. select * from tableA where tableA.field1 = _field1 indexs for table A are below 1. NumIdx field1 field2 field3 2. ItemIdx field1 field3 In best practice, which index should I use ? Thanks and Regards, Afin On Wed, Aug 18, 2010 at 6:53 PM, Malcolm Burtt mailto:malcolm.bu...@touchstone.co.uk>> wrote: Hi For most queries, the answer is to use neither. "Index Hint" overrides the SQL Query optimiser and forces the use of the specified index - which is ok if you're sure that the optimiser is picking a bad query plan and that using "index hint" allows your query to run faster but most of the time the optimiser is better at picking the right index than you are so you should let it do its job. When you do need to tune the query you should consider other hints (e.g. forcenestedloop, etc) alongside "index hint" to get the most effective query plan for your data. I can't think of a good reason to use "Index" at all. It simply ensures that the data is returned sorted in the index order which you can already achieve with the "Order By" clause with the important advantage that the sort order would not change if someone altered the index definition in some way. Regards Malcolm Burtt Touchstone Group People - Partnership - Solutions -- ne faites pas une limite pour votre competence - bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit - non faccia un limite per la vostra abilita - --(-@
Re: [Axapta-Knowledge-Village] when to use Index and index hint
Hi Malcolm, >From above question, I'd like to ask another question which related to indexhint. Let's say I need to query to tableA which field criteria is field1. select * from tableA where tableA.field1 = _field1 indexs for table A are below 1. NumIdx field1 field2 field3 2. ItemIdx field1 field3 In best practice, which index should I use ? Thanks and Regards, Afin On Wed, Aug 18, 2010 at 6:53 PM, Malcolm Burtt < malcolm.bu...@touchstone.co.uk> wrote: > > > Hi > > > > For most queries, the answer is to use neither. > > > > “Index Hint” overrides the SQL Query optimiser and forces the use of the > specified index – which is ok if you’re sure that the optimiser is picking a > bad query plan and that using “index hint” allows your query to run faster > but most of the time the optimiser is better at picking the right index than > you are so you should let it do its job. When you do need to tune the query > you should consider other hints (e.g. forcenestedloop, etc) alongside “index > hint” to get the most effective query plan for your data. > > > > I can’t think of a good reason to use “Index” at all. It simply ensures > that the data is returned sorted in the index order which you can already > achieve with the “Order By” clause with the important advantage that the > sort order would not change if someone altered the index definition in some > way. > > > > Regards > > > > > > Malcolm Burtt > > Touchstone Group > > People - Partnership - Solutions > > > > > > > -- ne faites pas une limite pour votre competence - bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit - non faccia un limite per la vostra abilita - --(-@
RE: [Axapta-Knowledge-Village] when to use Index and index hint
Hi For most queries, the answer is to use neither. “Index Hint” overrides the SQL Query optimiser and forces the use of the specified index – which is ok if you’re sure that the optimiser is picking a bad query plan and that using “index hint” allows your query to run faster but most of the time the optimiser is better at picking the right index than you are so you should let it do its job. When you do need to tune the query you should consider other hints (e.g. forcenestedloop, etc) alongside “index hint” to get the most effective query plan for your data. I can’t think of a good reason to use “Index” at all. It simply ensures that the data is returned sorted in the index order which you can already achieve with the “Order By” clause with the important advantage that the sort order would not change if someone altered the index definition in some way. Regards Malcolm Burtt Touchstone Group People - Partnership - Solutions From: Axapta-Knowledge-Village@yahoogroups.com [mailto:axapta-knowledge-vill...@yahoogroups.com] On Behalf Of pranam mukher Sent: 18 August 2010 12:17 To: axa knwoldge; dev ax Subject: [Axapta-Knowledge-Village] when to use Index and index hint hi I am aware of the fact that using index in select statement will include an order by clause in ur SQL query. i want to know, exactly when shud i use index and when to opt for index hint, in my Queries. Thanks Pradeep.SI
[Axapta-Knowledge-Village] when to use Index and index hint
hi I am aware of the fact that using index in select statement will include an order by clause in ur SQL query. i want to know, exactly when shud i use index and when to opt for index hint, in my Queries. Thanks Pradeep.SI
