Index 


An index in a database is essentially a quick lookup table which makes it faster to find records (rows) in our tables.
The indexes in the Microsoft Dynamics AX 2009 table definition are the physical indexes that exist on the tables in the database.

There are two types of indexes:
ü     Unique or Primary
ü    Non-Unique

If a unique index is created based on a column (or a set of columns), Microsoft Dynamics AX 2009 makes sure that no duplicate keys occur in that column (or set of columns).
A primary index is an index used to organize both the data store and other indexes for more efficient updating and faster access.

Non-unique, or cluster indexes, are created for performance reasons. They provide a quick way of retrieving data, instead of performing a full-table search of all the records in the table.

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself.

For example :
ü  if you want to make sure that the values in the NationalIDNumber column in the HumanResources.
ü  Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column.
ü  If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

When you use the index keyword in a select statement the kernel will translate this to a order by command and the database optimizer will chose the best index to actually use.
When you chose to use the :index hint” keyword in your select statement, Ax will force the database to use the chosen index.


What is the difference between index and index hint ?

Index :
Using "Index": when you add the statement "index MyIndex", the Axapta kernel will add an "ORDER BY" with all the fields of the index.

Example: select * from InventTable index GroupItemIdx will generate the following SQL statement to the database:

SELECT A.ITEMGROUPID, A.ITEMID, A.ITEMNAME,.... FROM INVENTTABLE A ORDER BY A.ITEMGROUPID, A.ITEMID



The Index ItemGroupIdx of the InventTable exactly contains the two fields ItemGroupID and ItemId (in that order). Using "index", you still give the control of which index to use to the database optimizer.
So, if the optimizer finds a better index to use, it will use it.

Index hint :
Using "Index hint": when you add the statement "index hint MyIndex", the Axapta kernel will add a statement to instruct the database to use that index and no other one.

Example: select * from InventTable index hint GroupItemIdx will generate the following SQL statement to the database:

SELECT /*+ INDEX(A I_175GROUPITEMIDX) */ A.ITEMGROUPID, A.ITEMID, A.ITEMNAME,.... FROM INVENTTABLE A

Using "index hint", you take away the control of which index to use from the database optimizer. So, if there may be a better index, the database will not use it.

Conclusion:

Adding the "index" statement to an Ax select, it does NOT mean that this index will be used by the database. What it DOES mean is that Ax will send an "order by" to the database.

Adding the "index hint" statement to an AX select, it DOES mean that this index will be used by the database (and no other one).

Comments

Post a Comment

Popular posts from this blog

Dynamics Ax 2012 Interview Questions:

Interview Questions: 1.       Difference between following condel   :- Use condel to delete one or more items from a container. confind :- Use confind to locate a sequence of items in a container.  conins   :- Use conins to insert some items into a container. conlen   :- Use conlen to find out how many items there are in a container. connull  :- Use connull to explicitly dispose of the contents of a container. conpeek :- Use conpeek to extract an item from a container, and to convert it into another data type   conpoke :- Use conpoke to replace (poke) an item in a container. 2.       Difference between edit and display method Display  Indicates that the method's return value is to be displayed on a form or a report. The value cannot be altered in the form or report Edit  Indicates that the method's return type is to be used to provide information for a field that is used     in  in a form. The value in the field can be edited.       3.       Diff
Read more

Oops concept in AX

Class : Class is the 1st OOPs concept .Class defines the characteristics of objects which includes its attributes , fields  properties and behavior . Let us say we have a class called car , then the color , model number , top speed can be its attributes and properties . Accelerating , breaking , turning will be its behavior Objects: Objects can be considered as a thing  that performs a set of related functions .Programming objects are used to model real worlds objects. An object is also an instant of a class . For our class Car , Ferrari  will be our object Instance : One can have an instance of a class; the instance is the actual object created at runtime.  The set of values of the attributes of a particular object is called its state. The object consists of state and the behaviour that’s defined in the object’s class. Method :Also called as  functions in some programming languages , methods defines the  behavior of particular objects . For our Car class , turning() , breakin
Read more
Exploring SysQuery class SysQuery is a helper class provided by Microsoft for working with queries dynamically. It has all its methods set as ‘static.’ It provides numerous functionalities that allow one to add data source in a query, add range, apply sort, etc. in a user-friendly system. This article will provide demonstrations for the most commonly used methods from SysQuery: findOrCreateDataSource() FindOrCreate is mainly used to check whether or not the query already contains the data source. If it does not, a data source will be created. FindOrCreate is most efficient when an event developer must write a code in which the event must be fired multiple times. This function allows developers to prevent adding additional elements to data sources. This is the most common situation in which findOrCreateDatasource() is used to avoid multiple addition of data sources in a query. Because this method is static, it uses Query as the first parameter. The next parameter w
Read more