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 - --(-@
[Axapta-Knowledge-Village] InventDim in a Relation definition?
How can I specify a component of InventDim in a relationship? I want to relate a table that has just ItemId and InventSerialId to InventSum, which has ItemId, and InventDim. - Please help! I don't see anyway to create a relationship like this on the table-definitions.
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
