Index vs Index hint

Hello,

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 Axapta select, it does NOT mean that this index will be used by the database. What it DOES mean is that Axapta will send an “order by” to the database.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s